SQL search in gridview or other way: Help please

Discussion in 'Databases' started by Rae, Feb 16, 2009.

0
  1. #1
    I am using Visual Studio 2008 (C#) and trying to add a gridview to my webpage that will return the results from a sql search based on typed info in a textbox.text search. It will let me only do a search of ONE field. I'd like the textbox.text search to search ALL the fields in the table. So for example, if they searched the name "Johnson", it would return all the fields whether the person's first name was johnson or the last name was johnson or their city name was johnson.

    Does anyone know how to do this? I tried changing the code below to add another field like so:
    SelectCommand="SELECT * FROM [authors] WHERE ([au_lname] LIKE '%' + @au_lname + '%')OR ([au_fname] LIKE '%' + @au_fname + '%')">

    but I get an error:
    Must declare the scalar variable "@au_fname".

    It only works like so:
    SelectCommand="SELECT * FROM [authors] WHERE ([au_lname] LIKE '%' + @au_lname + '%')">

    I will try any method that will let me search all fields. Any help would be appreciated .

    Rachel

    my code:
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="au_id" DataSourceID="SqlDataSource1">
    <Columns>
    <asp:BoundField DataField="au_id" HeaderText="au_id" ReadOnly="True"
    SortExpression="au_id" />
    <asp:BoundField DataField="au_lname" HeaderText="au_lname"
    SortExpression="au_lname" />
    <asp:BoundField DataField="au_fname" HeaderText="au_fname"
    SortExpression="au_fname" />
    <asp:BoundField DataField="phone" HeaderText="phone" SortExpression="phone" />
    <asp:BoundField DataField="address" HeaderText="address"
    SortExpression="address" />
    <asp:BoundField DataField="city" HeaderText="city" SortExpression="city" />
    <asp:BoundField DataField="state" HeaderText="state" SortExpression="state" />
    <asp:BoundField DataField="zip" HeaderText="zip" SortExpression="zip" />
    <asp:CheckBoxField DataField="contract" HeaderText="contract"
    SortExpression="contract" />
    </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:pUBSConnectionString4 %>"

    SelectCommand="SELECT * FROM [authors] WHERE ([au_lname] LIKE '%' + @au_lname + '%')OR ([au_fname] LIKE '%' + @au_fname + '%')">
    <SelectParameters>
    <asp:ControlParameter ControlID="txtList" DefaultValue="txtList.Text"
    Name="au_lname" PropertyName="Text" Type="String" />
    </SelectParameters>
    </asp:SqlDataSource>
     
    Rae, Feb 16, 2009 IP
  2. Rae

    Rae Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I found the solution playing with the gridview control. I never read anywhere that you could set up your sql statement with controls through that wizard. Discovered it by accident.

    After the gridview built it using AND, I edited it in the page and changed AND to OR. I don't think it lets you use OR in the wizard.

    <ContentTemplate>
    Searching: <asp:Label ID="Label1" runat="server" Text="Label" ></asp:Label>

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333"
    GridLines="None">
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <Columns>
    <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
    <asp:BoundField DataField="ISBN" HeaderText="ISBN" SortExpression="ISBN" />
    <asp:BoundField DataField="Author" HeaderText="Author"
    SortExpression="Author" />
    <asp:BoundField DataField="Category" HeaderText="Category"
    SortExpression="Category" />
    </Columns>
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString6 %>"
    SelectCommand="SELECT [Title], [ISBN], [Author], [Category] FROM [au_Products] WHERE (([Title] LIKE '%' + @Title + '%') OR ([ISBN] LIKE '%' + @ISBN + '%') OR ([Author] LIKE '%' + @Author + '%') OR ([Category] LIKE '%' + @Category + '%'))">
    <SelectParameters>
    <asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
    Name="Title" PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
    Name="Author" PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
    Name="ISBN" PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="txtSearch" DefaultValue="%txtSearch.Text"
    Name="Category" PropertyName="Text" Type="String" />
    </SelectParameters>
    </asp:SqlDataSource>

    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <asp:Button ID="btnSearch" runat="server" Text="Search" />
    </ContentTemplate>

    This was built with the GridView control wizard. So its all in the main aspx page not the code behind.
     
    Rae, Feb 17, 2009 IP