showing more than one set of data from a database.. help! [Archive]

startbar

09-18-2004, 01:58 PM

hi, this has always been puzzling for me and i cant find an answer!

<%@ Language=VBScript %>



<%

dim objConn, strCon, objRS, strSQL
Set objConn = Server.CreateObject("ADODB.Connection")

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb"

objConn.connectionstring = strCon
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
ON ERROR RESUME NEXT

Dim pageView, strNewsText

pageView=Request.Querystring("view")
%>

<% dim ordervariable, sortorder
ordervariable = "Category" 'default column to sort on
sortorder = "ASC" 'default sortorder

if (len(request.querystring("sortby")) >= 1) then
ordervariable = request.querystring("sortby")
end if
if (len(request.querystring("sortorder")) >= 1) then
sortorder = request.querystring("sortorder")
end if %>

<%if pageView="" THEN

strSQL="SELECT * FROM NETWORK WHERE Status=1 ORDER BY " & ordervariable & " " & sortorder

objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
'objRS.Filter=objRS("Status")=1
%>

THEN THE HTML PAGE IS HERE:

<table cellpadding="0" cellspacing="0" width="98%" height="0" bgcolor="#4b4b4b" align="center" style="border: 0px solid #767676;">
<tr>
<td width="100%" valign="top" align="left" style="background-color: #5F5F5F; padding: 4px; margin: 0px auto; border: 1px solid #767676;">
<font class="summary"><B>Latest Companies</B><BR><BR>

<%
' Write out the list of articles but it will
' only show the ones that have a status of
' 1 which shows they are online
DO WHILE NOT objRS.EOF%>

<%=objRS("Name")%> - <a class="nav" href="#">view company information</a><BR>
<%
objRS.MoveNext
Loop
objRS.Close
%>

</td>
</tr>
</table>

<%ELSE%>
No Categories Found
<%END IF%>
<%END IF%>

but HOW do i show 2 different sets of data on different parts of the page.. ie.

strSQL="SELECT * FROM NETWORK WHERE Status=1 ORDER BY " & ordervariable & " " & sortorder

and

strSQL="SELECT * FROM SOMETHING ELSE WHERE Status=1 ORDER BY " & ordervariable & " " & sortorder

show data..

please help - examples would be good! i need to be able to have more than one set of data from the database at one time.. thanks

BuddhaMan

09-19-2004, 02:29 AM

Create two different SQL strings and use two different recordsets using different variable names. Here's a sample sub I wrote which makes one query to get pieces of text to use in the second query. You would do something similar except display both recordsets on one webpage.

Sub AllOutput()

' Moved the EOMpercent lookup function outside the loops so it only gets looked up once
DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.Mappath(DataPath) & ";"
EOMgoal = EOMpercent()
Set rsExcel = Server.CreateObject("ADODB.Recordset")
Set rsTRB = Server.CreateObject("ADODB.Recordset")

