Page displaying Multiple instance of the same record [Archive]

jerry

12-18-2002, 02:33 PM

Please help me ! can any one tell me while my page is displaying Multiple rows of the same record.
while I only have one single record(row) for each customer in the MSSQL table?

Here is the code:

<center> <h2> Member Listing</h2> </center>

<%
'Option Explicit
' Dimension Local variables
Dim objConn
Dim objRS
Dim strDSN
Dim strSQL
Dim intTotalColumns
Dim intCounter
Dim Page_Size
Dim Current_Page
Dim Page_Count

MyConn.open "DSN=xxxxx;uid=yyyyy;pwd=yyyyyyyyfff"

Page_Size = 7 'this is where you set the # of records displayed per page

If Request("Page") = "" Then
Current_Page = 1
Else
Current_Page = CInt(Request("Page"))
End If

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open strDSN

objRS.CursorLocation = adUseClient
objRS.PageSize = Page_Size

strSQL = "Select Prefix, Uid, FirstName,LastName, Address, Membercity, MemberApt,MemberZipCode,PhoneNumber, EmailAddress from majortble order by LastName"
objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText

Page_Count = objRS.PageCount

If 1 > Current_Page Then Current_Page = 1
If Current_Page > Page_Count Then Current_Page = Page_Count

objRS.AbsolutePage = Current_Page

Response.write ("<table BORDER = 1 > <TR><TH>Title</TH><TH> First Name</TH><TH>Last Name</TH><TH> Member Address</TH><TH> City</TH><TH>ZipCode</TH><TH> Phone Number</TH><TH> Email Address</TH></TR> ")

Do While objRS.AbsolutePage = Current_Page And Not objRS.EOF

Response.write ("<TR>")

Response.write ("<TD>" & objRS("Prefix") & "</td>")
Response.write ("<TD>" & objRS("Firstname") & "</td>")
Response.write ("<TD>" & objRS("LastName") & "</td>")
Response.write ("<TD>" & objRS("Address") & "</td>" )
Response.write ("<TD>" & objRS("MemberCity") & "</td>" )
Response.write ("<TD>" & objRS("MemberZipCode") & "</td>")
Response.write ("<TD>" & objRS("PhoneNumber") & " </td> " )
Response.write ("<TD>" & objRS("EmailAddress") & "</td> ")
Response.write ("</TR>")

objRS.MoveNext
Loop
Response.write ("</table>")

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

If Current_Page = 1 Then
Response.Write " <center>" & "First | "
End If
If Current_Page >= 2 Then
Response.Write "<a href=""Memberlisting.asp?Page=1"
Response.Write """><center><< First</a> | " & vbCrLf
End If
If Current_Page >= Page_Count Then
Response.Write "Next >> | "
End If
If Current_Page < Page_Count Then
Response.Write "<a href=""Memberlisting.asp?Page="
Response.Write Current_Page + 1
Response.Write """>Next ></a>" & " |" & vbCrLf
End IF
If Current_Page <> 1 Then
Response.Write "<a href=""Memberlisting.asp?Page="
Response.Write Current_Page - 1
Response.Write """>< Previous </a> | " & vbCrLf
Response.Write " " & vbCrLf
End If
If Current_Page = 1 Then
Response.Write "" & "< Previous | "
End If
If Current_Page <> Page_Count Then
Response.Write "<a href=""Memberlisting.asp?Page="
Response.Write Page_Count
Response.Write """>Last >></a>" & vbCrLf
End If
If Current_Page >= Page_Count Then
Response.Write "Last" & ""
End If
Response.Write "</center>" %>

<center>Page <%=Current_Page%> of <%=Page_Count%></center>

raf

12-19-2002, 08:41 AM

Hmm. Let me try.

You mention 'MSSQL' in your post as a table, but in your code, you use the table 'majortble'.

What is MSSQL-table ? Is it really a table with the customers is, or are you refering to a MySQL database ?

If it's the table with the user, and you only want to display one record per user from the majortble-table, thenyou need to use an outer join to join these tables and to display one record per user from the MSSQL table.

Canyou tell us some more about this table and what your exactly try to do (do you need a DISTINCT or GROUP BY query or absolutely not, and stuff like that)

jerry

12-19-2002, 03:11 PM

I have solved this problem the Mssql serevr 2000 had a job the fires up and repopulate each night. As the result of this I end with the problem.

Thanks everyone.