Passing Parameters to MSAccess stored procedures 2

[perplexd]

I have used the format below to successfully call single records from an MSAccess database. However if I attempt use a query which returns more than one record then rs.recordcount = -1 and there appear to be no records in the rs. What am I doing wrong?

Dim rs As ADODB.Recordset, strParameter As String, strQuery As String
Set rs = New Recordset
Dim cm As ADODB.Command
Dim p As ADODB.Parameter
Set cm = New Command
Set cm.ActiveConnection = cn

strQuery = "qryYourQuery"

With cm
.CommandText = strQuery
.CommandType = adCmdStoredProc
End With

Set p = New Parameter

strParameter = Var1
Set p = cm.CreateParameter("pVar1", adString, adParamInput, 10, CDate(strParameter))
cm.Parameters.Append p

Set rs = new Recordset
Set rs = cm.Execute

 

[sunaj]

Hi,

The default cursortype of a recordset if forwardonly. This type of recordset does not support .recordcount. Use cursortype 'keyset' or 'static' - or even better: avoid using .recordcount
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jantie78]

I agree, it is better not to use .RecordCount. If you really want to use it, it's best to do a rs.MoveLast before the rs.RecordCount. Like this you make sure you have the exact number of records.

Another way to count the records is executing a query that give the number of records as result.

cm.CommandText = "SELECT COUNT(1) AS myCount FROM ... WHERE ..."
Set rs = cm.Execute
Msgbox rs("myCount&quot 'This gicves the number of records.
rs.Close

Greetz,
Jan
If this response was usefull to you, please mark it with a Star!

 

[perplexd]

I don't think that is the only problem. It seems that the recordset itself is blank as when I try to populate the MSFlexGrid with the data nothing happens. It is simply skipping over the loop which should populate the data, implying that the recordcount is zero. Below is my full code as it stands:
Sub Search()
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim prm As ADODB.Parameter

Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic

Set cmd = New Command
Set cmd.ActiveConnection = con

If optSerialNo.Value = True Then
GoTo SerialNoSearch
ElseIf optDate.Value = True Then
If VerifyDate = True Then
GoTo DateSearch
Else
Exit Sub
End If
End If

SerialNoSearch:
lblSearchedFor.Caption = "Search: Serial Number = " & txtSerialNo.Text
With cmd
.CommandText = "qryDateSearch"
.CommandType = adCmdStoredProc
End With


Set prm = cmd.CreateParameter("prmSerialNo", adInteger, adParamInput, , Val(txtSerialNo.Text))
cmd.Parameters.Append prm

Set rs = cmd.Execute

GoTo Display

DateSearch:
lblSearchedFor.Caption = "Search: Date = " & txtDateDay.Text & "/" & txtDateMonth & "/" & txtDateYear
With cmd
.CommandText = "qrySNSearch"
.CommandType = adCmdStoredProc
End With

Set prm = cmd.CreateParameter("prmDate", adDate, adParamInput, , CDate(txtDateDay.Text & "/" & txtDateMonth & "/" & txtDateYear))
cmd.Parameters.Append prm

Set rs = cmd.Execute

'Display the data
Display:
Dim i As Integer


grdData.Visible = False

On Error GoTo Error

'Display number of records returned
lblNoRecords.Caption = "Number of Records = " & rs.RecordCount

'Load data into table============================================
With grdData
.SelectionMode = flexSelectionFree

'Set number of rows (records)
.Rows = rs.RecordCount + 1

For i = 1 To (rs.RecordCount)
'Select table row (record)
.Row = i
.Col = 0
.Text = i

'Fill fields of row (record)
.Col = 1
.Text = rs!ProdID
.Col = 2
.Text = rs!SerialNo
.Col = 3
.Text = rs!ProdDesc

'Display "File Code" or "Part No"
.Col = 4
If rs!ProdType = "True" Then
.Text = "File Code"
Else
.Text = "Part No"
End If

'Format date to British
.Col = 5
.Text = Format(rs!DateGen, "dd-mmmm-yyyy&quot

'Insert comment if present
If Not IsNull(rs!Comment) Then
.Col = 6
.Text = rs!Comment
End If

rs.MoveNext
Next i
End With

Call ResizeGrid(grdData)

rs.Close
Set rs = Nothing

grdData.Visible = True

'Set form ready for next search
txtSerialNo.Text = ""
txtDateDay.Text = ""
txtDateMonth.Text = ""
txtDateYear.Text = ""

End Sub

Any insight is much appreciated!

 

[sunaj]

Yes it skips the loop because you loop from 1 to -1.
Try changeing the cursor type like I suggested above:

rs.CursorType = adOpenKeyset


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CajunCenturion]

to follow up to sunaj's suggestion to avoid the .recordcount property altogether - which I completely agree with -- as not all providers will support that property, regardless of the cursor type.

Rather than using a for loop to traverse the recordset, use a while loop based on the .EOF property, incrementing i within the loop.

I have also written a WriteCell Function that I have found usefull when working with FlexGrids, and with this function and the while loop, your routine would look something like this

i = 0
rs.MoveFirst
Do While (rs.EOF = False)
i = i + 1
WriteCell i, 0, Trim(i)
WriteCell i, 1, rs!ProdID
WriteCell i, 2, rs!SerialNo
WriteCell i, 3, rs!ProdDesc
If (rs!ProdType = "True) Then
WriteCell i, 4, "File Code"
Else
WriteCell i, 4, "Part No"
End If
WriteCell i, 5, Format(rs!DateGen, "dd-mmmm-yyyy&quot
If Not IsNull(rs!Comment) Then
WriteCell i, 6, rs!Comment
End If
rs.MoveNext
Loop

and the funciton

Private Sub WriteCell(vRow as Integer, vCol as Integer, vText as String)

With grdGrid
.Row = vRow
.Col = vCol
.Text = vText
End With

End Sub

I like this approach because it allows me to add additional parameters, such as Font and/or Alignment Settings as parameters to the subroutine withougt having to set each property for each assgnment
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[perplexd]

There must be something else wrong because using rs.CursorType = adOpenKeyset doesn't work either...ie there is still a recordcount of -1.

 

[sunaj]

Follow CajunCenturion & my suggestion: drop the .recordcount and use a do or while loop. You are not using the recordcount anyway. Recordcount is not very efficient as you implicit moves all the way to the end of the recordset to get the count. forwardonly recordsets are much faster.
A typical open recordset and loop could look like:

Rst.open
if not rst.eof and not rst.bof then
rst.movefirst
while not rst.eof
'code here...
rst.movenext
wend
else
'empty recordset
end if
rst.close
set rst = nothing

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[perplexd]

Thanks guys...you were right...it was returning a recordset, but the .recordcount was not working. Using a do loop now...another simple but frustrating problem solved!!