ExecutenonQuery not working

Question

I saved the proc as shown 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'm 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

 Incorrect Syntax near @Nominated 

Answer 1

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

 EXECUTE pr_Update @UserID, @Nominated 

second, change your code to 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 } } } 

Answer 2

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 do is call the stored procedure, you can 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; 

Answer 3

Give only the name of the stored procedure, because after that you add the parameter to the statement. Also specify CommandType.

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

Answer 4

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

 mySqlCom.CommandText = "pr_Update"; 

And set the type of type command to the stored procedure:

 mySqlCom.CommandType = CommandType.StoredProcedure; 

See the MSDN page for more details.