Must declare scalar variable error

Question

I am trying to display sql select results from code-behind and however this error occurred:

Must declare the scalar variable "@poNum".

Line 75: sdsUsers.SelectParameters.Add("@poNum", poNum.Text.Trim());
Line 76:
Line 77: DataView dvMember = (DataView)sdsUsers.Select(DataSourceSelectArguments.Empty); //here the error
Line 78: DataTable tblMember = dvMember.Table;

Code:

string connectionString2 = ConfigurationManager.ConnectionStrings["WholesaleConnectionString"].ConnectionString;
string selectSql = "SELECT invoiceNum, poNum FROM SendInvoice where poNum = @poNum";

SqlDataSource sdsUsers = new SqlDataSource(connectionString2, selectSql); 
sdsUsers.SelectParameters.Clear(); 
sdsUsers.SelectParameters.Add("@poNum", poNum.Text.Trim());

DataView dvMember = (DataView)sdsUsers.Select(DataSourceSelectArguments.Empty); //error occured here 
DataTable tblMember = dvMember.Table;

for(int i=0;i<tblMember.Rows.Count;i++) { 
            if (i==0){ 
             Label aaa = new Label();
             aaa.Text = tblMember.Rows[i].ItemArray[0].ToString();
            } 
        }

I am stuck with this problems for days and I couldn't find a solution

Consider using the ADO.NET SqlDataAdapter class instead of the ASP.NET SqlDataSource control for basic data retrieval. — Michael Liu
Please try changing this sdsUsers.SelectParameters.Add("@poNum", poNum.Text.Trim()); to this sdsUsers.SelectParameters.AddWithValue("@poNum", poNum.Text.Trim()); — Mike Perrenoud
I also had the at @ symbol in both the SQL Select statement AND the SelectParameters.Add(). It only needs to be in the later. — RADXack

sgeddes sgeddes · Accepted Answer · 2013-01-20T16:28:23

I would try removing the @ from the paramater name (leave it in your SQL though):

sdsUsers.SelectParameters.Add("poNum", poNum.Text.Trim());

or (assuming poNum is an int):

sdsUsers.SelectParameters.Add(new Parameter("poNum", System.TypeCode.Int32, poNum));

Good luck.

Must declare scalar variable error

2 Answers