* Whats Wrong with this code ???

[Debeast]

any ideas why this returns no records when there are plenty to return

<%


'----open connection----------
Session.timeout = 5
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
DSNtemp = &quot;DRIVER={MySQL};SERVER=demweb;UID=root;DATABASE=OCDEM&quot;
conn.open DSNTemp
Set Session(&quot;MyDB_conn&quot = conn
'-----------------------------



response.write &quot;Connection is &quot; & conn.state
response.write &quot;<br>&quot;

strSQLQuery = &quot;SELECT * FROM bleeps&quot;



Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open strSQLQuery, conn, 3, 3


response.write &quot;There are &quot; & rs.recordcount & &quot; records&quot;
response.write &quot;<br>&quot;

if not rs.recordcount < 1 then

do until rs.eof
response.write(rs(&quot;FieldName&quot & &quot;<br>&quot
rs.movenext
loop

else
response.write &quot;No Records Found&quot;

end if

'-------close connection-----
conn.close
set conn = Nothing

'-----------------------------

%>

 

[Targol]

To check where exactly your error comes from did you try to execute another query on the same connection to see if the same error occurs ? Water is not bad as soon as it stays out human body ;-)

 

[Debeast]

yep same error occurs get no records returned
but i know connection is physically(ie obdc etc) ok cos my php code works

 

[megalene]

I have been told there are problems with the recordcount feature in the RS object and it sometimes returns a 0 which would make your if statment not true.
try this around your loop:

if not rs.eof then
do while not rs.eof
response.write rs(&quot;myfield&quot & &quot;<br>&quot;
rs.movenext
loop
else
response.write &quot;no records found.&quot;
end if Megalene
If you crap it will stink!

 

[Debeast]

Thats exactly what it was WELL DONE and thank you
bloody asp thanks again

 

[Targol]

Yes !
Megalene is right, I don't know if your problem comes from here but I had this type of errors : the

recordCount

method only works (in some cases that I can't define) after you've used the

moveLast

method. Water is not bad as soon as it stays out human body ;-)

 

[Debeast]

 

[TedLiu]

Set conn.cusorlocation to client then you can use recordcount, otherwise always return -1.

Ted

 

[Debeast]

conn.cursorlocation = 3 work perfectly TY v v v much

 

[Simpleton]

The reason for this is that the ADO driver library because of the original object type does not bring back all of the rows, and will therefore set recordcount to -1 (It knows there are records just not how many)

you can read these anyway if your not bothered about volume using a simple

if rs.recordcount <> 0 then rs.movefirst
while not rs.eof
' Insert rs retrieval statements in here
rs.movenext
wend

Or set the correct recordset type and the rows will be prefetched before the rs.open is completed.