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

Barstow

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.. – Phail

@Phail , 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 – Barstow

Answer 1

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.

Lanford

Too much time and too many unsavory words because of this tonight. – Gelasias

Answer 2

<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

Wryneck

the problem is solved , rich's answer helped.Thanks for your answer. – Barstow

ModifyCustomerDataSource.SelectParameters.Add("@LoggedInUser", user.Trim()); , was not working , removing @ worked for me – Barstow

It worked because either you have not mentioned <SelectParameters> tag for "LoggedInUser" in aspx as per my sample. – Wryneck