Searching with multiple keywords

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Searching with multiple keywords

Author

Topic

bhavoo
Starting Member

5 Posts

Posted - 2007-10-29 : 13:22:56

I have started to develop a page for a database and I am building it with Visual Web Developer 2005 Express Edition.

I have 2 search fields to start with and possibly more if I can figure this one out. I started with one field and managed to create a search but when I added another field I have to match up exact records otherwise it won’t work. I want to be able to search with one field if I want. I’m sure this was a common problem for programmers when they first started.

Below is the source for my page.



<body>
    <form id="form1" runat="server">
    <div>
           
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RUGBYSCHOOLConnectionString %>"
            SelectCommand="SELECT * FROM [t_main] WHERE (([FirstNames] LIKE '%' + @FirstNames + '%') AND ([LastName] LIKE '%' + @LastName + '%'))">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="FirstNames" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="TextBox2" Name="LastName" PropertyName="Text" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>
        <br />
    
    </div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
        <br />
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
        <br />
        <asp:Button ID="Button1" runat="server" Text="Button" /><br />
        <br />
        <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
        </asp:GridView>
    </form>
</body>
</html>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-29 : 13:49:17

Just change the AND to an OR in your WHERE clause; it is just boolean logic. You have written "return rows where both firstname AND lastname match", when I think what you want is "return rows where either firstname OR lastname match."

This may also give you some more ideas:

http://weblogs.sqlteam.com/jeffs/archive/2004/11/02/2460.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

bhavoo
Starting Member

5 Posts

Posted - 2007-10-31 : 06:39:30

Hi Jeff i didnt understand that code you showed me, thanks for the reply i created this for my button:

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        If Trim(TextBox1.Text) = "" And Trim(TextBox2.Text) = "" Then

            Return

        End If
        If Trim(TextBox1.Text) <> "" And Trim(TextBox2.Text) <> "" Then
            SqlDataSource1.SelectCommand = "Select * from [t_main] WHERE (([FirstNames] LIKE '%' + @FirstNames + '%') AND ([LastName] LIKE '%' + @LastName + '%'))"

        ElseIf Trim(TextBox2.Text) <> "" And Trim(TextBox1.Text) = "" Then
            SqlDataSource1.SelectCommand = "SELECT * FROM [t_main] WHERE ([LastName] LIKE '%' + @LastName + '%')"

        Else
            SqlDataSource1.SelectCommand = "SELECT * FROM [t_main] WHERE ([FirstNames] LIKE '%' + @FirstNames + '%')"
        End If
        MsgBox(SqlDataSource1.SelectCommand)
    End Sub

The section which finds a match for both of the words works. The information displays in the datagrid. However it does not work for statements after this. There is nothing wrong with the sql but the statement is probably illogical. The message box shows the statement being outputed depending what i do. But there is no info in the datagrid. Can i do queries like this?

ann
Posting Yak Master

220 Posts

Posted - 2007-11-07 : 17:03:11

in your Trim(TextBox1.Text) = "" statement, you are assuming there is an empty string, when in fact it can be null. Change it to something like this (sorry this code is c# - just convert to vb):

TextBox1.Text.Trim()Length < 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources