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!

ASKER CERTIFIED SOLUTION

Easwaran Paramasivam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

SqlDataSource1.SelectParamenters.add(&quot;@FirstName&quot;, &quot;John&quot;). Must declare the scalar variable &quot;@FirstName&quot;.

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