locked
Must declare the scalar variable "@id". RRS feed

  • Question

  • User481677910 posted

    Hi,

    I am using membership system and trying to use search input box so that only specific member who is login and type in the input box and click on search only his submitted order shows up to him.

     <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:userConnectionString %>" 
                SelectCommand="SELECT [Order_Name], [Date], [Id], [Order#] FROM customer_Order WHERE ([Order_Name] LIKE '%' + @Order_Name + '%' AND Id = @id)">
                <SelectParameters>
                    <asp:ControlParameter ControlID="TextBox1" Name="Order_Name" PropertyName="Text" 
                        Type="String" />
                        
                </SelectParameters>
            </asp:SqlDataSource>

    now after click on searach button i am getting Must declare the scalar variable "@id".

    Kindly help please.

    Thursday, September 12, 2019 11:46 PM

All replies

  • User1120430333 posted

    <br>
    You are missing an parameter Id that the select statement is expecting in a parameterized t-sql statement&lt;br&gt;<br>
    &lt;br&gt;<br>
    It kind of like you are seeing in the example link that you have to apply. You gave one parm. You don't have or supplied the Id parm.&lt;br&gt;<br>
    &lt;br&gt;<br>
    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework<br>
    Friday, September 13, 2019 3:34 AM
  • User409696431 posted

    It is very strange that you posted this.  I gave you the answer in your other post (https://forums.asp.net/p/2159649/6277740.aspx?Re+How+to+get+search+result+for+order+place+by+specific+user+) and you said it worked, which is would not have if you didn't include the second parameter as I showed you.  You have now edited that last reply (where you said it worked) to ask a different question, which is not the way to do it since the thread is marked as answered

    Since your questions indicate lack of knowledge of basic SQL, I suggest you spend some time learning it rather than ask others to write each query for you.  You can find tutorials and documentation easily on the internet.  That doesn't mean you can't ask a question, but basic SELECT  WHERE, AND, and OR operators are something you should know.

    Friday, September 13, 2019 4:39 AM
  • User-719153870 posted

    Hi new2world2015,

    The reason for the error is obviously the lack of the parameter @id in your SelectCommand.

    Please refer to below code and update yours:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:userConnectionString %>" 
                SelectCommand="SELECT [Order_Name], [Date], [Id], [Order#] FROM customer_Order WHERE ([Order_Name] LIKE '%' + @Order_Name + '%' AND Id = @id)">
                <SelectParameters>
                    <asp:ControlParameter ControlID="TextBox1" Name="Order_Name" PropertyName="Text" 
                        Type="String" />
                     <asp:ControlParameter ControlID="TextBox2" Name="id" PropertyName="Text" 
                        Type="String" />   
                </SelectParameters>
            </asp:SqlDataSource>

    Best Regard,

    Yang Shen

    Friday, September 13, 2019 5:42 AM
  • User753101303 posted

    Hi,

    Not directly related but for Web Forms you could also consider using https://docs.microsoft.com/en-us/aspnet/web-forms/overview/presenting-and-managing-data/model-binding/retrieving-data

    Friday, September 13, 2019 7:21 AM
  • User481677910 posted

    Thank you. But there is no textbox2 in the content only search input and button for search

           <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Button" />
     <SelectParameters>
                    <asp:ControlParameter ControlID="TextBox1" Name="Order_Name" PropertyName="Text" 
                        Type="String" />
                     <asp:ControlParameter ControlID="TextBox1" Name="id" PropertyName="Text" 
                        Type="String" />   
                </SelectParameters>

    I tried above and it gave me this error

    Conversion failed when converting from a character string to uniqueidentifier.
    

    I am using membership asp.net usually get the id result by adding

    cmd.Parameters.AddWithValue("@Id", Membership.GetUser().ProviderUserKey);

    but since I am using sqldatasource on aspx page and not in .cs. How this membership get user thing will work with sqldatasource

    Friday, September 13, 2019 10:06 AM
  • User753101303 posted

    You could likely use https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.sqldatasource.selecting?view=netframework-4.8 and have something such as :
    e.Command.Parameters.AddWithValue("@Id",Membership.GetUser().ProdiverUserKey);
    to populate this parameter from server side code rather than going through a textbox...

    Edit: if you want to keep the current approach I believe you have to add theDbType or Type attribute to the ControlParameter so that it knows it should be handled as a Guid rather than as a string.

    Friday, September 13, 2019 10:52 AM