When users do not know what they are looking for, the option to search through a database can come quite in handy. In this tutorial, we will cover how to search a SQL database with a string in Visual Basic.

Setup

If you have not already done so, open Visual Studio and start a new web project.

We chose Server Intellect for its cloud hosting, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

The Concept…

Databases usually contain a large amount of information. This information often needs to be filtered before viewed. There are many ways to sort and filter database information. In our example we will be searching through a database of cities. The visitor will be allowed to enter in text which will be used to filter through the different cities in our database.

Step 1.

The first step we will take is creating the database. You will need a database with a simple two column table that contains an ID, City, and a Name column. The definition used to create this database table is as follows:

Step 2.

The next step we will take is creating our user interface. All we want for our user is the ability to type in a letter or two and click search. Drag and drop a label, a textbox next to that label, a button underneath the label/textbox, a repeater control underneath that group and a SqlDataSource and Label underneath the repeater control.

Modify your code so it appears like the code below:
Code Block
Ask a Question
Download Source
Default.aspx
<div>
    Search:
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
    <asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" /><br />
    <br />
    <asp:Repeater ID="Repeater1" runat="server" 
        onitemcommand="Repeater1_ItemCommand">
        <HeaderTemplate>
            <table width="50%">
                <tr>
                    <th>
                        Name
                    </th>
                    <th>
                        City
                    </th>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <%#DataBinder.Eval(Container.DataItem, "Name")%></a>
                </td>
                <td>
                    <%#DataBinder.Eval(Container.DataItem, "City")%>
                </td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table></FooterTemplate>
    </asp:Repeater>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT * FROM [People]"></asp:SqlDataSource>
    <asp:Label ID="lblStatus" runat="server"></asp:Label>
</div>
The design view of the project will appear as follows:

Step 3.

Now, we will need to add our stored procedure to the database. Switch to the server explorer and right click on the Stored Procedures folder located within the Database files. You will select, “Add New Stored Procedure.

Copy and paste the following code there:
Code Block
Ask a Question
Download Source
SQL Stored Procedure
ALTER PROCEDURE dbo.spSearchByString
 
@SearchString varchar(50)
AS 
SELECT [People].Name, [People].City
FROM [People]
WHERE ([People].Name LIKE '%' + @SearchString + '%' OR [People].City LIKE '%' + @SearchString + '%')
RETURN
What this stored procedure does is select two parameters, Name and City from the People table where name and city match the search string that is limited to 50 characters.

Step 4.

The last step, but most definitely not least is the functionality of the interface. Double click on your Search button so that Visual Studio creates the on_click method in the code behind. Within the code behind, we will need to have the appropriate binding statements added so that our stored procedure is used. To do this, we are to make a DisplaySearchResults method that takes one string parameter called strSearch. The code is as follows for this particular method:
Code Block
Ask a Question
Download Source
Default.aspx.vb
Public Sub DisplaySearchResults(ByVal strSearch As String)
Dim cmd As New SqlCommand("spSearchByString", New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"))
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SearchString", strSearch)
cmd.Connection.Open()
 
Repeater1.DataSource = cmd.ExecuteReader()
Repeater1.DataBind()
 
cmd.Connection.Close()
cmd.Connection.Dispose()
End Sub
What this does is connect to the database and opens the connection, sets up the command type to be the stored procedure we just made and commits the search. Whatever comes of the search is bound to the repeater control. After everything is over, the connection is closed and disposed.

Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.

Save your changes and switch over to your design view of the project. Double click on the repeater control so that you may have the Item Command method created in the code behind. We aren’t going to include anything within the method, but if you intend to do anything specific with the repeater control you will need this. Now that you are back to the code behind, we are going to add some items to the button on_click method.

Modify the method so that it contains the following code:
Code Block
Ask a Question
Download Source
Default.aspx.vb
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
 
If TextBox1.Text = "" Or TextBox1.Text Is Nothing Then
 
lblStatus.Text = "Please enter valid search criteria"
Else
 
DisplaySearchResults(TextBox1.Text)
lblStatus.Text = ""
TextBox1.Text = "Request Submitted..."
End If
End Sub
What this does is make sure that something was entered by the user. If nothing was entered the user will be told to enter valid search criteria. Otherwise, so long as something is entered DisplaySearchResults method is called. Nothing is displayed in the status label and the search text box displays the message stating that the search request was submitted.

Save and run the program. When the user enters b into the text box and hits enter, notice how it selects all possible entries containing a b either in the name or city field, or even both.

A Few Last Words…

There are various applications to today’s lesson; it all depends on what you are looking for in terms of functionality and results. Learning how to control and manipulate a database is easy with ASP.NET and we intend to be there every step of the way to help! Thank you for being a valued reader and join us next time for additional database tutorials!

SearchQueryString_VB.zip