C# SqlDataAdapter Must declare the scalar variable Sql Exception

Question

New to C# and working on a Windows Form application. I am attempting to execute an update query against a SQL database, but keep running into "Must declare the scalar variable" error and I do not understand why.

The below code successfully opens the connection. My update statement is valid. Looking through a lot of posts on this topic and I am just not seeing my error... any help would be appreciated.

public void SetJobStatus(long JobId)
{
    string strSql = "update Jobmaster set jobstatus = 5 where equid = @stationId AND ID <> @jobId AND OfflineEntry = 0;";

    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = GlobalVars.connString;
        conn.Open();
        // use the connection here, and check to confirm it is open
        if (conn.State != ConnectionState.Open)
        {
            if (conn != null)
            {
                conn.Close();
            }
            conn.Open();
        }
        SqlCommand command;
        SqlDataAdapter adapter = new SqlDataAdapter();

        command = new SqlCommand(strSql, conn);
        //below AddWithValue gives error:
        //System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@stationId".'
        //command.Parameters.AddWithValue("@stationId", 1);
        //command.Parameters.AddWithValue("@jobId", JobId);
        
        //next I tried this, and the same error:
        //System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@stationId".'
        command.Parameters.Add("@stationId", SqlDbType.Int);
        command.Parameters["@stationId"].Value = 1;
        command.Parameters.Add("@jobId", SqlDbType.Int);
        command.Parameters["@jobId"].Value = JobId;

        adapter.UpdateCommand = new SqlCommand(strSql, conn);
        adapter.UpdateCommand.ExecuteNonQuery();
    }
}

What if you just do `command.ExecuteNonQuery()`? Adapter is actually not needed to execute a command? Which line the code throws the exception? — Chetan, Oct 05 '20 at 11:52
usually this means the value is `null` or the parameter wasn't added, although I can't see that happening here; side note: this could be a **lot** easier with "Dapper" (free on NuGet): `conn.Execute(strSql, new { stationId = 1, jobId = JobId });` — Marc Gravell, Oct 05 '20 at 11:52
You're declaring `adapter.UpdateCommand = new SqlCommand(strSql, conn);` after you've added your parameters to `command`, which means you aren't actually adding any parameters when you do `ExecuteNonQuery`. If you do `adapter.UpdateCommand = command;` it should work. — WSC, Oct 05 '20 at 11:53
A SQL Command has four queries 1) Select 2) Update 3) Insert 4) Delete. For the UpdateCommand to work you need all four queries. A CommandBuilder take a Select Query and automatically generates the other three. See : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommandbuilder?view=dotnet-plat-ext-3.1 — jdweng, Oct 05 '20 at 11:55
Apologies for not indicating the error. When I execute the code as shown above, the declare scalar variable error occurs on "adapter.UpdateCommand.ExecuteNonQuery()". — argent65, Oct 05 '20 at 12:59

score 2 · Answer 1 · answered Oct 05 '20 at 13:59

I have checked your code and it's required some changes. Please try to run below code:

public void SetJobStatus(int JobId)
{
    string strSql = "update Jobmaster set jobstatus = 5 where equid = @stationId AND ID <> @jobId AND OfflineEntry = 0;";

    using (SqlConnection conn = new SqlConnection())
    {
        try
        {
            conn.ConnectionString = GlobalVars.connString;
            conn.Open();
            SqlCommand command = new SqlCommand(strSql, conn);
            command.CommandType = CommandType.Text;
            command.Parameters.Add("@stationId", SqlDbType.Int);
            command.Parameters["@stationId"].Value = 1;
            command.Parameters.Add("@jobId", SqlDbType.Int);
            command.Parameters["@jobId"].Value = JobId;
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }
}

Tips:

Always close connection after completion of task or in case of error.

The connection will automatically be closed due to the `using` statement: https://stackoverflow.com/a/18588084/2850543 — Millie Smith, Oct 05 '20 at 23:25

score 0 · Accepted Answer · edited Oct 05 '20 at 14:58

Thanks to everyone who chimed in here. WSC's comment did the trick- changing adapter.UpdateCommand = command; worked. I tried three variations of adding parameters after making WSC's change- two of them worked, one did not.

My revised code is below. I have all three variations listed in the code- hopefully this will help somebody else out.

public void SetJobStatus(long JobId)
{
    string strSql = "update Jobmaster set jobstatus = 5 where equid = @stationId AND ID <> @jobId AND OfflineEntry = 0;";

    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = GlobalVars.connString;
        conn.Open();
        // use the connection here, and check to confirm it is open
        if (conn.State != ConnectionState.Open)
        {
            if (conn != null)
            {
                conn.Close();
            }
            conn.Open();
        }
        SqlCommand command;
        SqlDataAdapter adapter = new SqlDataAdapter();

        command = new SqlCommand(strSql, conn);
        
        //works
        command.Parameters.AddWithValue("@stationId", GlobalVars.stationId);
        command.Parameters.AddWithValue("@jobId", JobId);

        //works
        //command.Parameters.Add("@stationId", SqlDbType.Int);
        //command.Parameters["@stationId"].Value = 5;
        //command.Parameters.Add("@jobId", SqlDbType.Int);
        //command.Parameters["@jobId"].Value = JobId;

        //throws error at adapter.UpdateCommand.ExecuteNonQuery line:
        //'The parameterized query '(@stationId int,@jobId int)update Jobmaster set jobstatus = 5 wh' expects the parameter '@stationId', which was not supplied.'
        //command.Parameters.Add("@stationId", SqlDbType.Int, 5);
        //command.Parameters.Add("@jobId", SqlDbType.Int, (int)JobId);

        adapter.UpdateCommand = command;
        adapter.UpdateCommand.ExecuteNonQuery();
    }

}

C# SqlDataAdapter Must declare the scalar variable Sql Exception

2 Answers2