Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the pubs database with SQL Server and save it as a ‘.aspx’ page.
This code will, upon loading the page, present you with a Text Box, where you will enter a last name. In this particular search, the lastname field in the database is what is searched. Then, based on the search criteria, will search for all authors with the last name chosen, displaying all items in a Datagrid.
As you will also see, if you type in a name that is not actually listed in the table, you will see how to handle this problem with an if/then statement.
SQL Server
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<script language="VB" runat="server">
Sub doQuery(Source as Object, E as EventArgs)
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
Dim strName as string
strName=Name.Text
Label1.Text="You searched for : " + strName
Dim MySQL as string
MySQL="select * from Authors where au_lname =’" & strName & "’"
panel1.visible="true"
label2.text=MySQL
MyConnection = New SqlConnection("server=localhost;uid=sa;pwd=pwd;database=pubs;")
MyCommand = New SqlDataAdapter(MySQL, MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, "Authors")
if ds.tables(0).rows.count>0 then
MyDataGrid.DataSource=ds.Tables("Authors").DefaultView
MyDataGrid.DataBind()
else
lblResults.text="Sorry – no Data Found!"
End if
End Sub
</script>
<title>ASP Express.com – Search a Database – Display Results in a DataGrid Control</title>
</head>
<body>
<h3><font face="Verdana">Search a Database – Display Results in a DataGrid Control</font></h3>
<Form id="form" runat="server">
<asp:textbox id="Name" runat="server" />
<asp:Button id="button" Text="Search" onclick="doQuery" runat="server" />
</Form>
<b>Hint:</b> :<br>
<i>Type in White, Greene, Hunter, Stringer, Smith or Carson</i>
<asp:Panel ID="Panel1" runat="server" visible="False">
<hr>
<div align="center"><b><i>Search Results</i></b>
<asp:Label ID="label1" runat="server" /><br>
<asp:Label ID="label2" runat="server" /><p>
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</div>
<b><i><asp:Label ID="lblResults" runat="server" /></i></b>
</asp:Panel>
</body>
</html>
With MS Access, replace the code at the top with:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<script language="VB" runat="server">
Sub doQuery(Source as Object, E as EventArgs)
Dim DS As DataSet
Dim MyConnection As OleDbConnection
Dim MyCommand As OleDbDataAdapter
Dim strName as string
strName=Name.Text
Label1.Text="You searched for : " + strName
Dim MySQL as string
MySQL="select * from Authors where au_lname =’" & strName & "’"
panel1.visible="true"
label2.text=MySQL
MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\biblio.mdb") & ";")
MyCommand = New OleDbDataAdapter(MySQL, MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, "Authors")
if ds.tables(0).rows.count>0 then
MyDataGrid.DataSource=ds.Tables("Authors").DefaultView
MyDataGrid.DataBind()
else
lblResults.text="Sorry – no Data Found!"
End if
End Sub
</script>