I need an update command with parameters, and for some reasons I can't use stored procedures, actually we generate update command depend on database, table and columns, the following forms are we use:
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 the scalar variable "@Name"
Where is the problem in this code? Does anyone have an idea about this?
Best Answer
As you are using
System.Data.OleDb
as database provider ( regardless you are using a sql server ) you need to use the?
as the parameter placeholder like:By using the
System.Data.OleDb
provider the names of the parameters doesn`t matter anymore but you need to ensure that the occurance of the parameters match the order the parameterobjects are added to the command objects parameter collection.EDIT: If you want to keep the
@
as parameter placeholder you can just change this:to