?
Solved

classic asp recordset

Posted on 2011-09-02
4
Medium Priority
?
446 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Webboy2008
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Rajesh Dalmia
ID: 36477575
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
 
LVL 11

Expert Comment

by:Rajesh Dalmia
ID: 36477576
note: I have only changed the If part... rest code will remain as it is....
0
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 2000 total points
ID: 36478119
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question