Return -1 on ADO Recordset

[shadowskull]

Hi All,

 

I've been having this problem for over a day now, and I can't pinpoint where the problem lies. Here is the code:

 

Private Function WatchWord(ByVal sTranscript As String) As String

Dim sWord, sUWord As String

Dim sFind As String

Dim ifind, iIndex, iPrior As Integer

'

Dim rscase As ADODB.Recordset

Dim connxn As ADODB.Connection

Dim ConnxnStr As String

ConnxnStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=EDMS"

'

sExec = "Select word from Words"

'

Set connxn = New ADODB.Connection

connxn.Open ConnxnStr

Set rscase = New ADODB.Recordset

rscase.Open sExec, connxn

MsgBox (CStr(rscase.RecordCount)) 'I get a -1

'

While Not rscase.EOF 'For iIndex = 0 To rscase.RecordCount - 1

sWord = rscase.Fields(0).Value

sTranscript = UCase(sTranscript)

sUWord = LTrim(RTrim(UCase(sWord)))

ifind = InStr(1, sTranscript, sUWord, vbTextCompare)

If ifind > 0 Then

iPrior = iPrior + 1

If sFind = "" Then

sFind = LTrim(RTrim(sWord))

Else

sFind = sFind + ", " + LTrim(RTrim(sWord))

End If

End If

rscase.MoveNext

'Next

Loop

'

rscase.Close

connxn.Close

Set rscase = Nothing

Set connxn = Nothing

'

If iPrior >= 2 Then

sPriority = "High"

ElseIf iPrior = 1 Then

sPriority = "Medium"

Else

sPriority = "Low"

End If

'

WatchWord = sFind

End Function

 

I've used almost the exact same code in several other functions that have the recordset insert, update and retrieve, and they all work fine. This one decides it doesn't want to work. That messagebox that gets the rowcount returns a -1, and there are 16 rows in the table. What could be wrong?

 

Thanks in advance!

 

:confused:

[wyrd]

This looks like VB6 code. Did you copy/paste this from VB6?

 

Maybe this will help;

 

http://msdn.microsoft.com/library/default.asp?url=/code/list/ado.asp?frame=true

[Nerseus]

This is ADO code, not ADO.NET. First, you're in the wrong forums.

 

Second (answer), in ADO you can't guarantee the RecordCount will be accurate until you've gone to the end of the RecordSet by using MoveNext to the end, or MoveLast. You may be able to read the RecordCount in some cases (it sounds like it works sometimes), but definitely DO NOT count on that. You should use the .EOF property like you are - it's safer.

 

If you really need the record count, you'll have to first check BOF or EOF, then loop through using MoveNext til the end, then check the RecordCount. Don't forget to go back to the beginning before trying to use your Recordset.

 

-Nerseus