SqlParameter not working with INSERT statement

Question

I am getting the following error when trying to use a parameter to insert a value into my SQL Server database.

SqlParameter Param1 = new SqlParameter("@test", SqlDbType.VarChar);
Param1.Value = "test";

SqlCommand AddtoDb = new SqlCommand("INSERT INTO [temp] VALUES (@test)", conn);
AddtoDb.ExecuteNonQuery();

This is the error I get:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

The database has only 1 column that accepts varChar(50). I know the connection is working because the database received the input if I do something like:

SqlCommand AddtoDb = new SqlCommand("INSERT INTO [temp] VALUES ('test')", conn);
AddtoDb.ExecuteNonQuery();

What am I doing wrong?

Please include the fill error text, when the execption shows up in the debugger click the Copy To Clipbord link then paste the text here inside a set of <pre></pre> tags. You have left out the details that would give us any chance of figuring out what went wrong. — Scott Chamberlain
– Scott Chamberlain, Commented Jun 28, 2013 at 19:52

p.s.w.g · Accepted Answer · 2013-06-28 19:51:16Z

8

You created the parameter, but you haven't actually associated it with the command in any way. You need to add the parameter to the command's Parameters collection:

SqlCommand AddtoDb = new SqlCommand("INSERT INTO [temp] VALUES (@test)", conn);
SqlParameter Param1 = new SqlParameter("@test", SqlDbType.VarChar);
Param1.Value = "test";
AddtoDb.Parameters.Add(Param1);
AddtoDb.ExecuteNonQuery();

Or more simply:

SqlCommand AddtoDb = new SqlCommand("INSERT INTO [temp] VALUES (@test)", conn);
AddtoDb.Parameters.AddWithValue("@test", "test");
AddtoDb.ExecuteNonQuery();

answered Jun 28, 2013 at 19:51

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

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

2 Comments

Silvermind Over a year ago

just a simple perhaps retorical question, but shouldn't the @ be left out for the name? I taught it was added automatically.

p.s.w.g Over a year ago

@Silvermind No. It should be included. From the docs: "The ParameterName is specified in the form @paramname. You must set ParameterName before executing a SqlCommand that relies on parameters."

Ann L. · Accepted Answer · 2013-06-28 19:52:49Z

2

You aren't associating your SqlParameter object with the SqlCommand object.

Try adding this line between lines 3 and 4:

AddtoDb.Parameters.Add(Param1);

answered Jun 28, 2013 at 19:52

Ann L.

14k5 gold badges39 silver badges67 bronze badges

Collectives™ on Stack Overflow

SqlParameter not working with INSERT statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related