Re: ADO Recordcount = -1
From: Stephen Howe (stephenPOINThoweATtns-globalPOINTcom)
Date: 01/25/05
- Previous message: DS: "Re: ADO Recordcount = -1"
- In reply to: DS: "ADO Recordcount = -1"
- Next in thread: DS: "Re: ADO Recordcount = -1"
- Reply: DS: "Re: ADO Recordcount = -1"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 Jan 2005 19:12:25 -0000
"DS" <dsweatman@bellsouth.net> wrote in message
news:1106676303.986530.236150@f14g2000cwb.googlegroups.com...
> The following code is in a asp page. I can't get the recordcount to
> return the correct information. The recordset is populated because I
> can loop through it. How do I get the recordcount to work correctly?
> Any help would be greatly appreciated!
RecordCount being -1 is the number 1 question here.
And if you check the MS documentation, it depends on CursorType
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp
RecordCount is always -1 if the CursorType is ForwardOnly and can be -1 if
Dynamic.
In other cases it is the right number.
Lets look at your code
> Dim madoRs,madoCmd,msConn,msSQL
>
> Set madoRs = Server.CreateObject("ADODB.Recordset")
>
> Set madoCmd = Server.CreateObject("ADODB.Command")
>
> msConn = "Provider=SQLOLEDB;Data Source=XXXXXDEV;Initial
> Catalog=TestDB;UID=xxx;PWD=xxx"
>
> msSQL = "select lMSDSId from tblMSDS"
>
> With madoRs
> .MaxRecords = MAX_ROWS
> .LockType = adLockReadOnly
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> End With
These properties you have setup will be ignored.
Instead madoRs inherits the properties of the returned RecordSet from
madoCmd.
And the returned RecordSet's CursorLocation will be whatever is the default
CursorLocation inherited from the the Connection used by Command object.
On the Connection object that is adUseServer by default unless changed. So
you will _ALWAYS_ get a returned recordset that is
ServerSided, ForwardOnly, ReadOnly
or
ClientSided, Static, ReadOnly
if you do Cmd.Execute
If you want to specify the type of RecordSet other than the defaults other
than above you need to not use .Execute. You have to use a 2nd method.
In your case, the reason it is likely to be failing is because the
Connection object's CursorLocation is adUseServer which the Cmd object uses.
So you are getting a returned Recordset that is ServerSided, ForwardOnly,
ReadOnly and ForwardOnly cursors always have a RecordCount of -1
Instead you do
With madoCmd
.ActiveConnection = msConn
.CommandType = adCmdText
.CommandText = msSQL
.CommandTimeout = 60
End With
With madoRs
.MaxRecords = MAX_ROWS
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.CursorType = adOpenStatic
REM .ActiveConnection THIS MUST NOT BE SET, it gets the connection from the
Cmd object
.Open madoCmd ' Do RecordSet.Open() with source being the Cmd object
End With
> 'empty recordset
> If err.number = 0 Then
> If madoRs.RecordCount = 0 Then
> msMsg = "There are no MSDSs matching your search criteria."
> End If
> End If
Examining RecordCount is not a robust method for determining no records
(because it can be -1).
A better method which works for all cursor types (and you now you can use
Server-sided ForwardOnly cursors, the fastest cursor, as you are no longer
dependent on RecordCount being populated) is
' There can't be any records if BOF and EOF are both true
If madoRs.BOF and madoRs.EOF Then
msMsg = "There are no MSDSs matching your search criteria."
End If
Cheers
Stephen Howe
- Previous message: DS: "Re: ADO Recordcount = -1"
- In reply to: DS: "ADO Recordcount = -1"
- Next in thread: DS: "Re: ADO Recordcount = -1"
- Reply: DS: "Re: ADO Recordcount = -1"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