Response.Write("<table cellspacing=""0"">")
' Inserts a squadron selector into the SQL statement and nothing if "All" is selected so the total for the whole OG is computed
' Also, displays the Sq or OG logo and accompanying table header text depending which option is selected
Response.Write("<table cellspacing=""0"">")
Select Case strSQ
Case "960 AACS"
SQsql = " AND Squadron='960 AACS'"
Response.Write ("<tr><td><img src=""./graphics/960aacs.jpg"" alt=""960 AACS Squadron patch""><td><div class=""trbtotalheader"">    RAP Totals</div><div class=""label-date"">                      Current as of: " & FormatDateTime(LastMod, vbShortDate) & "</div><div class=""label-date"">                      EOM goal = " & EOMPercent() & "% remaining</div></td></tr>")
Case "963 AACS"
SQsql = " AND Squadron='963 AACS'"
Response.Write ("<tr><td><img src=""./graphics/963aacs.jpg"" alt=""963 AACS Squadron patch""></td><td><div class=""trbtotalheader"">    RAP Totals</div><div class=""label-date"">                      Current as of: " & FormatDateTime(LastMod, vbShortDate) & "</div><div class=""label-date"">                      EOM goal = " & EOMPercent() & "% remaining</div></td></tr>")
Case "964 AACS"
SQsql = " AND Squadron='964 AACS'"
Response.Write ("<tr><td><img src=""./graphics/964aacs.jpg"" alt=""964 AACS Squadron patch""></td><td><div class=""trbtotalheader"">    RAP Totals</div><div class=""label-date"">                      Current as of: " & FormatDateTime(LastMod, vbShortDate) & "</div><div class=""label-date"">                      EOM goal = " & EOMPercent() & "% remaining</div></td></tr>")
Case "965 AACS"
SQsql = " AND Squadron='965 AACS'"
Response.Write ("<tr><td><img src=""./graphics/965aacs.jpg"" alt=""965 AACS Squadron patch""></td><td><div class=""trbtotalheader"">    RAP Totals</div><div class=""label-date"">                      Current as of: " & FormatDateTime(LastMod, vbShortDate) & "</div><div class=""label-date"">                      EOM goal = " & EOMPercent() & "% remaining</div></td></tr>")
Case "966 AACS"
SQsql = " AND Squadron='966 AACS'"
Response.Write ("<tr><td><img src=""./graphics/966aacs.jpg"" alt=""966 AACS Squadron patch""></td><td><div class=""trbtotalheader"">    RAP Totals</div><div class=""label-date"">                      Current as of: " & FormatDateTime(LastMod, vbShortDate) & "</div><div class=""label-date"">                      EOM goal = " & EOMPercent() & "% remaining</div></td></tr>")
Case "552 OG"
SQsql = ""
Response.Write ("<tr><td><img src=""./graphics/552og.jpg"" alt=""552 Ops Group patch""></td><td><div class=""trbtotalheader"">    RAP Totals</div><div class=""label-date"">                      Current as of: " & FormatDateTime(LastMod, vbShortDate) & "</div><div class=""label-date"">                      EOM goal = " & EOMPercent() & "% remaining</div></td></tr>")
End Select
Response.Write("</table>")
Response.Write("<br />")

Response.Write("<table cellspacing=""0"">")
Response.Write ("<tr><td colspan=""2"" class=""tableheader-black"">&nbsp</td><td align=""center"" colspan=""3"" class=""tableheader"" scope=""col"">Assigned</td><td></td><td align=""center"" colspan=""3"" class=""tableheader"" scope=""col"">Attached</td><td></td><td align=""center"" colspan=""3"" class=""tableheader"" scope=""col"">Total</td></tr>")
Response.Write ("<tr><td class=""tableheaderwrap-left"" scope=""col""><b>Position</b></td><td class=""tableheaderwrap-right"" scope=""col""><b>Event</b></td><td class=""tableheaderwrap"" scope=""col""><b>Rem</b></td><td class=""tableheaderwrap"" scope=""col""><b>Total</b></td><td class=""tableheaderwrap-both-moreright"" scope=""col""><b>% Rem</b></td><td class=""tableheaderwrap""></td><td class=""tableheaderwrap-both"" scope=""col""><b>Rem</b></td><td class=""tableheaderwrap"" scope=""col""><b>Total</b></td><td class=""tableheaderwrap-both-moreright"" scope=""col""><b>% Rem</b></td>" _
& "<td class=""tableheaderwrap""></td><td class=""tableheaderwrap-both"" scope=""col""><b>Rem</b></td><td class=""tableheaderwrap"" scope=""col""><b>Total</b></td><td class=""tableheaderwrap-both-moreright"" scope=""col""><b>% Rem</b></td></tr>") & vbCrLf

strSQLExcel = "SELECT * FROM excel;"
rsExcel.Open strSQLExcel, DB_CONNECTIONSTRING

Do While not rsExcel.EOF
' Zeroize total counters
TotalLineRequired = 0
TotalAttachedRequired = 0
TotalLineRemaining = 0
TotalAttachedRemaining = 0
TotalRequired = 0
TotalRemaining = 0

strSQLTRB = "SELECT * FROM personnel_data INNER JOIN (fltdeck_data2 INNER JOIN (fltdeck_data1 INNER JOIN (msn_data INNER JOIN sim_data ON msn_data.SSAN=sim_data.SSAN) ON fltdeck_data1.SSAN=sim_data.SSAN) ON fltdeck_data2.SSAN=fltdeck_data1.SSAN) ON personnel_data.SSAN=fltdeck_data2.SSAN WHERE " & rsExcel("sql_command") & SQsql & " AND ([MR Status] LIKE 'CMR' OR [MR Status] LIKE 'NCMR' OR [MR Status] LIKE 'BMC' OR [MR Status] LIKE 'BMC') AND Rank NOT LIKE 'CIV' ORDER BY [Last Name];"

