Error in executing an OleDbCommand.. "Must declare the scalar variable "@MaxID"."

Question

private void AddValue(string strValue)

{

      //get the maximum id for Lists first

      int MaxID = DataOperations.ReturnMaxIDInATable("Lists", connString);
      int iSqlStatus = 0;
      string query = "INSERT INTO Lists(ID, ListName, ListValue) 
         VALUES(@MaxID, @ListName, @ListValue)";

      MaxID++;

      OleDbConnection dbConn = new OleDbConnection(connString);
      OleDbCommand dbComm = new OleDbCommand();

      dbComm.Parameters.Clear();
      try
      {
                dbComm.CommandText = query;
                dbComm.CommandType = CommandType.Text;

                OleDbParameter IDParam = new OleDbParameter();
                IDParam.ParameterName = "@MaxID";
                IDParam.OleDbType = OleDbType.BigInt;
                IDParam.Value = MaxID;
                dbComm.Parameters.Add(IDParam);

                dbComm.Parameters.AddWithValue("@ListName", ListName);
                dbComm.Parameters.AddWithValue("@ListValue", strValue);
                dbComm.Connection = dbConn;
                DataAccess.HandleConnection(dbConn);

                iSqlStatus = Convert.ToInt16(dbComm.ExecuteNonQuery());

                //Now check the status
                if (iSqlStatus != 0)
                {
                    //DO your failed messaging here
                    //return false;
                }
                else
                {
                    //Do your success work here
                    //dbComm.
                    //return true;
                }
      }
      catch (Exception ex)
      {
                MessageBox.Show(ex.Message, "Error inserting value in "
                                            + ListName + ","
                                            + strValue);
                //return false;
      }
      finally
      {
                DataAccess.HandleConnection(dbConn);
      }

}

http://stackoverflow.com/questions/389591/dotnet-oledbparameters-name-obsession — o.k.w, May 03 '11 at 13:49

score 5 · Answer 1 · answered May 03 '11 at 13:33

5

I believe you need to use question marks for the parameters when executing SQL through the OleDbCommand (while SqlCommand uses @). Example:

INSERT INTO Lists (ID, ListName, ListValue) VALUES (?, ?, ?)

You only need to Add the Parameters in the order that they appear in the SQL.

answered May 03 '11 at 13:33

Erick Petrucelli

14,386
8
64
84

1

Yea for OleDb, it is the order that counts, not the names – o.k.w May 03 '11 at 13:41
1

But not only the order, but the *question marks* that represent "parameter" for OleDb. – Erick Petrucelli May 03 '11 at 13:44
I've been using @ for the parameter name in other parts of the code and it works so changing it in ? should be moot. But I would try other wise. Also the order I think doesn't affect but I would also try. – Rej Maranan May 05 '11 at 20:30
Thank you for all your answers – Rej Maranan May 05 '11 at 20:30

score 0 · Answer 2 · answered Jul 05 '16 at 11:09

If you include the clause "DECLARE" in the start of query, will work:

string query = "DECLARE @MaxID as bigint, "+
                      " @ListName as Varchar(100), "+
                      " @ListValue As Varchar(100) " +
        " INSERT INTO Lists(ID, ListName, ListValue) " +
            " VALUES(@MaxID, @ListName, @ListValue)"

Furthermore, the right solution is change your driver to SQLClient and OracleClient. OleDb is not recomended for be used with SQL 2005 and above.

score -2 · Answer 3 · edited May 03 '11 at 13:39

-2

The following fragment should read:

IDParam.ParameterName = "MaxID";
IDParam.OleDbType = OleDbType.BigInt;
IDParam.Value = MaxID;
dbComm.Parameters.Add(IDParam);

dbComm.Parameters.AddWithValue("ListName", ListName);
dbComm.Parameters.AddWithValue("ListValue", strValue);

edited May 03 '11 at 13:39

Rob

45,296
24
122
150

answered May 03 '11 at 13:35

Flawless

127
7

1

It don't solve the problem for OleDbCommand. Even, it haven't any effect on SqlCommand. – Erick Petrucelli May 03 '11 at 14:06
I tried BigInt before and it didn't work. But haven't tried not using a prefix for the Parameter names. I would try this one also. – Rej Maranan May 05 '11 at 20:31
No prefixes on the parameters don't work either. Any more solutions in mind? – Rej Maranan May 16 '11 at 12:42

score -2 · Answer 4 · edited May 17 '11 at 08:58

-2

This seemed to do the trick..

string query = string.Format("INSERT INTO Lists(ID, ListName, ListValue) 
         VALUES({0}, '{1}', '{2}')", MaxID, ListName, strValue);

Although I have reservations about it like what if I need to add a date value?

edited May 17 '11 at 08:58

stealthyninja

10,343
11
51
59

answered May 16 '11 at 13:19

Rej Maranan

27
1
3

Error in executing an OleDbCommand.. "Must declare the scalar variable "@MaxID"."

4 Answers4

Linked