RecordCount is -1...my question in ???? area.

[week]

Private Sub DupIDCheck()
On Error GoTo ErrorDupIDCheckGeneric

Dim lcSQLString As String

' ADO variables
Dim lcnnDupIDCheck As ADODB.Connection
Dim lcmdDupIDCheck As ADODB.Command
Dim lrsDupIDCheck As ADODB.Recordset


' Other variables
Dim lcQueryText As String
Dim llngDupIDCount As Long

' Initialize variables
Set lcnnDupIDCheck = New ADODB.Connection
Set lcmdDupIDCheck = New ADODB.Command


' Use the SAM to get the read-only claims string
lcSQLString = SAMInterface("ClaimsMonitor", "SQLRaw&quot

If lcSQLString <> &quot;&quot; Then
' This means a good connection string was returned here

' Connect to SQL
lcnnDupIDCheck.ConnectionString = lcSQLString
On Error GoTo ErrorDupIDCheckOpenConnect
lcnnDupIDCheck.Open
On Error GoTo ErrorDupIDCheckGeneric

' Execute the queries
Set lcmdDupIDCheck.ActiveConnection = lcnnDupIDCheck
lcQueryText = SAMInterface(&quot;ClaimsMonitor&quot;, &quot;CCAPBRRSQueryText&quot

If lcQueryText <> &quot;&quot; Then
' This means the query text was populated with something
lcmdDupIDCheck.CommandText = lcQueryText
On Error GoTo ErrorDupIDCheckExecute
Set lrsDupIDCheck = lcmdDupIDCheck.Execute
On Error GoTo ErrorDupIDCheckGeneric
' Examine the results


???? The CCAPBRRSQueryText is query looks like this:
SELECT GIS_ID FROM CCAPBRRS GROUP BY GIS_ID HAVING COUNT(GIS_ID) > 1
Why lrsDupIDCheck.RecordCount is always -1 even when there is a dupid? What am I doing wrong? There must be something missing here.... Thanks for your help.


If lrsDupIDCheck.RecordCount > 0 Then
AlertInterface -6001, &quot;There are duplicate REG_ID on CCAPBRRS table. &quot;
End If
Else
' The SQL query text was empty
Log &quot;The CCAPBRRS query text was empty&quot;
End If


lcQueryText = SAMInterface(&quot;ClaimsMonitor&quot;, &quot;CCAPBRDIQueryText&quot
If lcQueryText <> &quot;&quot; Then
' This means the query text was populated with something
lcmdDupIDCheck.CommandText = lcQueryText
On Error GoTo ErrorDupIDCheckExecute
Set lrsDupIDCheck = lcmdDupIDCheck.Execute
On Error GoTo ErrorDupIDCheckGeneric
' Examine the results
If lrsDupIDCheck.RecordCount >= 0 Then
AlertInterface -6001, &quot;There are duplicate REG_ID on CCAPBRDI table. &quot;
End If
Else
' The SQL query text was empty
Log &quot;The CCAPBRDI query text was empty&quot;
End If

 

[chadt]

clientside cursor will not give you an accurate recordcount. try serverside.

 

[week]

How? :=(

 

[dvannoy]

rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic

you can change these to whatever you like.. I use client side and have no problems getting a record count..

it may have something to do with your cursor type..

 

[chadt]

my bad....I had clientside and serverside reversed in my solution.

 

[VBOldTimer]

A client side cursor can give you an accurate record count if you just use the MoveLast (and then move back to the first record with MoveFirst if desired) prior to checking the count.