Solved

classic asp recordset

Posted on 2011-09-02
4
437 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
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 18

Expert Comment

by:mgfranz
ID: 36477874
0
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how can i check if the text is an Isogram ? 2 27
PHP Form Calculate Total Price 10 43
How to show span when clicked on? 10 24
replace link with jQuery 4 15
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 article discusses the difference between strict equality operator and equality operator in JavaScript. The Need: Because JavaScript performs an implicit type conversion when performing comparisons, we have to take this into account when wri…
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…

832 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