classic asp recordset

Function DestinationList(Destination,DbConnection)

SQL =" SELECT "
SQL = SQL & " f.AddressID, f.Address AS frAddress, f.City AS frCity, f.Zip AS frZip, "
SQL = SQL & " d.Address AS toAddress, d.City AS toCity, d.Zip AS toZip, "
SQL = SQL & " g.VanpoolID, g.MilesToWork, g.PickupAddressID, "
SQL = SQL & " p.EmployerName, p.VanpoolID AS Expr1, "
SQL = SQL & " v.VanNumber "
SQL = SQL & " FROM Vanpool AS p INNER JOIN "
SQL = SQL & " Passenger AS g ON p.VanpoolID = g.VanpoolID INNER JOIN "
SQL = SQL & " Van AS v ON p.VanID = v.VanID INNER JOIN "
SQL = SQL & " Address AS d ON p.DestinationID = d.AddressID INNER JOIN "
SQL = SQL & " Address AS f ON g.PickupAddressID = f.AddressID " 
SQL = SQL & " WHERE (d.City ='" & Destination & "') "
SQL = SQL & " ORDER BY f.AddressID, v.VanNumber " 
 
Set RsDestinationList = Server.CreateObject("ADODB.recordset")
RsDestinationList.open SQL, DbConnection, 3,3
If Not RsDestinationList.Eof Then
    p="<table class=""DestinationListMasterStyle""  cellpadding=""3"" cellspacing=""3"">"
    p=p & "<tr>"
    p=p & "<td><strong>From Address</strong></td>"
    p=p & "<td><strong>From City</strong></td>"
    p=p & "</tr>"
    While Not RsDestinationList.Eof
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    ToAddress = Trim(RsDestinationList("toAddress")) 
    ToCity = Trim(RsDestinationList("toCity")) 
    ToZip = Trim(RsDestinationList("toZip"))  
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    FrZip = Trim(RsDestinationList("frZip"))  
    MilesToWork = Trim(RsDestinationList("MilesToWork"))  
    PickupAddressID = Trim(RsDestinationList("PickupAddressID"))  
    VanpoolID = Trim(RsDestinationList("VanpoolID"))  
    VanNumber = Trim(RsDestinationList("VanNumber"))         
    p=p & "<tr>"
    p=p & "<td>" & FromAddress & "</td>"
    p=p & "<td>" & FromCity & "</td>"
    p=p & "</tr>"
    RsDestinationList.MoveNext
    Wend
    p=p & "</table>"
 
else
    p=p & "<table><tr>"
    p=p & "<td>" & GlobalSearchDestinationNullMsg  & "</td>"
    p=p & "</tr></table>"
End If
RsDestinationList.close
Set RsDestinationList = Nothing
Set DbConnection = Nothing




DestinationList=p

End Function

Open in new window

Function DestinationList(Destination,DbConnection)

SQL =" SELECT "
SQL = SQL & " f.AddressID, f.Address AS frAddress, f.City AS frCity, f.Zip AS frZip, "
SQL = SQL & " d.Address AS toAddress, d.City AS toCity, d.Zip AS toZip, "
SQL = SQL & " g.VanpoolID, g.MilesToWork, g.PickupAddressID, "
SQL = SQL & " p.EmployerName, p.VanpoolID AS Expr1, "
SQL = SQL & " v.VanNumber "
SQL = SQL & " FROM Vanpool AS p INNER JOIN "
SQL = SQL & " Passenger AS g ON p.VanpoolID = g.VanpoolID INNER JOIN "
SQL = SQL & " Van AS v ON p.VanID = v.VanID INNER JOIN "
SQL = SQL & " Address AS d ON p.DestinationID = d.AddressID INNER JOIN "
SQL = SQL & " Address AS f ON g.PickupAddressID = f.AddressID " 
SQL = SQL & " WHERE (d.City ='" & Destination & "') "
SQL = SQL & " ORDER BY f.AddressID, v.VanNumber " 
 
