I'm having trouble with setting the cursor type with my ADO command objects. Here is my code, however everytime I try to use 'rs.MoveFirst' I get an error that the recordset position can not be reset.
---Begin Code---
Private Sub cboPart_Click()
On Error GoTo Err_cboPart_Click
Dim lngEndPartID As Long
Dim lngReturn As Long
Dim rs As New ADODB.Recordset
Dim strRS As String
'Get the PartID to the TopPart item.
lngEndPartID = cboPart.ItemData(cboPart.ListIndex)
'Get a recordset with all the items from that Part.
lngReturn = modRead.PartSubSelectByParent(rs, lngEndPartID)
Do While Not rs.EOF
strRS = strRS & rs("PartDescription"
& "(Dwg#: " & _
rs("PartDrawingNumber"
& " Rev: " & _
rs("PartCurrentRevision"
& "
[Part#: " & _
rs("PartID"
& "]" & vbNewLine
rs.MoveNext
Loop
MsgBox strRS
rs.MoveFirst
Add_flxParts rs
Set rs = Nothing
Exit_cboPart_Click:
Exit Sub
Err_cboPart_Click:
LogError Me.Name & ".cboPart_Click"
End Sub
---End Code Segment---
---Begin modRead Code---
Public Function PartSubSelectByParent(ByRef rs As ADODB.Recordset, lngParentID As Long) As Long
On Error GoTo Err_PartSubSelectByParent
'Use the global ADO command object.
Set g_objCmd = New ADODB.Command
'Setup the command object for execution.
Set g_objCmd.ActiveConnection = g_objConn
g_objCmd.CommandText = "procPartSubSelectByParent"
g_objCmd.CommandType = adCmdStoredProc
'Set the cursor type.
rs.CursorType = adOpenDynamic
'Append the parameters into the command object.
g_objCmd.Parameters.Append g_objCmd.CreateParameter("PartParentID", adInteger, _
adParamInput, , lngParentID)
'Now execute our command and return recordset result.
Set rs = g_objCmd.Execute
PartSubSelectByParent = lngParentID
Exit_PartSubSelectByParent:
Exit Function
Err_PartSubSelectByParent:
LogError "modRead.PartSubSelectByParent()"
End Function
---End modRead Code---
The only examples I have been able to find for using the adOpenDynamic cursor were using the Open method of the Recordset object itself. However I do not know how to pass parameters to the stored procedures using the Open method. Any help would be appreciated.
---Begin Code---
Private Sub cboPart_Click()
On Error GoTo Err_cboPart_Click
Dim lngEndPartID As Long
Dim lngReturn As Long
Dim rs As New ADODB.Recordset
Dim strRS As String
'Get the PartID to the TopPart item.
lngEndPartID = cboPart.ItemData(cboPart.ListIndex)
'Get a recordset with all the items from that Part.
lngReturn = modRead.PartSubSelectByParent(rs, lngEndPartID)
Do While Not rs.EOF
strRS = strRS & rs("PartDescription"
rs("PartDrawingNumber"
rs("PartCurrentRevision"
rs("PartID"
rs.MoveNext
Loop
MsgBox strRS
rs.MoveFirst
Add_flxParts rs
Set rs = Nothing
Exit_cboPart_Click:
Exit Sub
Err_cboPart_Click:
LogError Me.Name & ".cboPart_Click"
End Sub
---End Code Segment---
---Begin modRead Code---
Public Function PartSubSelectByParent(ByRef rs As ADODB.Recordset, lngParentID As Long) As Long
On Error GoTo Err_PartSubSelectByParent
'Use the global ADO command object.
Set g_objCmd = New ADODB.Command
'Setup the command object for execution.
Set g_objCmd.ActiveConnection = g_objConn
g_objCmd.CommandText = "procPartSubSelectByParent"
g_objCmd.CommandType = adCmdStoredProc
'Set the cursor type.
rs.CursorType = adOpenDynamic
'Append the parameters into the command object.
g_objCmd.Parameters.Append g_objCmd.CreateParameter("PartParentID", adInteger, _
adParamInput, , lngParentID)
'Now execute our command and return recordset result.
Set rs = g_objCmd.Execute
PartSubSelectByParent = lngParentID
Exit_PartSubSelectByParent:
Exit Function
Err_PartSubSelectByParent:
LogError "modRead.PartSubSelectByParent()"
End Function
---End modRead Code---
The only examples I have been able to find for using the adOpenDynamic cursor were using the Open method of the Recordset object itself. However I do not know how to pass parameters to the stored procedures using the Open method. Any help would be appreciated.