Must declare the scalar variable "@LoggedInUser". Error when trying to add parameter to SqlDataSource

Question

I am using SqlDataSource to show records based on the logged in user ,

<asp:SqlDataSource ID="ModifyCustomerDataSource" SelectCommand="SELECT cApplicationNo,cFirstName,cMiddleName,cLastName,nTelNo,nMobileNo,cPanGirNo from Data_Customer_Log where cAddedBy=@LoggedInUser" ConnectionString="<%$ ConnectionStrings:CwizDataConnectionString %>"   runat="server"></asp:SqlDataSource>

And in the Page Load I am adding the parameter as follows

protected void Page_Load(object sender, EventArgs e)
        {
            string user = HttpContext.Current.User.Identity.Name;
            ModifyCustomerDataSource.SelectParameters.Clear();
            ModifyCustomerDataSource.SelectParameters.Add("@LoggedInUser", user.Trim());
        }

But it gives me error

Must declare the scalar variable "@LoggedInUser".
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 "@LoggedInUser".

Can anybody point me out where I am going wrong. Thanks

Is this best practice to use sqldatasource? Just curious. I am working around without using sqldatasource. If you want sample code, please let me know.. — AnandMohanAwasthi, May 17 '12 at 09:37
@AnandMohanAwasthi , I was binding this data to my jqGrid , so this was easier approach for me , well dont have much idea as to is it the best practice , can you ask rich okelly about it.Sample code will help me.Thanks — Priyank Patel, May 17 '12 at 09:41

score 11 · Accepted Answer · answered May 17 '12 at 09:37

11

You do not need the @ when adding the parameter eg:

ModifyCustomerDataSource.SelectParameters.Add("LoggedInUser", user.Trim());

The @ symbol in the query indicates that the following text is the name of a parameter, however the @ is not considered part of the parameter name.

answered May 17 '12 at 09:37

Rich O'Kelly

41,274
9
83
114

1

Too much time and too many unsavory words because of this tonight. – Chad Schouggins Sep 24 '13 at 04:19

Romil Kumar Jain · Answer 2 · 2012-05-17T09:43:24.413

1

<asp:SqlDataSource ID="ModifyCustomerDataSource" SelectCommand="SELECT cApplicationNo,cFirstName,cMiddleName,cLastName,nTelNo,nMobileNo,cPanGirNo from Data_Customer_Log where cAddedBy=@LoggedInUser" ConnectionString="<%$ ConnectionStrings:CwizDataConnectionString %>"   runat="server">

        <SelectParameters>

            <asp:Parameter Name="LoggedInUser" />

        </SelectParameters>

</asp:SqlDataSource>

Verify that you have SelectParameter defined in Sqldatasource as sample mentioned above.

You can set the value of parameter in two ways:

ModifyCustomerDataSource.SelectParameters["LoggedInUser"].DefaultValue = "some value";

or

ModifyCustomerDataSource.SelectParameters.Add("@LoggedInUser", user.Trim()); //@is must here with the parameter name

edited May 17 '12 at 09:43

answered May 17 '12 at 09:36

Romil Kumar Jain

20,239
9
63
92

the problem is solved , rich's answer helped.Thanks for your answer. – Priyank Patel May 17 '12 at 09:39
ModifyCustomerDataSource.SelectParameters.Add("@LoggedInUser", user.Trim()); , was not working , removing @ worked for me – Priyank Patel May 17 '12 at 09:45
It worked because either you have not mentioned tag for "LoggedInUser" in aspx as per my sample. – Romil Kumar Jain May 17 '12 at 09:46

Must declare the scalar variable "@LoggedInUser". Error when trying to add parameter to SqlDataSource

2 Answers2