Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RecordCount showing -1

Posted on 2004-09-26
15
Medium Priority
?
927 Views
Last Modified: 2012-06-27
     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
0
Comment
Question by:dba123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 33

Accepted Solution

by:
hongjun earned 2000 total points
ID: 12157488
to get RecordCount is to use a adOpenStatic, adOpenKeyset or adOpenDynamic CursorType. When you use <i>Set objRs = objConn.Execute("select * from your_table") ' firehose method</i>, it is using adOpenForwardOnly by default. That's why you get RecordCount -1. But using the firehose method will make execution faster. Use it if you do not need the RecordCount property.


Example
=======
<%@ Language=VBScript %>
<%
Option Explicit
Response.Buffer = true
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

<font face="Verdana">
Another method to get RecordCount is to use a adOpenStatic, adOpenKeyset or adOpenDynamic CursorType. When you use <i>Set objRs = objConn.Execute("select * from your_table") ' firehose method</i>, it is using adOpenForwardOnly by default. That's why you get RecordCount -1. But using the firehose method will make execution faster. Use it if you do not need the RecordCount property.
<br><br>

<%
Dim objConn, objRs
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" & server.MapPath("record_cnt.mdb") & ";uid=sa;pwd=;"

set objRs = Server.CreateObject("ADODB.Recordset")
objRs.Open "select * from your_table", objConn, adOpenStatic, adLockReadOnly, adCmdText

Response.Write "Record Count = <b>" & objRs.RecordCount & "</b>"

objRs.Close
objConn.Close
set objRs = nothing
set objConn = nothing
%>

</font>


</BODY>
</HTML>



hongjun
0
 
LVL 33

Assisted Solution

by:hongjun
hongjun earned 2000 total points
ID: 12157489
Another method to get RecordCount is to use a adUseClient CursorLocation. When you use <i>Set objRs = objConn.Execute("select * from your_table") ' firehose method</i>, it is using adUseServer by default. That's why you get RecordCount -1. But using the firehose method will make execution faster. Use it if you do not need the RecordCount property.

Using adUseClient
=================
<%
Dim objConn, objRs
Const adUseClient = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" & server.MapPath("record_cnt.mdb") & ";uid=sa;pwd=;"

set objRs = Server.CreateObject("ADODB.Recordset")
objRs.CursorLocation = adUseClient
objRs.Open "select * from your_table", objConn, , adLockReadOnly, adCmdText

Response.Write "Record Count = <b>" & objRs.RecordCount & "</b>"

objRs.Close
objConn.Close
set objRs = nothing
set objConn = nothing
%>



hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12157490
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

Assisted Solution

by:hongjun
hongjun earned 2000 total points
ID: 12157493
Another simple method is something like this

    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
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12157497
Just simple change the sql statement and variable names accordingly.
0
 
LVL 1

Author Comment

by:dba123
ID: 12157534
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
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12157539
>> 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
0
 
LVL 1

Author Comment

by:dba123
ID: 12157551
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
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12157560
sorry try this

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



hongjun
0
 
LVL 1

Author Comment

by:dba123
ID: 12157575
I need to pull those other 2 fields....username and password in the query
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12157596
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.
0
 
LVL 1

Author Comment

by:dba123
ID: 12157609
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?
0
 
LVL 15

Expert Comment

by:gladxml
ID: 12157612
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....
0
 
LVL 1

Author Comment

by:dba123
ID: 12157614
I just want to get rid of that error....I know I need to group by, but by what?  I hate that error
0
 
LVL 1

Author Comment

by:dba123
ID: 12157618
got it

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question