Database search using textbox input (including masterpage use)

Back to the tutorials list

Introduction

This tutorial will teach you how to create a page where a user can search the database based on what they type into a textbox. Our examples will show how to let a user search based on someone's last name.

 We will show two examples: one where the search results are on the same page as the search form, and one with the search results on a different page. The latter example has an extra complication that arises when a Master Page is being used, and you will learn how to handle that.

If you did not already do the tutorial Sending Form Input to a Database  then do it now, before continuing with this tutorial, since we are going to search the database created in that tutorial.

Search box and results on same page

Open VWD and with it open the website you previously created (most likely you called it MyTutorialsWebsite).

In the Solution Explorer pane, right-click on your website name (at the very top), click Add New Item, highlight Web Form, highlight Visual Basic as the code choice at the upper left, check the box at lower right to Place Code in Separate File, change the Default name offered to Search.aspx, and click the Add button. This should open your new blank page in a new tab.

Set up the search textbox and search button

Open the search.aspx page. Being an aspx page, it already has a form on it. In Design View, type something like "Enter the person's last name:" and then drag a TextBox Control on to the form (ASP.NET Controls / Standard / TextBox). This will be the field a user enters their search term into.

In the Properies window, give the textbox a useful name for its ID of "TxtBxLastName". You would use such a name if later on the Results.aspx page you wanted to show the results of a search based on someone's last name in the database (and, of course, the database needs to have a person's last name as a field in each record).

We set up our database earlier to include a last name field, so we will be searching that.

You need a submit button for the form, so Drag a Button onto the form (ASP.NET Controls / Standard / Button). Highlight the Button, go to Properties pane and change the Text attribute of the button to a meaningful name such as "DoTheSearch" , and give it a meaningful ID like "BtnSearch".

Set up a gridview to show the results on the same page as search box

In Design View, drag a GridView from the Data section of the toolbox onto the page. In the properties pane, give the Gridview an ID of GVSearchResults, and set the Empty Data Text to read "Sorry, no records have that last name". If the common gridview tasks box is not showing, click the expander arrow, choose autoformat, and select a color scheme you like.

In the "Choose Data Source" box select "New Data Source", click on the SQL Database Icon and specify a sensible ID for the SQL datasource like "SDSGridviewSearch" and click OK. For the connection string, you should find the connection string you already created in the drop down list. It is titled "MyTutorialsDatabaseConnectionString". That is the one to select, then click Next.

Check the "Specify Columns from Table or View" and your "FormInputTable" should be available to choose. You can either select * to display all the fields in the database, or check the individual fields you would like to display. Click the Advanced button and select the "Generate Insert, Update, and Delete Statements" if you would like the user to be able to edit and delete results in the database (in many situations you would not want this, but if you were creating an admin page for your own use, you probably would want those abilities). Important Note: You must include the primary key (in this case ID) or the box to check the Generate Insert, Update, and Delete statements will be greyed out.

Now here is the important part. Click the WHERE button. Choose the field you want to search on from the column dropdown list. This should be a field that is expecting the input text from the search form. In our case, we have set things up for the user to search by a last name, so choose LName

Leave the Operator set at "=" for this particular search. Choose "Control" as the Source, and select the ID of your search textbox ("TxtBxLastName") from the ControlID dropdown list.  Click Add, OK, Next, TestQuery, OK, Finish.

Then click OK to close that window, click Next, Test Query, and Finish