Set RsDestinationList = Server.CreateObject("ADODB.recordset")
RsDestinationList.open SQL, DbConnection, 3,3
If Not RsDestinationList.Eof Then
    p="<table class=""DestinationListMasterStyle""  cellpadding=""3"" cellspacing=""3"">"
    p=p & "<tr>"
    p=p & "<td><strong>From Address</strong></td>"
    p=p & "<td><strong>From City</strong></td>"
    p=p & "</tr>"
    While Not RsDestinationList.Eof
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    ToAddress = Trim(RsDestinationList("toAddress")) 
    ToCity = Trim(RsDestinationList("toCity")) 
    ToZip = Trim(RsDestinationList("toZip"))  
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    FrZip = Trim(RsDestinationList("frZip"))  
    MilesToWork = Trim(RsDestinationList("MilesToWork"))  
    PickupAddressID = Trim(RsDestinationList("PickupAddressID"))  
    VanpoolID = Trim(RsDestinationList("VanpoolID"))  
    VanNumber = Trim(RsDestinationList("VanNumber"))         
    p=p & "<tr>"
    p=p & "<td>" & FromAddress & "</td>"
    p=p & "<td>" & FromCity & "</td>"
    p=p & "</tr>"
    RsDestinationList.MoveNext
    Wend
    p=p & "</table>"
 
else
    p=p & "<table><tr>"
    p=p & "<td>" & GlobalSearchDestinationNullMsg  & "</td>"
    p=p & "</tr></table>"
End If
RsDestinationList.close
Set RsDestinationList = Nothing
Set DbConnection = Nothing




DestinationList=p

End Function

Open in new window


Hi. attached classic asp code is working but would like to add two thing.

1. Paging: at the bottom, would like to see something like << 1,2,3,4,5 >>
2. alternative color row: like 1st row is silver, 2nd row is white, 3rd now is silver...

Thanks
Webboy2008Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajesh DalmiaCommented:
for alternet row color you can try below code....


note: replace #Silver and #White with the color # values that you want to show....
p="<table class=""DestinationListMasterStyle""  cellpadding=""3"" cellspacing=""3"">"
    p=p & "<tr>"
    p=p & "<td><strong>From Address</strong></td>"
    p=p & "<td><strong>From City</strong></td>"
    p=p & "</tr>"
    RecCount = 0
    While Not RsDestinationList.Eof
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    ToAddress = Trim(RsDestinationList("toAddress")) 
    ToCity = Trim(RsDestinationList("toCity")) 
    ToZip = Trim(RsDestinationList("toZip"))  
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    FrZip = Trim(RsDestinationList("frZip"))  
    MilesToWork = Trim(RsDestinationList("MilesToWork"))  
    PickupAddressID = Trim(RsDestinationList("PickupAddressID"))  
    VanpoolID = Trim(RsDestinationList("VanpoolID"))  
    VanNumber = Trim(RsDestinationList("VanNumber"))         
    If RecCount Mod 2 = 0 THEN
       p=p & "<tr bgcolor='#Silver'>"
    Else
       p=p & "<tr bgcolor='#White'>"
    End If
    p=p & "<td>" & FromAddress & "</td>"
    p=p & "<td>" & FromCity & "</td>"
    p=p & "</tr>"
    RecCount = RecCount + 1
    RsDestinationList.MoveNext
    Wend
    p=p & "</table>"

Open in new window

0
Rajesh DalmiaCommented:
note: I have only changed the If part... rest code will remain as it is....
0
sammySeltzerCommented:
You can try this:

<!--#include file=paging.asp-->
<%
Function DestinationList(Destination,DbConnection)

SQL =" SELECT "
SQL = SQL & " f.AddressID, f.Address AS frAddress, f.City AS frCity, f.Zip AS frZip, "
SQL = SQL & " d.Address AS toAddress, d.City AS toCity, d.Zip AS toZip, "
SQL = SQL & " g.VanpoolID, g.MilesToWork, g.PickupAddressID, "
SQL = SQL & " p.EmployerName, p.VanpoolID AS Expr1, "
SQL = SQL & " v.VanNumber "
SQL = SQL & " FROM Vanpool AS p INNER JOIN "
SQL = SQL & " Passenger AS g ON p.VanpoolID = g.VanpoolID INNER JOIN "
SQL = SQL & " Van AS v ON p.VanID = v.VanID INNER JOIN "
SQL = SQL & " Address AS d ON p.DestinationID = d.AddressID INNER JOIN "
SQL = SQL & " Address AS f ON g.PickupAddressID = f.AddressID " 
SQL = SQL & " WHERE (d.City ='" & Destination & "') "
SQL = SQL & " ORDER BY f.AddressID, v.VanNumber " 
 
