recordcount will not work with the default cursor type you are using for the
recordset. However, instead of switching to a more expensive cursor, I have
two alternative suggestions. The one you choose depends on what you need to
use the data in the recordset for:
1. If all you need to do is determine if the user_ID/password (bad!
"password" is very likely to be a reserved word - I don't have time to look
it up right now to verify this, but I think you should rename this field)
exists, then simply do this:
sql = "Select count(*) from Users where User_ID = '" & username & _
"' AND password ='" & password & "';"
'for debugging:
response.write sql
....
rs.Open sql, adoCon,,,1
len = rs(0).value
rs.close
set rs=nothing
adoCon.close
set adoCon = nothing
response.write len
2.If you need to do something with the data in the recordset, then first of
all, you should explicitly name the fields that you want to return from the
table instead of using "select *" and then use getrows to stuff the data
from the recordset into an array, allowing you to immediately close your
recordset and connection (a good thing for scalability), and to use this
code to determine the number of records ... actually, I assume there's only
one record per user, so recordcount is not needed in this case either: all
you really need to do is determine if the query returned any records. You
can test the EOF property ro figure this out:
rs.Open sql, adoCon,,,1
if not rs.EOF then
ar = rs.getrows
end if
rs.close
set rs = nothing
adoCon.close
set adoCon = nothing
if isarray(ar) then
for iRow = 0 to ubound(ar,2)
for iCol = 0 to ubound(ar,1)
response.write ar(iCol, iRow) & "; "
next
response.write "<BR>"
next
else
response.write "No records were returned"
end if
HTH,
Bob Barrows
I'm having a bit of problem with a login script
<%
Dim sql
Dim len
Dim username, password
username = request.form("username")
password = request.form("pass")
sql = "Select * from Users where User_ID = '" & username & "' AND
password = '" & password & "';"
set adoCon = server.CreateObject ("adodb.connection")
adoCon.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
Server.MapPath("something.mdb") & ";Persist Security Info=False"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, adoCon
len = rs.recordcount
response.write len
%>
This outputs -1 regardless if I enter the correct username\password
or not. Wondering if my sql sentence is correct, not sure about the "
and ' is correct. Both User_ID and password are text fields in the
database