c# Inserting int value into SQL table

Question

I want to insert some data into SQL table. But while inserting int no matter what I've tried I get error:

System.Data.SqlClient.SqlException: 'Conversion failed when converting the varchar value '@ID' to data type int.'

I even manually set ID to 1 simply to be 100 % sure it's int but still get that error

String query = "INSERT INTO table(dbo.table.ID, dbo.table.secondvar) 
VALUES ('@ID','@secondvar')";             
 using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        int ID = 1;
                        cmd.Parameters.AddWithValue("@ID", ID);
                        cmd.Parameters.AddWithValue("@secondvar", tableModel.secondvar);    
                        connection.Open();
                        int result = cmd.ExecuteNonQuery();

juergen d · Accepted Answer · 2018-10-16 11:58:45Z

5

Remove the quotes around the values. The framework handles that already.

VALUES ('@ID','@secondvar')

should be

VALUES (@ID,@secondvar)

answered Oct 16, 2018 at 11:58

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

1 Comment

David Over a year ago

this answer is better, both values must be without semicolumns. I will mark this as answer after timeout elapse

Ashkan Mobayen Khiabani · Accepted Answer · 2018-10-16 12:07:30Z

1

ID is an integer so it must not be between '':

Change this line:

String query = "INSERT INTO table(dbo.table.ID, dbo.table.secondvar) 
VALUES ('@ID','@secondvar')";

to this:

String query = "INSERT INTO table(dbo.table.ID, dbo.table.secondvar) 
VALUES (@ID,@secondvar)";

And its better avoid using AddWithValue instead use it like:

String query = "INSERT INTO table(dbo.table.ID, dbo.table.secondvar) 
VALUES (@ID,'@secondvar')";             
 using (SqlCommand cmd = new SqlCommand(query, connection))
 {
      int ID = 1;
      cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
      cmd.Parameters.Add("@secondvar", SqlDbType.VarChar).Value = somevalue; 
      //rest of the code
 }

edited Oct 16, 2018 at 12:07

answered Oct 16, 2018 at 11:59

Ashkan Mobayen Khiabani

34.4k35 gold badges112 silver badges187 bronze badges

1 Comment

juergen d Over a year ago

Don't quote any values. Not even strings. The framework does that for you. That is the advantage of Prepared Statements.

Collectives™ on Stack Overflow

c# Inserting int value into SQL table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related