asked on

RecordCount showing -1

     strSQL = "SELECT * FROM tbl_Registration where username = '" & Trim(strUserName) & "'" & " and passwd ='" & Trim(strPassWord) & "'"
      'response.write strSQL
      'Response.End
      Set Query = objConnection.Execute(strSQL)

      response.write Query.RecordCount
      response.end

with the code above, the RecordCount returns a -1 but I know if I paste the select into Query analyzer, it brings back one record

ASKER CERTIFIED SOLUTION

hongjun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

hongjun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hongjun

Reference site
http://www.adopenstatic.com/faq/recordcounterror.asp

hongjun

SOLUTION

hongjun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hongjun

Just simple change the sql statement and variable names accordingly.

dba123

ASKER

I open my recordset like this, which requires less codeing:

Set Query = objConnection.Execute(strSQL)

I don't believe I have done it this way for about 2 years and don't understand why people still use this syntax:

objRs.Open "select * from your_table", objConn, adOpenStatic, adLockReadOnly, adCmdText

with that, what syntax can I use....how can I add a different cursor type to the way I am opening my recordset? I don't want to use the recordset.Open method, I like the recordset = objectconnection.Execute method

hongjun

>> I don't want to use the recordset.Open method, I like the recordset = objectconnection.Execute method
If you use Set rs = Conn.Execute (...), then you are using a firehost recordset. Then you got to use this to get the recordcount.

strSQL = "SELECT count(*) as cnt FROM tbl_Registration where username = '" & Trim(strUserName) & "'" & " and passwd ='" & Trim(strPassWord) & "'"
'response.write strSQL
'Response.End
Set Query = objConnection.Execute(strSQL)

response.write Query("cnt")
response.end

hongjun

dba123

ASKER

strSQL = "SELECT count(*) as cnt, username, passwd FROM tbl_Registration where username = '" & Trim(strUserName) & "'" & " and passwd ='" & Trim(strPassWord) & "'"

Column 'tbl_Registration.Username' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause

hongjun

sorry try this

strSQL = "SELECT count(*) as cnt FROM tbl_Registration where username = '" & Trim(strUserName) & "'" & " and passwd ='" & Trim(strPassWord) & "'"

hongjun

dba123

ASKER

I need to pull those other 2 fields....username and password in the query

hongjun

then you can try this

Dim cnt

strSQL = "SELECT count(*) as cnt FROM tbl_Registration where username = '" & Trim(strUserName) & "'" & " and passwd ='" & Trim(strPassWord) & "'"
Set Query = objConnection.Execute(strSQL)
cnt = Query("cnt")

strSQL = "SELECT username, passwd FROM tbl_Registration where username = '" & Trim(strUserName) & "'" & " and passwd ='" & Trim(strPassWord) & "'"
Set Query = objConnection.Execute(strSQL)

Now, you can use your Query recordset as per normal.

dba123

ASKER

yea, that is inefficient. I want it all in one query. why should I code 2 separate queries which just creates more calls to the server?

gladxml

if this is regarding login maybe you can try this approach... assuming that you recordobject is rs and your connection is oConn then

'check the user name in the db
sqlrsLogin = "select username, password From tblUsers Where userName = '"& Trim(strUserName) &"'
rs.Open sql, oConn, 0, 2
if not rs.EOF then
'compare the password in the db and the password enetered by the user..
if UCase(rs("password ")) = UCase(Trim(strPassWord)) then

response.write "password verified.."
'retrieve the values

else

response.write "Please check hter is some problem..."

end if
end if

HTH...

HAppy programming....

dba123

ASKER

I just want to get rid of that error....I know I need to group by, but by what? I hate that error

dba123

ASKER

got it

strSQL = "SELECT count(*) as cnt, username, passwd FROM tbl_Registration where username = '" & Trim(strUserName) & "'" & " and passwd ='" & Trim(strPassWord) & "'" & "group by username, passwd"