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
– Soner Gönül

2013-08-20 12:23:09 +00:00
Commented Aug 20, 2013 at 12:23 — Soner Gönül
– Soner Gönül, Commented Aug 20, 2013 at 12:23
@SonerGönül on commandInsert.ExecuteNonQuery();

Lucky Luke2
– Lucky Luke2

2013-08-20 12:25:33 +00:00
Commented Aug 20, 2013 at 12:25 — Lucky Luke2
– Lucky Luke2, Commented Aug 20, 2013 at 12:25

Community · Accepted Answer · 2017-05-23 10:31:54Z

4

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

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Aug 20, 2013 at 12:25

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lucky Luke2 Over a year ago

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

Steve Over a year ago

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?

Microsoft DN · Accepted Answer · 2013-08-20 13:37:40Z

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, 2013 at 13:37

answered Aug 20, 2013 at 12:24

Microsoft DN

10.1k10 gold badges54 silver badges74 bronze badges

2 Comments

Lucky Luke2 Over a year ago

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.

Microsoft DN Over a year ago

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,'')

Collectives™ on Stack Overflow

Casting datareader values to integer in insert statement

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related