| Author |
 Topic  |
|
|
pokemon
Junior Member
151 Posts |
 Posted - 02 February 2004 : 12:34:43
|
How do i change a code below to display 5 newest members in my portal? Thanks
Dim Forum_Count
Dim NewMember_Name, NewMember_Id, Member_Count
Dim LastPostDate, LastPostLink
Forum_Count = intForumCount
'## Forum_SQL - Get newest membername and id from DB
strSql = "SELECT M_NAME, MEMBER_ID FROM " & strMemberTablePrefix & "MEMBERS " &_
" WHERE M_STATUS = 1 AND MEMBER_ID > 1 " &_
" ORDER BY MEMBER_ID desc;"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open TopSQL(strSql,1), my_Conn
if not rs.EOF then
NewMember_Name = chkString(rs("M_NAME"), "display")
NewMember_Id = rs("MEMBER_ID")
else
NewMember_Name = ""
end if
rs.close
set rs = nothing
if NewMember_Name <> "" then
Response.Write " <tr>" & vbNewline & _
" <td bgcolor=""" & strForumCellColor & """ colspan=""" & sGetColspan(6,5) &_
""">" & _
"<font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """ color=""" & strForumFontColor & """>welcome & _
"<span class=""spnMessageText"">" & profileLink(NewMember_Name,NewMember_Id) & "</span>.</font></td>" & vbNewline & _
" </tr>" & vbNewline
end if |
|
|
Cliff
Average Member
United States
501 Posts |
Posted - 02 February 2004 : 12:40:01
|
This should work in Access / MS SQL:
strSql = "SELECT Top 5 M_NAME, MEMBER_ID FROM " & strMemberTablePrefix & "MEMBERS " &_
" WHERE M_STATUS = 1 AND MEMBER_ID > 1 " &_
" ORDER BY MEMBER_ID desc;"
This should work in MySQL:
strSql = "SELECT M_NAME, MEMBER_ID FROM " & strMemberTablePrefix & "MEMBERS " &_
" WHERE M_STATUS = 1 AND MEMBER_ID > 1 " &_
" ORDER BY MEMBER_ID desc Limit 5;"
|
 |
|
|
pokemon
Junior Member
151 Posts |
Posted - 02 February 2004 : 12:49:29
|
| it not work with Top 5. I don't know why...It works without Top 5, but only show one member. Would you please verify? Thanks |
|
|
|
miperez
Junior Member
Spain
243 Posts |
Posted - 02 February 2004 : 12:55:04
|
Why don't you use the TopSQL function provided by Snitz itself? You can find it in inc_fuinc_common, and it performs the appropriate changes for each database.
It's declared as:
Function TopSQL(strSQL, lngRecords)
Edit: I just saw the code you posted above. So, in order to show 5 members, change the digit inside the TopSQL function from 1 to 5, like this:
rs.open TopSQL(strSql,5), my_Conn |
Best Regards
Mikel Perez
"Hell is the place where everything test perfectly, and nothing works"
|
Edited by - miperez on 02 February 2004 12:58:09 |
|
|
|
pokemon
Junior Member
151 Posts |
Posted - 02 February 2004 : 14:44:05
|
| i changed to 5 like this: rs.open TopSQL(strSql,5), my_Conn, but nothing will work. |
|
|
|
pokemon
Junior Member
151 Posts |
Posted - 02 February 2004 : 15:58:27
|
i changed TopSQL to 5 like this: but it still showing up only one member. Anyone know how to fix it.
'## Forum_SQL - Get newest membername and id from DB
strSql = "SELECT M_NAME, MEMBER_ID FROM " & strMemberTablePrefix & "MEMBERS " &_
" WHERE M_STATUS = 1 AND MEMBER_ID > 1 " &_
" ORDER BY MEMBER_ID desc;"
set rs = Server.CreateObject("ADODB.Recordset")
iTopNewNum = 5
rs.open TopSQL(strSql,iTopNewNum), my_Conn
if not rs.EOF then
NewMember_Name = chkString(rs("M_NAME"), "display")
NewMember_Id = rs("MEMBER_ID")
else
NewMember_Name = ""
end if
rs.close
set rs = nothing
if NewMember_Name <> "" then
Response.Write " <tr>" & vbNewline & _
" <td bgcolor=""" & strForumCellColor & """ colspan=""" & sGetColspan(6,5) &_
""">" & _
"<font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """ color=""" & strForumFontColor & """>welcome: " & _
"<span class=""spnMessageText"">" & profileLink(NewMember_Name,NewMember_Id) & "</span>.</font></td>" & vbNewline & _
" </tr>" & vbNewline
end if
|
|
|
|
miperez
Junior Member
Spain
243 Posts |
Posted - 03 February 2004 : 04:18:27
|
Hi, pokemon.
I have reviewed it, and is happening because of how the code is written. No matter how many records you retrieve from the database, the code afterwards just reads the first one, close the records, and print that single one.
I am not an expert in ASP, but I think that in order to change it, you could do the following: enclose the if.. end if statements inside a do...while loop, and close the recordset outside the loop:
do
if not rs.EOF
NewMember_Name = chkString(rs("M_NAME"), "display")
NewMember_Id = rs("MEMBER_ID")
rs.movenext
else
'NewMember_Name = ""
exit do
end if
if NewMember_Name <> "" then
Response.Write " <tr>" & vbNewline & _
" <td bgcolor=""" & strForumCellColor & """ colspan=""" & sGetColspan(6,5) &_
""">" & _
"<font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """ color=""" & strForumFontColor & """>welcome: " & _
"<span class=""spnMessageText"">" & profileLink(NewMember_Name,NewMember_Id) & "</span>.</font></td>" & vbNewline & _
" </tr>" & vbNewline
end if
while not rs.EOF
rs.close
set rs = nothing
Make a backup of your files before trying these changes, OK?
Tell me if this has worked...
|
Best Regards
Mikel Perez
"Hell is the place where everything test perfectly, and nothing works"
|
Edited by - miperez on 03 February 2004 04:29:09 |
|
|
|
pokemon
Junior Member
151 Posts |
Posted - 06 February 2004 : 16:49:28
|
| thanks, it works....you are the man |
|
|
|
miperez
Junior Member
Spain
243 Posts |
Posted - 09 February 2004 : 03:50:50
|
| Glad that helped, really. Cheers!! |
Best Regards
Mikel Perez
"Hell is the place where everything test perfectly, and nothing works"
|
|
|
| |
Topic |
|
Topic Locked
