Exception in Add Parameter To DbCommand

Question

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?

At which point did you associate the command with the connection? Did the factory did that? Also, at which line do you get the exception? — Geeky Guy
– Geeky Guy, Commented Aug 20, 2013 at 4:57
@Nilesh Give me this link That's the answer, but how about Odbc command, is that worked like Oledb? — Saeid Alizade
– Saeid Alizade, Commented Aug 20, 2013 at 5:12

Heslacher · Accepted Answer · 2013-08-20 05:21:30Z

7

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.OleDbprovider 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");

edited Aug 20, 2013 at 5:21

answered Aug 20, 2013 at 5:09

Heslacher

2,18722 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nilesh Over a year ago

This is what I was trying to answer but all of a sudden the post was back to the original state, without any comments or answers!

Saeid Alizade Over a year ago

@Nilesh The answer was deleted by owner, I just be lucky to see your link

Saeid Alizade Over a year ago

I found it: See this one for Odbc

chresse · Accepted Answer · 2014-09-18 09:09:05Z

0

Try that 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);

edited Sep 18, 2014 at 9:09

chresse

5,8873 gold badges35 silver badges50 bronze badges

answered Aug 20, 2013 at 5:14

CodeXerox

951 gold badge5 silver badges18 bronze badges

Comments

PrakashG · Accepted Answer · 2019-02-05 09:11:26Z

-1

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

Hard code parameters while calling stored procedure.
This solved my issue.

edited Feb 5, 2019 at 9:11

PrakashG

1,6506 gold badges21 silver badges30 bronze badges

answered Feb 5, 2019 at 8:31

user1089345

1193 silver badges11 bronze badges

Collectives™ on Stack Overflow

Exception in Add Parameter To DbCommand

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related