rsTRB.Open strSQLTRB, DB_CONNECTIONSTRING
Do While not rsTRB.EOF

If Not IsNull(rsTRB(rsExcel("requirement") & "-#Req")) Then
Select Case rsTRB("Att")
Case "L"
TotalLineRequired = TotalLineRequired + rsTRB(rsExcel("requirement") & "-#Req")
TotalRequired = TotalRequired + rsTRB(rsExcel("requirement") & "-#Req")
Case "S"
TotalLineRequired = TotalLineRequired + rsTRB(rsExcel("requirement") & "-#Req")
TotalRequired = TotalRequired + rsTRB(rsExcel("requirement") & "-#Req")
Case "A"
TotalAttachedRequired = TotalAttachedRequired + rsTRB(rsExcel("requirement") & "-#Req")
TotalRequired = TotalRequired + rsTRB(rsExcel("requirement") & "-#Req")
End Select
End If
If Not IsNull(rsTRB(rsExcel("requirement") & "-#Rem")) Then
Select Case rsTRB("Att")
Case "L"
TotalLineRemaining = TotalLineRemaining + rsTRB(rsExcel("requirement") & "-#Rem")
TotalRemaining = TotalRemaining + rsTRB(rsExcel("requirement") & "-#Rem")
Case "S"
TotalLineRemaining = TotalLineRemaining + rsTRB(rsExcel("requirement") & "-#Rem")
TotalRemaining = TotalRemaining + rsTRB(rsExcel("requirement") & "-#Rem")
Case "A"
TotalAttachedRemaining = TotalAttachedRemaining + rsTRB(rsExcel("requirement") & "-#Rem")
TotalRemaining = TotalRemaining + rsTRB(rsExcel("requirement") & "-#Rem")
End Select
End If

rsTRB.MoveNext
Loop

' Calculate percent complete only if both required and remaining are not zero
If TotalLineRequired = 0 AND TotalLineRemaining = 0 Then
TotalLinePercent = 0
Else
TotalLinePercent = Round(((TotalLineRemaining / TotalLineRequired) * 100), 0)
End If

If TotalAttachedRequired = 0 AND TotalAttachedRemaining = 0 Then
TotalAttachedPercent = 0
Else
TotalAttachedPercent = Round(((TotalAttachedRemaining / TotalAttachedRequired) * 100), 0)
End If

If TotalRequired = 0 AND TotalRemaining = 0 Then
TotalPercent = 0
Else
TotalPercent = Round(((TotalRemaining / TotalRequired) * 100), 0)
End If

' Highlight those #s over this month's percentage
If TotalLinePercent > EOMgoal Then
TLPhilite = "highlightsmall"
Else
TLPhilite = "nohighlightsmall"
End If
If TotalAttachedPercent > EOMgoal Then
TAPhilite = "highlightsmall"
Else
TAPhilite = "nohighlightsmall"
End If
If TotalPercent > EOMgoal Then
TPhilite = "highlightsmall"
Else
TPhilite = "nohighlightsmall"
End If

Response.Write ("<tr><td class=""tableheaderwrap-left"" scope=""row"">" & rsExcel("crewposition") & "</td><td class=""tableheaderwrap-right"">" & rsExcel("description") & "</td><td class=""tableheaderwrap"">" & TotalLineRemaining & "</td><td class=""tableheaderwrap"">" & TotalLineRequired & "</td><td class=""" & TLPhilite & """>" & TotalLinePercent & "%</td>" _
& "<td class=""tableheaderwrap""></td><td class=""tableheaderwrap-both"">" & TotalAttachedRemaining & "</td><td class=""tableheaderwrap"">" & TotalAttachedRequired & "</td></td><td class=""" & TAPhilite & """>" & TotalAttachedPercent & "%</td>" _
& "<td class=""tableheaderwrap""></td><td class=""tableheaderwrap-both"">" & TotalRemaining & "</td><td class=""tableheaderwrap"">" & TotalRequired & "</td></td><td class=""" & TPhilite & """>" & TotalPercent & "%</td></tr>") & vbCrLf

rsTRB.Close

rsExcel.MoveNext
Loop
rsExcel.Close

Set rsTRB = Nothing
Set rsExcel = Nothing
Set DB_CONNECTIONSTRING = Nothing

Response.Write("</table>")
End Sub