Re: RecordCount error

Hi Richard,

That works! Great. Thanks :)
--
pmud


"Richard Mueller" wrote:

Hi,

Set cursorType to adOpenStatic and see if that helps. RecordCount returns -1
if the cursorType does not support it.

rsPatient.cursorType = adOpenStatic

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"pmud" <pmud@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5F58CD3A-E48C-4520-82A7-40D536F97DE2@xxxxxxxxxxxxxxxx
Hi Richard,

Thats what my the problem is that the RecordCount is always showing a
value
of -1 even though there are more records returned. I read somewheer on
Google
that its a known issue in VB that sometimes RecordCount will not retun
the
correct value. It was mentioned to use the code below to get around this
error, so i used the code below:
rsRecCount = 0
rsPatient.MoveLast --- ERROR IS SHOWN TO BE IN THIS LINE

rsRecCount = rsPatient.RecordCount
If rsRecCount = 0 Then
MsgBox ("This patient has not been yet added to the database")
End IF

Thanks
--
pmud


"Richard Mueller" wrote:

Hi,

Since you are not enumerating the recordset, there is no need to move the
cursor backwards. The code should work if you delete the statement:

rsPatient.MoveLast

If you need to move the cursor back and forth, then you need to set the
cursorType property to allow this.

rsPatient.CursorType = adOpenStatic

When you retrieve the RecordCount property, the cursor is moved to the
end.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"pmud" <pmud@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F2022707-A1BC-43C9-85D1-C10C75C9566F@xxxxxxxxxxxxxxxx
Hi,

I have the following function. I need to use RecordCount of the
RecordSet
that is returned when the query is run, but I get the error : "Rowset
does
not support fetching backward. "


Private Function CheckInDatabase(dteBirthDate As Date, arrPatientName()
As
String)

Dim rsRecCount As Integer ' For counting records in a record set
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=UID;Password=PWD;Initial Catalog=DB;Data Source=IP"
cn.Open

MSHFlexGrid1.Clear

Dim rsPatient As ADODB.Recordset
Set rsPatient = New ADODB.Recordset

rsPatient.Open "Select patient_first,patient_last,patient_dob from
patient WHERE patient_dob ='" & dteBirthDate & "' AND ( (
patient_first
= '" & LTrim$(arrPatientName(0)) & "' AND patient_last = '" &
LTrim$(arrPatientName(1)) & "') or (patient_last = '" &
LTrim$(arrPatientName(0)) & "' and patient_first= '" &
LTrim$(arrPatientName(1)) & "'))", cn
Set MSHFlexGrid1.DataSource = rsPatient

rsRecCount = 0
rsPatient.MoveLast --- ERROR IS SHOWN TO BE IN THIS LINE

rsRecCount = rsPatient.RecordCount
If rsRecCount = 0 Then
MsgBox ("This patient has not been yet added to the patient
table
in
the database")
End If

cn.Close

End Function

--
pmud






.

Relevant Pages

  • Re: VB6 - ADODB - Strange behavior between IDE & EXE
    ... it mentions that RecordCount may return -1 for unsupported CursorType. ... However, this CursorType usage had been working for years, particularly with Access and Sybase ASE, on Windows 2000 and XP. ... It appears that the rs object itself is now reflecting some other object than that I set in the given LostFocus event. ... If iAns = vbYes Then ...
    (microsoft.public.vb.database.ado)
  • Re: rs.PageCount cannot function
    ... Before open, using stored procedure executed via dynamic sql, cursortype = 3 ... Before open, using stored procedure executed via Command object, cursortype ... So, even though it's giving us the "right" cursortype, the recordcount is ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: Embedded SQL within VBA?
    ... If you can't MoveLast, then the provider you are using probably defaults to using an "adOpenForwardOnly" cursortype (you can use the CursorType parameter of the Open method to try to specify the CursorType, but the provider may change the type if it doesn't support the one you asked for. ... If you have an adOpenForwardONly cursor type, try changing it to any of the others - if you need to be able to update the recordset, use adOpenDynamic or adOpenKeyset, otherwise you may be able to use adOpenStatic. ... figure out why .RecordCount is returning -1 here? ...
    (microsoft.public.word.vba.general)
  • Re: RecordCount error
    ... Set cursorType to adOpenStatic and see if that helps. ... RecordCount returns -1 ... Microsoft MVP Scripting and ADSI ... rsRecCount = rsPatient.RecordCount ...
    (microsoft.public.vb.database.ado)
  • Re: RecordCount error
    ... Thats what my the problem is that the RecordCount is always showing a value ... rsRecCount = rsPatient.RecordCount ... If you need to move the cursor back and forth, then you need to set the ... Dim rsPatient As ADODB.Recordset ...
    (microsoft.public.vb.database.ado)
Loading