Set RsDestinationList = Server.CreateObject("ADODB.recordset")
RsDestinationList.open SQL, DbConnection, 3,3

if RsDestinationList.EOF =true then 
     Response.Write "No  Records Found" 
else 
    dim intPageSize,PageIndex,TotalPages 
    dim RecordCount,RecordNumber,Count intpageSize=5 
PageIndex=request("PageIndex")
 if PageIndex="" then 
      PageIndex=1 RecordCount=Rst.RecordCount   
RecordNumber=(intPageSize * PageIndex) - intPageSize 
Rst.PageSize =intPageSize 
Rst.AbsolutePage = PageIndex TotalPages=Rst.PageCount 
dim intPrev,intNext intPrev=PageIndex - 1 intNext=PageIndex +1 
RecCount=1 

If Not RsDestinationList.Eof Then
p="<table class=""DestinationListMasterStyle""  cellpadding=""3"" cellspacing=""3"">"
    p=p & "<tr>"
    p=p & "<td><strong>From Address</strong></td>"
    p=p & "<td><strong>From City</strong></td>"
    p=p & "</tr>"
    RecCount = 0
    While Not RsDestinationList.Eof RecCount<=intPageSize
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    ToAddress = Trim(RsDestinationList("toAddress")) 
    ToCity = Trim(RsDestinationList("toCity")) 
    ToZip = Trim(RsDestinationList("toZip"))  
    FromAddress = Trim(RsDestinationList("frAddress"))
    FromCity = Trim(RsDestinationList("frCity"))
    FrZip = Trim(RsDestinationList("frZip"))  
    MilesToWork = Trim(RsDestinationList("MilesToWork"))  
    PickupAddressID = Trim(RsDestinationList("PickupAddressID"))  
    VanpoolID = Trim(RsDestinationList("VanpoolID"))  
    VanNumber = Trim(RsDestinationList("VanNumber"))         
    If RecCount Mod 2 = 0 THEN
       p=p & "<tr bgcolor='#Silver'>"
    Else
       p=p & "<tr bgcolor='#White'>"
    End If
    p=p & "<td>" & FromAddress & "</td>"
    p=p & "<td>" & FromCity & "</td>"
    p=p & "</tr>"
    RecCount = RecCount + 1
    RsDestinationList.MoveNext
    Wend
    p=p & "</table>"
 
else
    p=p & "<table><tr>"
    p=p & "<td>" & GlobalSearchDestinationNullMsg  & "</td>"
    p=p & "</tr></table>"
   call button(intPrev,intNext,TotalPages)
End If
RsDestinationList.close
Set RsDestinationList = Nothing
Set DbConnection = Nothing


DestinationList=p

End Function


%>

Open in new window


Here is paging.asp

<%
Sub button(intPrev,intNext,TotalPages)
dim Counter
if intPrev <> 0 then
     response.write "<a href=ASP_Paging.asp?PageIndex=" & intPrev & ">Previous Page</a>"
end if
if Rst.AbsolutePage <> -3 then
   response.write "<a href=YourCallingPage.asp?PageIndex=" & intNext  & ">Next Page </a>"
end if
Counter=1
response.write "<br>Navigate to Page :"
While Counter <=TotalPages
   if cint(Counter)=cint(PageIndex) then
      response.write Counter
   else
      response.write "<a href=YourCallingPage.asp?PageIndex=" & Counter & ">" & Counter & "</a>"
   end if

if cint(Counter) <> cint(TotalPages) then
    response.write " | "
End if
Counter= Counter +1
wend
End Sub

Function Connect()
set Connect=server.createobject("ADODB.connection")
Connect.open "dbCOnnection"
End Function
%>

Not tested, sorry but let us know if any bugs
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.