CODEWITHSUNDEEP
c#sqlasp.net-mvc-4parameters
HendPro12
HendPro12

Reputation: 1104

SQL Parameters Formatting Issue

In my C# MVC4 application, I have the following code:

 string[] parameters = new string[items.Count];
            SqlCommand SecondQuery = new SqlCommand();
            for (int i = 0; i < items.Count; i++)
            {
                parameters[i] = string.Format(items[i].Id);
                SecondQuery.Parameters.AddWithValue(parameters[i], items[i]);
            }
            SecondQuery.CommandText = string.Format("SELECT * from S_analysis WHERE heat_no IN ({0})", string.Join(", ", parameters));
            SecondQuery.Connection = new SqlConnection(strSQLconnection);
            using (SqlConnection conn = new SqlConnection(strSQLconnection))
            {
                SecondQuery.CommandTimeout = 50000;
                conn.Open();
                SecondQuery.Connection = new SqlConnection(strSQLconnection);
                SecondQuery.Connection.Open();

                using (var reader7 = SecondQuery.ExecuteReader())
                {
                    int fieldCount = reader7.FieldCount;

                    while (reader7.Read())
                    {
                        for (int i = 0; i < fieldCount; i++)
                        {
                            finalresults.Add(reader7[i].ToString());
                        }
                    }
                }
            }

My application crashes on the line: using (var reader7 = SecondQuery.ExecuteReader()) with this error:No mapping exists from object type <>f__AnonymousType42[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

Im assuming this is because when I view my SecondQuery.CommandText my query looks like:SecondQuery.CommandText = "SELECT * from S_analysis WHERE heat_no IN (B5P5649, B5P5647, B5P5656, A5P0761, A5P0762)" I believe each parameter within the IN portion of the code should have the single quote symbol on each side. How can I modify my code to add this or is this what is causing my error?

Upvotes: 0

Views: 167

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107726

You're right in that string values need to be quoted for SQL Server.

string.Join(", ", parameters)

There are many options, but this would work if this were the only problem

"'"+string.Join(",", parameters).Replace(",", "','")+"'"

However, the bugs in your code are more extensive. You were attempting to parameterize each item - this doesn't work since nowhere in the CommandText are you specifying the placeholders. All you have is the static IN list. You also have far more SqlConnection instances than required.

        string[] parameters = new string[items.Count];
        SqlCommand SecondQuery = new SqlCommand();
        for (int i = 0; i < items.Count; i++)
        {
            parameters[i] = "'" + string.Format(items[i].Id) + "'";
            //SecondQuery.Parameters.AddWithValue(parameters[i], items[i]);
        }
        SecondQuery.CommandText = string.Format("SELECT * from S_analysis WHERE heat_no IN ({0})", string.Join(",", parameters));
        using (SqlConnection conn = new SqlConnection(strSQLconnection))
        {
            SecondQuery.CommandTimeout = 50000;
            conn.Open();
            SecondQuery.Connection = conn;

            using (var reader7 = SecondQuery.ExecuteReader())
            {
                int fieldCount = reader7.FieldCount;

                while (reader7.Read())
                {
                    for (int i = 0; i < fieldCount; i++)
                    {
                        finalresults.Add(reader7[i].ToString());
                    }
                }
            }
        }

Upvotes: 3

Related Questions