SQL statement only returns 1 row instead of 60+

[usfshelly]

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!

[jayadevi]

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,

If not rs.eof then
 do until rs.eof
  response.write(rs.fields(0) & "," & rs.fields(1)) 'suppose two columns are there
 rs.movenext
 loop
end if
 

I hope this code works.

Thanks & Regards,
Jaya.S
mail to: [e-mail]

[usfshelly]

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