CODEWITHSUNDEEP
c#oledb
Syed Salman Raza Zaidi
Syed Salman Raza Zaidi

Reputation: 2192

Must declare scalar variaable @ID when inserting record

I am inserting a record in DB, I am using inline query, I have to get the generated ID, this is how I am doing this

 var query = "INSERT INTO [Users] ([Username],[Password])  VALUES(@username,@pwd) SELECT @ID = SCOPE_IDENTITY()";
            OleDbParameter[] queryparam = new OleDbParameter[3];
            queryparam[0] = new OleDbParameter("@username", OleDbType.VarChar);
            queryparam[0].Value = "username";
            queryparam[1] = new OleDbParameter("@pwd", OleDbType.VarChar);
            queryparam[1].Value = "123456";
            queryparam[2] = new OleDbParameter("@ID", OleDbType.Integer);
            queryparam[2].Direction = ParameterDirection.Output;

             OleDbCommand myCommand = new OleDbCommand();
              myCommand.Connection = DBConnectionHelper.getConnection();
                myCommand.CommandText = query;
                myCommand.Parameters.AddRange(queryparam);
                adapter.InsertCommand = myCommand;               
                myCommand.ExecuteNonQuery();

I am getting error:

Must declare the scalar variable "@ID".

Must declare the scalar variable "@username".

I also tried using (int)myCommand.ExecuteScalar() but no luck.

How can I get generated ID in this case?

Upvotes: 1

Views: 399

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

From OleDbCommand.Parameters:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text1. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

So, for your code your query should be:

INSERT INTO [Users] ([Username],[Password]) VALUES(?,?) SELECT ? = SCOPE_IDENTITY()

1Which is the default, so is what your code is using.

Upvotes: 1

user3509755
user3509755

Reputation: 26

Instead of Select Use Set @Id=Scope_Identity()

Upvotes: 0

Dhrumil
Dhrumil

Reputation: 3204

From what I can make out of your question, you have declared a variable called query and written your query inside it. Also, your parameter declaration seems to be correct.

Now the problem might be in this line :

myCommand.CommandText = _query;

The variable is query and here you are using _query. So that is probably why the parameters are not added for that particular query and hence the error. It should be like this :

myCommand.CommandText = query;

Hope this clears.

Upvotes: 0

Related Questions