There have been many requests for this particular type of example in the newsgroups and forums. What we’re doing here is doing a representation of the classic Windows app which accesses a database, moving to the first record of a table when it runs. Then, the user can navigate from one record to the next (or also to the first or last record, in this case) with the mere click of a button.
We’re using the SQL Server managed provider, accessing the Northwind Database Employee Table (though here, it is altered/edited by other processes in the code samples, of course).
When the app first starts, it accesses the Minimum and the Maximum ID from the table and uses labels to store the information during the life of the app.
Just copy this to your new file, change the provider/and/or connection info and you’re off and running.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<script language="VB" runat="server">
Dim intMinID, MinRecNo, MaxRecNo as Integer
Dim sLast, sFirst, sTitle, sHireDate, sAddress, sCity, sExtension, sReportsTo as String
Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=NorthWind"
Dim MySQL as string = "Select Min(EmployeeID) as MinID, Max(EmployeeID) as MaxID from Employees"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
While objDR.Read()
intMinID=objDR("MinID")
MinRecNo=intMinID
MaxRecNo=objDR("MaxID")
End While
objDR.Close
doPopulation
lblID.text=intMinID
lblMin.text=MinRecNo
lblMax.text=MaxRecNo
end if
response.Write ("MaxRecNo = " & lblMax.text & "<br>MinRecNo = " & lblMin.text)
End Sub
Sub doPopulation()
Dim strConn as string = "server=server=YourServer;uid=YourUID;pwd=YourPWD;database=NorthWind"
Dim MySQL as string = "Select EmployeeID, LastName, FirstName, Title, HireDate, Address, City, " & _
"Extension from Employees Where EmployeeID =" & intMinID
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
While objDR.Read()
sLast=objDR("LastName")
sFirst=objDR("FirstName")
sTitle=objDR("Title")
sHireDate=objDR("HireDate")
sAddress=objDR("Address")
sCity=objDR("City")
sExtension=objDR("Extension")
End While
frmLastName.text=sLast
FrmFirstName.text=sFirst
frmTitle.text=sTitle
frmHireDate.text=sTitle
frmAddress.text=sAddress
frmCity.text=sCity
frmExtension.text=sExtension
End Sub
Sub doNext(Source as Object, E as EventArgs)
if cInt(lblID.text)< cInt(lblMax.text) then
intMinID=Cint(lblID.text) + 1
lblWarning.text=""
else
intMinID=lblID.text
lblWarning.text= ("<font Color=""#FF0000""><i>You''ve Reached the End of the Records</i></font>")
End If
DoPopulation
lblID.text=intMinID
End Sub
Sub doPrev(Source as Object, E as EventArgs)
if cInt(lblID.text)>cInt(lblMin.text)then
intMinID=Cint(lblID.text) - 1
lblWarning.text=""
else
intMinID=lblID.text
lblWarning.Text= ("<font Color=""#FF0000""><i>You''ve Reached the Beginning of the Records</i></font>")
End If
DoPopulation
lblID.text=intMinID
End Sub
Sub doFirst(Source as Object, E as EventArgs)
intMinID=cInt(lblMin.text)
DoPopulation
lblWarning.Text= ("<font Color=""#FF0000""><i>You''ve Reached the Beginning of the Records</i></font>")
lblID.text=intMinID
End Sub
Sub doLast(Source as Object, E as EventArgs)
intMinID=cInt(lblMax.text)
DoPopulation
lblWarning.Text= ("<font Color=""#FF0000""><i>You''ve Reached the End of the Records</i></font>")
lblID.text=intMinID
End Sub
</script>
<meta name="GENERATOR" Content="ASP Express 2.2">
<title>Next - Previous Records in a DataBase</title>
</head>
<body>
<form runat="server">
<table>
<tr>
<td align="right">Last Name</td>
<td> <asp:textbox id="frmLastName" runat="server" /></td>
</tr>
<tr>
<td align="right">First Name</td>
<td> <asp:textbox id="frmFirstName" runat="server" /></td>
</tr>
<tr>
<td align="right">Title</td>
<td> <asp:textbox id="frmTitle" runat="server" /></td>
</tr>
<tr>
<td align="right">Hire Date</td>
<td> <asp:textbox id="frmHireDate" runat="server" /></td>
</tr>
<tr>
<td align="right">Address</td>
<td> <asp:textbox id="frmAddress" runat="server" /></td>
</tr>
<tr>
<td align="right">City</td>
<td> <asp:textbox id="frmCity" runat="server" /></td>
</tr>
<tr>
<td align="right">Extension</td>
<td> <asp:textbox id="frmExtension" runat="server" /></td>
</tr>
<tr>
<td align="right"><b>Current Record #: </b>
<asp:Label ID="lblID" runat="server" />
</td>
<td>
<asp:Button id="btnPrev" Text="<< Previous" onclick="doPrev" runat="server" />
<asp:Button id="btnNext" Text="Next >>" onclick="doNext" runat="server" />
</td>
</tr>
<tr>
<td align="Left" valign="Top">
<asp:Label ID="lblMin" visible="false" runat="server" />
<asp:Label ID="lblMax" visible="false" runat="server" />
</td>
<td align="Left" valign="Top">
<asp:Button id="lblFirst" Text="First" onclick="doFirst" runat="server" />
<asp:Button id="lblLast" Text="Last" onclick="doLast" runat="server" /><br>
<asp:Label ID="lblWarning" runat="server" /><br>
</td>
</tr>
</table>
</form>
</body>
</html>
