I am getting the "Must declare the scalar variable "@Val1"" error when inserting the record using C#

Question

When I am running the following code

        if (NewButtonClicked == true) {
            string sql = "SELECT MAX(location_id)+1 FROM locations";
            OdbcCommand my_odbc_cmd = new OdbcCommand(sql, my_odbc_connection);
            OdbcDataReader my_data_reader;
            int new_id = 0;
            my_data_reader = my_odbc_cmd.ExecuteReader();
            if (my_data_reader.HasRows) 
            {
                my_data_reader.Read();
                new_id = (int)my_data_reader[0];
            }
            my_data_reader.Close();
            textBoxLocationID.Text = new_id.ToString();
            sql = "INSERT INTO locations (location_id,location,latitude,longitude,city,"
                + "state_province,country,continent) VALUES (@Val1,'@Val2',@Val3,@Val4,'@Val5','@Val6','@Val7','@Val8')";
            my_odbc_cmd.Connection = my_odbc_connection;
            my_odbc_cmd.CommandText = sql;
            my_odbc_cmd.Parameters.AddWithValue("@Val1", new_id);
            my_odbc_cmd.Parameters.AddWithValue("@Val2", textBoxName.Text);
            my_odbc_cmd.Parameters.AddWithValue("@Val3", textBoxLatitude.Text);
            my_odbc_cmd.Parameters.AddWithValue("@Val4", textBoxLongitude.Text);
            my_odbc_cmd.Parameters.AddWithValue("@Val5", textBoxCity.Text);
            my_odbc_cmd.Parameters.AddWithValue("@Val6", textBoxState_Province.Text);
            my_odbc_cmd.Parameters.AddWithValue("@Val7", textBoxCountry.Text);
            my_odbc_cmd.Parameters.AddWithValue("@Val8", textBoxContinent.Text);

            my_odbc_cmd.CommandType = CommandType.Text;
            my_odbc_cmd.ExecuteNonQuery();
        }

It is giving me "Must declare the scalar variable "@Val1"" when the execution is reached at:

my_odbc_cmd.ExecuteNonQuery();

How can I solve this error. Can anyone help me? Database used at the backend is SQL Server 2008 R2. I am using the ODBC connection to connect to the database.

Please retag your question with the appropriate database system you are using... — Lee Taylor, Apr 15 '14 at 19:01

Marc Gravell · Answer 1 · 2014-04-15T19:49:06.423

4

This probably means that new_id is null. Parameters with a value of null are not sent. You need to pass DBNull.Value instead. Crazy but true.

my_odbc_cmd.Parameters.AddWithValue("@Val1", ((object)new_id) ?? DBNull.Value);

Also: '@Val2' (and similar) are very wrong. You almost certainly mean just @Val2, no quotes.

edited Apr 15 '14 at 19:49

answered Apr 15 '14 at 19:04

Marc Gravell

1,026,079
266
2,566
2,900

+1 for crazy, why are you casting to an object btw? I always do `new_id ?? DbNull.Value` or is there something to it? – Silvermind Apr 15 '14 at 19:15
@Silvermind because I can't see how `new_id` is declared. If it is declared as `object`, then yes: `new_id ?? DBNull.Value` is fine. If it is declared as `int?` then what you suggest won't compile (`Operator '??' cannot be applied to operands of type 'int?' and 'System.DBNull'`). What I suggest will. I will, however, update with something simpler - a middle ground. Happier now? – Marc Gravell Apr 15 '14 at 19:46
I was not going for a simpler approach and my state of happiness might be a little higher, but I just honestly made a mistake for the sake of dealing with a lot of weak types in this same context all day ;) Thanks for the response. – Silvermind Apr 15 '14 at 20:16

score 2 · Answer 2 · answered Apr 15 '14 at 19:09

2

According to the "Working with Parameter Placeholders" section on the MSDN page Configuring Parameters and Parameter Data Types, the Odbc datasource supports only positional parameters via ? in the query text, not named parameters (such as @Val1), which are supported only by SqlClient.

Additionally as Marc Gravell indicates, you shouldn't be quoting the parameters in your query.

Try changing your SQL to:

sql = "INSERT INTO locations (location_id,location,latitude,longitude,city,"
            + "state_province,country,continent) VALUES (?,?,?,?,?,?,?,?)";

answered Apr 15 '14 at 19:09

Iridium

23,323
6
52
74

How can I assign a value to a parameter ?. – Taha Hassan Latif Apr 16 '14 at 08:54
The way you're adding parameters currently should be fine, but the relation of the `?` to the parameter is by the order in which they appear in the query (so the first `?` takes its value from the first parameter added, etc.) – Iridium Apr 16 '14 at 09:02

I am getting the "Must declare the scalar variable "@Val1"" error when inserting the record using C#

2 Answers2