ExecutenonQuery not working

Question

I have stored proc as below:

ALTER PROC pr_Update_Users_Nomination ( @UserID AS VARCHAR(100), @Nominated AS BIT ) AS UPDATE User SET isNominated = @Nominated WHERE EMPID = @UserID;

I want to call this procedure from c# code: Below is the code I am trying:

void OpenConnection() { string Nominated = "False"; //Connection String string sConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConString1"].ConnectionString; SqlConnection mySqlCon = new SqlConnection(sConnString); SqlCommand mySqlCom = mySqlCon.CreateCommand(); //Call the stored proc and provide in parameters mySqlCom.CommandText = "EXECUTE pr_Update @UserID @Nominated"; mySqlCom.Parameters.Add("@UserID", SqlDbType.VarChar, 20).Value = UserID; mySqlCom.Parameters.Add("@Nominated", SqlDbType.Bit).Value = Nominated; mySqlCon.Open(); mySqlCom.ExecuteNonQuery(); mySqlCon.Close(); }

I get an error saying

Incorrect Syntax near @Nominated

John Woo · Accepted Answer · 2012-11-14 15:34:49Z

first, when executing a procedure with parameter(s), separate the parameters with a comma

EXECUTE pr_Update @UserID, @Nominated

second, modify your code into this,

 string sConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConString1"].ConnectionString; using(SqlConnection mySqlCon = new SqlConnection(sConnString)) { using(SqlCommand mySqlCom = new SqlCommand()) { mySqlCom.Connection = mySqlCon; mySqlCom.CommandText = "pr_Update"; mySqlCom.CommandType = CommandType.StoredProcedure; mySqlCom.Parameters.Add("@UserID", SqlDbType.VarChar, 20).Value = UserID; mySqlCom.Parameters.Add("@Nominated", SqlDbType.Bit).Value = Nominated; try { mySqlCon.Open(); mySqlCom.ExecuteNonQuery(); } catch(SqlException ex) { // do something with the exception // don't hide it } } }

SWeko · Accepted Answer · 2012-11-14 15:02:44Z

You are missing a comma (,) between the parameters.

It should be

mySqlCom.CommandText = "EXECUTE pr_Update @UserID, @Nominated"; mySqlCom.Parameters.Add("@UserID", SqlDbType.VarChar, 20).Value = UserID; mySqlCom.Parameters.Add("@Nominated", SqlDbType.Bit).Value = Nominated;

Alternatively, since all you are doing is calling a stored proc, you could do:

mySqlCom.CommandType = CommandType.StoredProcedure ; mySqlCom.CommandText = "pr_Update"; //no need to specify parameter names mySqlCom.Parameters.Add("@UserID", SqlDbType.VarChar, 20).Value = UserID; mySqlCom.Parameters.Add("@Nominated", SqlDbType.Bit).Value = Nominated;

Adil · Accepted Answer · 2012-11-14 15:03:01Z

4

Give only name of stored procedure, as you are adding parameter in statements after this. Also set CommandType.

 mySqlCom.CommandText = "pr_Update"; mySqlCom.CommandType = CommandType.StoredProcedure;

answered Nov 14, 2012 at 15:03

Adil

148k25 gold badges217 silver badges207 bronze badges

2 Comments

Fabio Over a year ago

Don't forget to set mySqlCom.CommandType = CommandType.StoredProcedure

Adil Over a year ago

Thanks @Fabio GouW, I was doing that mean while.

Karel Frajták · Accepted Answer · 2012-11-14 15:06:16Z

You are invoking wrong SQL. You should set the command text of command to pr_Update only:

mySqlCom.CommandText = "pr_Update";

And set type command type to stored procedure:

mySqlCom.CommandType = CommandType.StoredProcedure;

See MSDN page for more.

The way you did it. Only setting the CommandText was wrong.

Collectives™ on Stack Overflow

ExecutenonQuery not working

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Linked

Related