System.ArgumentException Value does not fall within the expected range, SQL issue

Question

I'm using .Net Compact 3.5 Windows 7 CE.

I have an application with about 50 users, I have it setup so that I would get an email every time a database transaction failed, with the query.

Every so often I would get an email with a stack trace that starts like this:

System.ArgumentException: Value does not fall within the expected range.
at System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, SqlParameter value)
at System.Data.SqlClient.SqlParameterCollection.AddWithoutEvents(SqlParameter value)
at System.Data.SqlClient.SqlParameterCollection.Add(SqlParameter value)
at MedWMS.Database.startSqlConnection(String query, SqlParameter[] parameters, SqlConnection connection, SqlCommand cmd)
at MedWMS.Database.<>c__DisplayClasse.b__8()
at MedWMS.Database.retry(Action action)
at MedWMS.Database.executeNonQuery(String query, SqlParameter[] parameters, String connectionString)...

The SQL query which causes this issue is not always the same. I run the same query seconds after I get the email in SQL Server Management Studio with no issues.

I would like to know why this could be happening. This is my first question on SO so please let me know if I'm doing something wrong. I would be happy to answer any questions to provide more detail.

This is a sample of the code that would cause this error:

SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("@salesOrder", this.salesOrderNumber);

string query = @"
                Select InvTermsOverride from SorMaster where SalesOrder = Convert(int, @salesOrder) and InvTermsOverride = '07' --07 is for COD";

DataTable dt = Database.executeSelectQuery(query, parameters, Country.getCurrent().getSysproConnectionStrReportServer());

This is the query that actually gets passed:

Select InvTermsOverride from SorMaster where SalesOrder = Convert(int, '000000001138325') and InvTermsOverride = '07' --07 is for COD

Here is the relevant methods from the Database class:

public static DataTable executeSelectQuery(String query, SqlParameter[] parameters, string connectionString)
{
    DataTable dt = new DataTable();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = null;

        try
        {
            retry(() =>
            {
                cmd = startSqlConnection(query, parameters, connection, cmd);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            });
        }
        catch (Exception ex)
        {
            onDbConnectionCatch(cmd, ex);
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }

    return dt;
}

public static void executeNonQuery(String query, SqlParameter[] parameters, string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = null;

        try
        {
            retry(() =>
            {
                cmd = startSqlConnection(query, parameters, connection, cmd);
                cmd.ExecuteNonQuery();
            });
        }
        catch (Exception ex)
        {
            onDbConnectionCatch(cmd, ex);
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }
}

private static void retry(Action action)
{
    int retryCount = 3;
    int retryInterval = 1000;
    Exception lastException = null;

    for (int retry = 0; retry < retryCount; retry++)
    {
        try
        {
            if (retry > 0)
                System.Threading.Thread.Sleep(retryInterval);
            action();

            lastException = null;
            return;
        }
        catch (Exception ex)
        {
            lastException = ex;
        }
    }

    if (lastException != null)
    {
        throw lastException;
    }
}

private static SqlCommand startSqlConnection(String query, SqlParameter[] parameters, SqlConnection connection, SqlCommand cmd)
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    cmd = new SqlCommand(query, connection);

    if (parameters != null)
    {
        foreach (SqlParameter sp in parameters)
        {
            if (sp != null)
            {
                cmd.Parameters.Add(sp);
            }
        }
    }

    return cmd;
}

private static void onDbConnectionCatch(SqlCommand cmd, Exception ex)
{
    try
    {
        new BigButtonMessageBox("", "Unable connect to database").ShowDialog();
        sendEmailWithSqlQuery(cmd, ex);
    }
    catch
    {
    }
}

private static void sendEmailWithSqlQuery(SqlCommand cmd, Exception ex)
{
    string query2 = "cmd was null";

    if (cmd != null)
    {
        query2 = cmd.CommandText;

        foreach (SqlParameter p in cmd.Parameters)
        {
            query2 = query2.Replace(p.ParameterName, "'" + p.Value.ToString() + "'");
        }
    }

    InternetTools.sendEmail("DB ERROR", ex.ToString() + "\r\n" + query2);
}

Answer 1

I had the same issue as Can't solve "Sqlparameter is already contained by another SqlparameterCollection"

For some reason SQL CE has a different error.

Because of my retry method, I couldn't reuse the SqlParameter object, still not sure why it's not allowed

Anyways I changed

cmd.Parameters.Add(sp);

to

cmd.Parameters.Add(sp.ParameterName, sp.Value);