C# and SQL - Must declare the scalar variable writing to DB

Question

I've been at this for hours and can't catch a break. I'm writing this program in C# which connects to a database and writes new rows each time new sensor readings are available. The Function snippet that writes a new row to the DB is below.

When I run my code I continue to get the following run-time error:

System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@dbSpeed".

I started having this issue when I switched from using a regular old array to a Dictionary, but I'm not sure that is the cause of this.

private void WriteDataRowtoSQL(Dictionary<string, double> dataPoints)
{
     //SQL Database Connection String.
    string dataSource = @"Data Source=TSCRPC\TSCRPCDB;Initial Catalog=TSCRPCDB;Integrated Security=True";

    //SQL Database Query Command Build to insert sensor data as rows.  
    string sensorDataRowItems = "(Date,Time,Mac_State,S_GainStatus,S_Speed,S_Direction,S_Temperature,S_Humidity,S_PowerOutput,S_Current,S_Voltage,S_EngineRPM,S_NoiseLevel,S_BatteryVolts,S_TorkStrain1,S_TorkStrain2,S_WheelStrain,S_Vibration1,S_Vibration2,S_BreakPressure)";
    string cmdString = "INSERT INTO SensorData " + sensorDataRowItems + " VALUES (@dbDate, @dbTime, @dbMacState, @dbGainStatus, @dbSpeed, @dbDirection, @dbTemperature, @dbHumidity, @dbPowerOutput, @dbCurrent, @dbVoltage, @dbEngineRPM, @dbNoiseLevel, @dbBatteryVolts, @dbTorkStrain1, @dbTorkStrain2, @dbWheelStrain, @dbVibration1, @dbVibration2, @dbBreakPressure)";

    //Connect to and use SQL DB.
    using (SqlConnection sqlConn = new SqlConnection(dataSource))
    {
        using (SqlCommand comm = new SqlCommand())
        {
            comm.Connection = sqlConn;
            comm.CommandText = cmdString;

            if (dataPoints.ContainsKey("Gain Status") == true)
            {
                comm.Parameters.AddWithValue("@dbGainStatus", true); 
            }
            else if (dataPoints.ContainsKey("Battery Voltage") == true)
            {
                comm.Parameters.AddWithValue("@dbBatteryVolts", dataPoints["Battery Voltage"]);
            }
            else if (dataPoints.ContainsKey("Break Line Pressure") == true)
            {
                comm.Parameters.AddWithValue("@dbBreakPressure", dataPoints["Break Line Pressure"]);
            }
            else if (dataPoints.ContainsKey("Volts") == true)
            {
                comm.Parameters.AddWithValue("@dbVoltage", dataPoints["Volts"]);
            }
            else if (dataPoints.ContainsKey("Current") == true)
            {
                comm.Parameters.AddWithValue("@dbCurrent", dataPoints["Current"]);
            }
            else if (dataPoints.ContainsKey("Vibration") == true)
            {
                comm.Parameters.AddWithValue("@dbVibration1", dataPoints["Vibration"]);
            }
            else if (dataPoints.ContainsKey("Tork Strain 1") == true)
            {
                comm.Parameters.AddWithValue("@dbTorkStrain1", dataPoints["Tork Strain 1"]);
            }
            else if (dataPoints.ContainsKey("Tork Strain 2") == true)
            {
                comm.Parameters.AddWithValue("@dbTorkStrain2", dataPoints["Tork Strain 2"]);
            }
            else if (dataPoints.ContainsKey("Speed") == true)
            {
                comm.Parameters.AddWithValue("@dbSpeed", dataPoints["Speed"]);
            }
            else if (dataPoints.ContainsKey("Engine RPM") == true)
            {
                comm.Parameters.AddWithValue("@dbEngineRPM", dataPoints["Engine RPM"]);
            }

            comm.Parameters.AddWithValue("@dbDate", DateTime.Now.ToString("M/d/yyyy"));
            comm.Parameters.AddWithValue("@dbTime", DateTime.Now.ToString("H:m:s:fff"));
            comm.Parameters.AddWithValue("@dbMacState", true);  
            comm.Parameters.AddWithValue("@dbDirection", 22);
            comm.Parameters.AddWithValue("@dbTemperature", 22);
            comm.Parameters.AddWithValue("@dbHumidity", 22);
            comm.Parameters.AddWithValue("@dbPowerOutput", 22);
            comm.Parameters.AddWithValue("@dbNoiseLevel", 22);
            comm.Parameters.AddWithValue("@dbVibration2", 22);
            comm.Parameters.AddWithValue("@dbWheelStrain", 22);

            try
            {
                sqlConn.Open();
                comm.ExecuteNonQuery();
            }
            catch(SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }  
}

You need to add all parameters to the command even if they don't have a value. You can use DBNull.Value to represent a null value. — D Stanley
– D Stanley, Commented Apr 7, 2015 at 2:34
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Apr 7, 2015 at 5:06

D Stanley · Accepted Answer · 2015-04-07 12:54:00Z

6

You need to add all parameters in the query even if they don't have a value. Change

if (dataPoints.ContainsKey("Gain Status") == true)
{
    comm.Parameters.AddWithValue("@dbGainStatus", true); 
}

to something like

comm.Parameters.AddWithValue("@dbGainStatus", 
    dataPoints.ContainsKey("Gain Status") ? (object)true : DBNull.Value);

And so on for each parameter (changing true to the value you intend to use)

edited Apr 7, 2015 at 12:54

answered Apr 7, 2015 at 2:37

D Stanley

153k12 gold badges189 silver badges257 bronze badges

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

3 Comments

shA.t Over a year ago

And for OP's error, OP should use it for @dbSpeed parameter.

D Stanley Over a year ago

@shA.t It's probably just coincidence that the Speed parameter is the first one that was missing - it should be added to all parameters.

EvergreenNRG Over a year ago

You were right, it was a problem across the board. I corrected all of them and then it all started working as expected. Thanks again for your quick response.

Collectives™ on Stack Overflow

C# and SQL - Must declare the scalar variable writing to DB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related