Searching Records in a Microsoft Access Database with ASP.NET - Part 3

Next we call the Add method of the parameters property of the command class and pass in a newly created OledbParameter object that accepts the name of our parameter that was specified when the command object was created namely @LastName and the value of that parameter which in this case is the text property of ‘textbox1’.

Command.Parameters.Add(New OleDbParameter("@LastName", textbox1.Text))

Now we need to return the records from the database into a DataReader object.

First of all lets declare our OledbDatareader variable ‘DataReader’. Then we’ll fill the datareader by calling the ExecuteReader method of our command object. You’ll notice that rather than using New Keyword as we do with other ADO.NET objects we use the ExecuteReader method of the Command object to create our DataReader object. The Command object references the connection and the SQL statement necessary for the OledbDataReader to obtain data.

Dim DataReader As OleDbDataReader
DataReader = Command.ExecuteReader()

We could iterate through the records in the DataReader row by row but an easier alternative is to place a Gridview control in our code within the HTML body element and bind the DataReader to the Gridview.

We set the Datasource property of the Gridview to our DataReader and then use the DataBind method of our Gridview to bind the DataReader’s results.

GridView1.DataSource = DataReader
GridView1.DataBind()

We then close the connection.

Connection.Close()

Just in case there are no records to display we can set the Gridview’s emptydatatext property to ‘There are no records’ as below.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.Oledb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    Protected Sub SearchRecords(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim Connection As OledbConnection
        Connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
                                     "Data Source=C:\Inetpub\wwwroot\code\Customers.mdb")
        Connection.Open()
        Dim Command As OleDbCommand
        Command = New OleDbCommand("SELECT * FROM tblCustomers WHERE LastName= @LastName", Connection)
        Command.Parameters.Add(New OleDbParameter("@LastName", textbox1.Text))
        Dim DataReader As OleDbDataReader
        DataReader = Command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()

        Connection.Close()

    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Search Records MS Access Database</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
<asp:TextBox id=”textbox1” runat=”server” />
<asp:Button id=”btnSubmit” OnClick=”SearchRecords” />
        <asp:GridView ID=GridView1 runat="server" EmptyDataText=”There are no records”>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

<< Searching Records with ASP.NET Part 2

Searching Records in a Microsoft Access Database with ASP.NET - Part 3

Advertisements

MembersPro

LinksPro Script