Casting datareader values to integer in insert statement

Question

I am building an insert statement with data from an excel file using data reader values. The excel file datareader always only has one record. There are two columns in the destination table, first of type int and second column of varchar.

while (dr.Read())
{
     string insertstring = @"insert into configtest values
     ('" + dr.GetValue(0) + "','"
         + dr.GetValue(1) +  "')";

}   
SqlCommand commandInsert = new SqlCommand(insertstring, conn);
commandInsert.ExecuteNonQuery();

I get error

"Error converting varchar type to numeric.

I tried casting the first value to type int and get a

"Specified cast is not valid"

error. Please help with this.

On which line do you get this errors? – Soner Gönül Aug 20 '13 at 12:23 — Soner Gönül, Aug 20 '13 at 12:23
@SonerGönül on commandInsert.ExecuteNonQuery(); – Lucky Luke2 Aug 20 '13 at 12:25 — Lucky Luke2, Aug 20 '13 at 12:25

score 4 · Answer 1 · edited May 23 '17 at 10:31

If the first column in the destination table is an integer column you should not pass a string.
In your concatenation command you put single quotes around the first parameter and this means you try to pass a string. Thus the error.

However you should always write a parameterized query, not try to build a sql command using string concatenation

string insertstring = @"insert into configtest values (@p1, @p2)";
while (dr.Read())
{
     SqlCommand commandInsert = new SqlCommand(insertstring, conn);
     if(dr.IsDBNull(0))
          commandInsert.Parameters.AddWithValue("@p1", DBNull.Value);
     else
          commandInsert.Parameters.AddWithValue("@p1", Convert.ToInt32(dr[0]));
     if(dr.IsDBNull(1))
          commandInsert.Parameters.AddWithValue("@p2", DBNull.Value);
     else
          commandInsert.Parameters.AddWithValue("@p2", dr[1].ToString());
     commandInsert.ExecuteNonQuery();
}

This approach will keep you safe from Sql Injection and from syntax error triggered if your string values contain single quotes.

As a final note, keep in mind that when a DataReader is open you cannot use its connection for other activities (ExecuteNonQuery) unless you use the MultipleActiveResultSets=True in your connection string

Thank you. Both fields allow nulls. Without using the parameterized query, how would i cater for empty or null values. Insert statement would look "this insert into configtest values (,'')" and throws an error — Lucky Luke2, Aug 20 '13 at 12:58
Added a quick fix for the NULL value in the input data. Probably more checks are required. What if both fields are null? Do you plan to insert a record with all null values? — Steve, Aug 20 '13 at 17:15

Microsoft DN · Accepted Answer · 2013-08-20T13:37:40.647

1

Replace your string with following (assuming your dr.GetValue(0) is int.)

string insertstring = @"insert into configtest values
     (" + dr.GetValue(0) + ",'"
         + dr.GetValue(1) +  "')";

Just removed quotes around dr.GetValue(0). As it is of type int it does not require quotes.

EDIT:

To insert null values, you can check for null values in query itself-

string insertstring = @"insert into configtest values
         (" + (dr.GetValue(0) == null ? System.Data.SqlTypes.SqlInt32.Null : dr.GetValue(0)) + ",'"
            + (dr.GetValue(1) == null ? string.Empty : dr.GetValue(1)) +  "')";

Though this is not the perfect solution but can do a workaround !!!!

edited Aug 20 '13 at 13:37

answered Aug 20 '13 at 12:24

Microsoft DN

9,706
10
51
71

Thank you. Both fields allow nulls. how would i cater for empty or null values. Insert statement would look this "insert into configtest values (,'string')" and throws an error. – Lucky Luke2 Aug 20 '13 at 12:58
1

You can insert "NULL" if you find null value for dr.GetValue(0). Then your insert statement will look like- "insert into configtest values (NULL,'') – Microsoft DN Aug 20 '13 at 13:00

Casting datareader values to integer in insert statement

2 Answers2