Return -1 on ADO Recordset

[shadowskull]

Hi All,

Ive been having this problem for over a day now, and I cant 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

Ive 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 doesnt 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!

 

[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, youre in the wrong forums.

Second (answer), in ADO you cant guarantee the RecordCount will be accurate until youve 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 - its safer.

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

-Nerseus