From the Gridview Tasks, select "Enable Sorting" (and "Enable Editing" and "Enable Deleting if you choose). Click Autoformat and choose a format scheme that appeals to you. Save the page.

Finally we need to set some VB code to run when the search button is clicked.

In the Solution Explorer, click the little + by your search.aspx file to expose the associated VB code-behind file just below it. Double-click that VB file to open it.

From the upper left drop down menu choose the BtnSearch control, and from the Declarations drop down menu on the right, choose Click.

That sets up the subroutine boilerplate. Enter the following code into the subroutine right at the spot your cursor is sitting (you will see Intellisense offering to assist you and you can use your Tab key to select from what it offers):

GVSearchResults.DataBind()

Save the code behind page. Save the Search.aspx page, and while the Search.aspx page is open, do file / View in Browser. Test your search page by entering a last name in the textbox and doing a search. You should be able to find Smith's record, if you left it in the database in an earlier tutorial.

Search box and results each on different pages:

Things change somwhat if you want to display the Gridview results on a different page.

Create a  Results.aspx page using the same procedure used above for creating the search.aspx page. We will display data using a Gridview on this page using the parameters inputted by the user on the search page.

Open the Search.aspx page you made above. For this example, remove the Gridview from that page because we will be putting the gridview on the Results.aspx page for this part of the tutorial. Then open its code-behind page, highlight the subroutine we had created there, delete it, and save the page.

Back on the search page, click on the button to highlight it, and under Behavior, set the value of PostBackURL to Results.aspx. This will cause the value entered by a user into the textbox to become the input parameter for the search results on the Results.aspx page.

Save the page and now open the Results.aspx page in Design View. Drag a gridview onto the page. Once again, in the properties pane, set the Empty Data Text to "Sorry, no records contain that last name", change the ID from Gridview1 to GVSearchResults and autoformat the gridview to your liking.

Click the expander arrow to get to Gridview Tasks. In the "Choose Data Source" box select "New Data Source", click on the SQL Database Icon and specify a sensible ID for the SQL datasource like SDSGridViewTest and click OK. For the connection string, you should find the connection string you already created in the second basic tutorial. It is titled "MyTutorialsDatabaseConnectionString". That is the one to select, then click Next.

Check the "Specify Columns from Table or View" and your "FormInputTable" should be available to choose. You can either select * to display all the fields in the database, or check the individual fields you would like to display. Click the Advanced button and select the "Generate Insert, Update, and Delete Statements" if you would like the user to be able to edit and delete results in the database (in some situations you would not want this). Important Note: You must include the primary key (in this case ID) or the box to check the Generate Insert, Update, and Delete statements will be greyed out.

Now here is the important part. Click the WHERE button. Choose the field you want to search on from the column dropdown list. This should be a field that is expecting the input text from the search form. In our case, we have set things up for the user to search by a last name, so choose LName.

Leave the Operator set at "=" for this particular search. Then a key thing is to choose "Form" as the Source, and type the ID of the search form field on the search.aspx page (remember we named the text field on the search form "TxtBxLastName") in the Parameter Properties Form field box.  NOTE: If you happen to be doing this using a Master Page the method in this paragraph will fail. See the comments at the bottom of the tutorial.

Click Add, OK, Next, TestQuery, OK, Finish.

If you desire, in the Gridview Tasks, enable editing, sorting, and deleting.

Save the page and close it.

Open Search.aspx, preview in browser, and test your search by searching for last name of Smith. If you still have one or more records with LName= Smith in your database you should see those records in the gridview on the results page.

If you are using a Master Page with search box and results on different pages

There is an issue passing the value of a textbox ID from the search page to the results page when the search page control is in the ContentPlaceHolder of a Master Page. In this case setting up the WHERE clause as described above will fail. So what we will do is put a textbox on the results page and give it the same ID as was used on the search page, "TxtBxLastName"

So on the results page, drag in a textbox, and give it the ID "TxtBxLastName"

Then on the Results page, drag in a gridview, configure your gridview as usual, configure its datasource as usual, and set up the WHERE choosing Source as  Control and TxBxLastName from the drop-down list for the parameter properties of the Control ID.

The last step you need some VB code to run when the Results page loads that assigns the value to TxtBxLastName that is the same as was entered in that textbox on the search page.

In Solution Explorer, click the little +  by the Results.aspx page, which shows the associated VB page just below. Double-click the VB code-behind page to open it. Then in the upper left dropdown list select Page Events and the upper right drop down list select Load.

You will see the bold subroutine boilerplate code below already written, so just copy and paste the intervening code into the subroutine. The finished result should look like:

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

If Not Page.PreviousPage Is Nothing Then

Dim placeHolder As Control = PreviousPage.Controls(0).FindControl("ContentPlaceHolder1")

Dim SourceTextBox As TextBox = CType(placeHolder.FindControl("TxtBxLastName"), TextBox)

If Not SourceTextBox Is Nothing Then

TxtBxLastName.Text = SourceTextBox.Text

End If

End If

End Sub

That should complete making everything work when a Master Page is Involved.

Save the Results.aspx and its code-behind page and close them. Then File / View in Browser your Search.aspx page and test it. Clicking the search button should bring up the results on the Results.aspx page.

Congratulations. You now know how to set up a web page that allows searching based on user input to a textbox.