' Using VBScript/ASP to connect to an Access database
<%
Dim conn
Dim vcs
Set conn = Server.CreateObject("ADODB.Connection")
vcs= "Provider=Microsoft.Jet.OLEDB.4.0"
vcs= vcs & "; Data Source=" & Server.MapPath
("\Database\ExpressDataWeb.mdb")
conn.connectionstring = vcs
conn.open
%>
' to open the recordset and the data out
<%
Dim objrs1
Dim strSQL
Dim DateToday
DateToday=formatdatetime(DateAdd("d",-5,Date))
'Create the recordset object
Set objrs1 = Server.CreateObject("ADODB.Recordset")
'Set the SQL String
strSQL = "Select * from tblDispatch Where Account = '" & varAccount & "'"
strSQL = strSQL & " AND PickupDate >=" & "#" & DateToday & "#"
strSQL = strSQL & " AND TicketStatus <> '" & "Completed" & "'"
strSQL = strSQL & " AND TicketStatus <> '" & "Cancel" & "'"
strSQL = strSQL & " ORDER BY ControlNumber DESC"
'Response.Write strSQL
'Response.End
'Open the recordset getting all the data for this ticket
objrs1.Open strSQL, conn
%>
End of Recordset connection
Create HTML table to display data returned from recordset
<table border="0" align="center">
<tr>
<td class=tdtext><input type="button" name="Print" value="Print this Page"
onClick="print()" ;>
<input type=button value="Back" onClick="history.go(-1)";></td></tr>
<tr><td class=tdtext>Hint: Open Page Setup in browser and set to print
landscape or set in the printer popup dialog box</td></tr>
</table>
<Form Action="trackdeliveries.asp" method="post" name="frmShow">
<input type="hidden" name="Action" value="show">
<input type="hidden" name="lngTicketCode"
value=<%=Request.Form("cboAutoTicket")%>>
<table border="1" width="80%" align="center">
<caption>Tracking Information <br />
(Orders with a status of Completed/Cancel will not
show. )</font></h3></caption>
' Return the Account name for the user
<%
Response.Write "<strong>" & "Account for: "
Response.Write varAccount & "</strong>"
%>
' Heading for the table
<tr>
<th bgcolor="navy"><font>Control</font></th>
<th bgcolor="navy"><font>Automated Ticket</font></th>
<th bgcolor="navy"><font>Date</font></th>
<th bgcolor="navy"><font>Time</font></th>
<th bgcolor="navy"><font>Pickup Name</th>
<th bgcolor="navy"><font>Delivery Name</font></th>
<th bgcolor="navy"><font>Service</font></th>
<th bgcolor="navy"><font>Status</font></th>
<th bgcolor="navy"><font>Del Time</font></th>
<th bgcolor="navy"><font>POD</font></th>
</tr>
' While not End of the file write the data in table format
<% Do While Not objrs1.EOF %>
<tr>
<td align="center" class=tdtext><%=objrs1("ControlNumber")%></td>
<td align="center" class=tdtext>
<%=objrs1("AutomaticTicketCtr")%></td>
<td align="center" class=tdtext>
<%=objrs1("PickUpDate")%></td>
<td align="center" class=tdtext>
<%=objrs1("PickUpTime")%></td>
<td align="center" class=tdtext>
<%=objrs1("PickupName")%></td>
<td align="center" class=tdtext>
<%=objrs1("DeliverName")%></td>
<td align="center" class=tdtext>
<%=objrs1("PriceTable")%></td>
<td align="center" class=tdtext>
<%=objrs1("TicketStatus")%></td>
<td width="10%" align="center" class=tdtext>
<%=objrs1("DeliverTime")%></td>
<td width="10%" align="center" class=tdtext>
<%=objrs1("POD")%></td>
</tr>
' At end of the first record move to the next record
<% objrs1.MoveNext
Loop
%>
</table>
</form>
<%
' Close and dereference the database objects (extremely important)
objrs1.close
Set objrs1 = nothing
conn.close
Set conn = nothing
%>
Hope this helps.
