Exception from Add Parameter to DbCommand

Question

I need an update command with parameters, and for some reason I cannot use stored procedures, in fact we generate an update command depending on the database, table and columns, we use the following forms:

string conStr = "Provider=SQLNCLI10;Server=.\\sql2008;DataBase=MyDataBase;Trusted_Connection=true;"; DbProviderFactory dbFactory = DbProviderFactories.GetFactory("System.Data.OleDb"); DbConnection dbConnection = dbFactory.CreateConnection(); dbConnection.ConnectionString = conStr; DbCommand dbCommand = dbFactory.CreateCommand(); dbCommand.CommandText = "UPDATE [Student] SET Name = @Name Where Id = @Id"; DbParameter param1 = dbCommand.CreateParameter(); param1.ParameterName = "@Name"; param1.Value = "LOL"; DbParameter param2 = dbCommand.CreateParameter(); param2.ParameterName = "@Id"; param2.Value = 5; dbCommand.Parameters.Add(param1); dbCommand.Parameters.Add(param2); dbConnection.Open(); dbCommand.ExecuteNonQuery(); dbConnection.Close(); 

But there is an exception:

Must declare scalar variable "@Name"

Where is the problem in this code? Does anyone have an idea about this?

Answer 1

Since you are using System.Data.OleDb as your database provider (regardless of whether you use sql server) do you need to use ? as a parameter placeholder, for example:

 "UPDATE [Student] SET Name = ? Where Id = ?"; 

Using the System.Data.OleDb provider, the parameter names no longer matter, but you need to make sure that the parameters are in the order that the parametric objects add to the command object's parameter collection.

EDIT: If you want to keep the @ placeholder as a parameter, you can simply change this:

 DbProviderFactory dbFactory = DbProviderFactories.GetFactory("System.Data.OleDb"); 

to

 DbProviderFactory dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient"); 

Answer 2

Try this and let me know if it works.

 DbParameter param1 = dbCommand.CreateParameter(); param1.ParameterName.AddWithValue("@Name", Textbox1.Text); 

or

 DbParameter param1 = dbCommand.CreateParameter(); param1.SelectCommand.Parameters.AddWithValue("@Name", Textbox1.Text); 

Answer 3

 command.CommandText = "EXEC [dbo].[PR_GetPurchase_ProjectBUDetails] " + prjID.ToString()+ " ," + empID.ToString(); 

Hard code parameters when calling stored procedure .
This solved my problem.