472,096 Members | 1,214 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,096 software developers and data experts.

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(" = ")
Response.Write(x.value & "")

set rs=Nothing
set conn = nothing

Thanks in advance for all your help!
Sep 16 '06 #1
2 1658
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
I hope this code works.

Thanks & Regards,
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!

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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.