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?
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:
"UPDATE [Student] SET Name = ? Where Id = ?";
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:
DbProviderFactory dbFactory = DbProviderFactories.GetFactory("System.Data.OleDb");
to
DbProviderFactory dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With