Avatar of dejandejanovic
dejandejanovic
 asked on

SqlDataSource1.SelectParamenters.add("@FirstName", "John"). Must declare the scalar variable "@FirstName".

Hello,
I'm trying to change SqlDataSource control behind code, in vb.net, with Button. But just cannot get throught problem 'Must declare the scalar'. I was reading so many topics, but just cannot get results.

Asp.net code:
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<br />
<asp:DropDownList ID="DropDownListFirstName" runat="server">
    <asp:ListItem>John</asp:ListItem>
    <asp:ListItem>Paul</asp:ListItem>
    <asp:ListItem>James</asp:ListItem>
</asp:DropDownList>
<br />
<asp:DropDownList ID="DropDownListSecondName" runat="server">
    <asp:ListItem>Brown</asp:ListItem>
    <asp:ListItem>White</asp:ListItem>
</asp:DropDownList>
<br />
<asp:DropDownList ID="DropDownListTown" runat="server">
    <asp:ListItem>New York</asp:ListItem>
    <asp:ListItem>London</asp:ListItem>
    <asp:ListItem>Paris</asp:ListItem>
</asp:DropDownList>
<br />
<asp:Button ID="Button1" runat="server" Text="Button" />
<br />
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>

<div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
        EmptyDataText="There are no data records to display.">
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
                SortExpression="FirstName" />
            <asp:BoundField DataField="SecondName" HeaderText="SecondName" 
                SortExpression="SecondName" />
            <asp:BoundField DataField="Town" HeaderText="Town" SortExpression="Town" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:database21ConnectionString %>"
        
        SelectCommand="SELECT [FirstName], [SecondName], [Town] FROM [test]">
    </asp:SqlDataSource>
</div>
</ContentTemplate>
</asp:UpdatePanel>
</form>

Open in new window


Vb.net code:
Imports System.Data
Imports System.Data.SqlClient
Partial Class zest
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        SqlDataSource1.SelectCommand = "Select * From test Where FirstName = @FirstName"
        SqlDataSource1.SelectParameters.Add("@FirstName", "John")
        SqlDataSource1.DataBind()
    End Sub
End Class

Open in new window


Thanks in advance for help!
ASP.NET

Avatar of undefined
Last Comment
dejandejanovic

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Easwaran Paramasivam

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dejandejanovic

ASKER
I have something which so far works. But, still need to test it with more complex sql query.
SqlDataSource1.SelectParameters.Add("FirstName", DropDownListFirstName.Text)

Open in new window


But, when page is open, first attempt to filter gridview works. But, when try to select another value I get an error:
The variable name '@FirstName' has already been declared. Variable names must be unique within a query batch or stored procedure.
Solution:
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        SqlDataSource1.SelectParameters.Clear()
    End Sub

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23