ASP.NET C# Must declare the scalar variable

Question

I am trying to populate a GridView using a method called PopulateGrid() (below) but keep getting the same server error "Must Declare the scalar variable "@QUALID".

public void PopulateGrid()
    {
        String val = TextBox2.Text;

        String sql = "SELECT QLEVELNAME FROM Qual_Levels WHERE QUALID=@QUALID";
        SqlCommand cmd = new SqlCommand(sql,
           new SqlConnection(ConfigurationManager.ConnectionStrings["RecruitmentDBConnString"].ConnectionString));
        
        cmd.Parameters.Add(new SqlParameter("QUALID", val));

        cmd.Connection.Open();


        SqlDataAdapter da = new SqlDataAdapter(sql, cmd.Connection);

        DataSet ds = new DataSet();
        da.Fill(ds, "Qual_Levels");


        SelectionGrid.DataSource = ds;
        SelectionGrid.DataBind();


        ds.Dispose();
        da.Dispose();
        cmd.Connection.Close();
        cmd.Connection.Dispose();
    }

The GridView is being declared like so..

<asp:GridView ID="SelectionGrid"
            autogeneratecolumns="False" 
            runat="server" CellPadding="4" 
            ForeColor="#333333" GridLines="None" DataKeyNames="QUALID">
            
            <Columns>
                <asp:BoundField DataField="QLEVELNAME" HeaderText="Level Name" 
                    ReadOnly="True" SortExpression="name" />
            </Columns>
</asp:GridView>

After trying countless things and trawling through the forums I keep coming up against the same error.

Server Error in '/' Application.

Must declare the scalar variable "@QUALID".

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@QUALID".

Source Error:

Line 282: DataSet ds = new DataSet();

Line 283: da.Fill(ds, "Qual_Levels");

If anyone can shed any light on the situation I would be really grateful!

Answer 1

This:

cmd.Parameters.Add(new SqlParameter("QUALID", val));

should be this:

cmd.Parameters.Add(new SqlParameter("@QUALID", val));

---

Sorry, typed too quick, try:

cmd.Parameters.AddWithValue("@QUALID", val);

---

OK, you have a slightly more fundamental issue in your code. You create a command object, but then you pass the SQL string and the connection for the command into your dataadapter, where it will execute your sql string with no parameters on it's connection.

I haven't used dataadapters too much, but I think you need to set the parameters on the select command of your adapter.

Answer 2

Try adding the @ to your sql param like so

 cmd.Parameters.Add(new SqlParameter("@QUALID", val));

Answer 3

You are missing the "@" where you add the parameter:

SqlParameter("@QUALID", val)

Answer 4

change

cmd.Parameters.Add(new SqlParameter("QUALID", val));

to either

cmd.Parameters.Add(new SqlParameter("@QUALID", val));

or

cmd.Parameters.Add("@QUALID", SqlDbType.WhatFitsYourDB).Value = val; 

and you should be good to go. Your problem is that you are missing a '@' in the paramter name