Searching Database with String using ASP.NET and VB

Quick Search

Navigator: Home - Retrieve - Searching Database with String using ASP.NET and VB

This tutorial shows how simple it is to allow users to search a database for matching text they input themselves. VB version.

Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!

Looking for the C#.NET 2005 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!

Searching a website is often taken for granted. Implementing a search facility on a website used to be rather complex. However, as with many things in ASP.NET, it has gotten much easier. This tutorial will show how we can implement a simple search facility to allow users to input text and search a database for matching records.
First, we need to add the following assembly reference:

Imports System.Data.SqlClient
Imports System.Data

In the Web.config, we declare the connection string:

</appSettings>

The ASPX page will consist of a textbox, a button and a repeater control to display the results. It will look something like this:

<form id="form1" runat="server">
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">
<HeaderTemplate><table width="100%"><tr><th>Name</th><th>City</th></tr></HeaderTemplate>
<ItemTemplate>

</ItemTemplate>
<FooterTemplate></table></FooterTemplate>

</asp:Repeater>
</form>

In the code-behind, we will reference a Stored Procedure, which will be something like this:

ALTER PROCEDURE spSearchByString

@SearchString varchar(50)

SELECT [tblOne].theName, [tblOne].theCity
FROM [tblOne]
WHERE ([tblOne].theName LIKE '%' + @SearchString + '%' OR [tblOne].theCity LIKE '%' + @SearchString + '%')
RETURN

The code-behind should look something like this:

Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Request.QueryString("searchString") IsNot Nothing Then

DisplaySearchResults(Request.QueryString("searchString"))

End If

End Sub

Public Sub DisplaySearchResults(ByVal strSearch As String)

Dim cmd As New SqlCommand("spSearchByString", New SqlConnection(ConfigurationManager.AppSettings("ConnString")))
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

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)

Response.Redirect("Default.aspx?searchString=" & Server.UrlEncode(TextBox1.Text))

End Sub

End Class

Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!

Looking for the C#.NET 2005 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!