Solved

classic asp recordset

Posted on 2011-09-02
4
429 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
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Rajesh Dalmia
Comment Utility
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
Comment Utility
note: I have only changed the If part... rest code will remain as it is....
0
 
LVL 18

Expert Comment

by:mgfranz
Comment Utility
0
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The task A number given should be formatted for easy reading by separating digits into triads. Format must be made inline via JavaScript, i.e., frameworks / functions are not welcome. So let’s take a number like this “12345678.91¿ and format i…
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/…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now