469,626 Members | 1,025 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

SQL statement only returns 1 row instead of 60+

I have an access db with 60+ records in a table, I am trying to display the records on a page. At this point, I am able to connect to the db, but I can only get the first record within the recordset.

I've tried everything I can think of, so I'm hopeing someone with a fresh mind can review and see what I'm doing wrong.

[code begins here]
<%
Dim conn, rs, DBPath

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../db/login.mdb") & ";"
Set rs = Server.CreateObject("ADODB.RecordSet")

'Set SQL
rsStr = "SELECT title, fname, lname From userinfo"

rs.open rsStr, conn

'This is to display the results from the SQL String
Response.Write "There are " & rs.recordcount & " rows that were returned"

for each x in rs.Fields
Response.Write(" ")
Response.Write(x.name)
Response.Write(" = ")
Response.Write(x.value & "")
next

rs.close
set rs=Nothing
conn.close
set conn = nothing
%>
[/code]

Thanks in advance for all your help!
Sep 16 '06 #1
2 1610
Hi ,

Let the number of rows returned is 60.

Response.Write "There are " & rs.recordcount & " rows that were returned"
Is the above statement returning correct result,in this case 60?
If it returns correct result, then the problem is with for loop.

Instead of using for loop, u just try these below statements,
Expand|Select|Wrap|Line Numbers
  1. If not rs.eof then
  2.  do until rs.eof
  3.   response.write(rs.fields(0) & "," & rs.fields(1)) 'suppose two columns are there
  4.  rs.movenext
  5.  loop
  6. end if
  7.  
I hope this code works.

Thanks & Regards,
Jaya.S
mail to: jayadevi.s@tcs.com
Sep 18 '06 #2
Hi Jaya!

To answer your question, the
Response.Write "There are " & rs.recordcount & " rows that were returned"
is only reporting that 1 record is in the record set. But the sample loop that you provided is displaying all 60+ records! Horray!

Thanks again for your help!

Michelle
Sep 19 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Rachel Weeden | last post: by
10 posts views Thread by ale.of.ginger | last post: by
35 posts views Thread by Thierry Loiseau | last post: by
3 posts views Thread by Assembly file locked | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.