April 25, 2003 at 6:55 am
I would like to populate an Access.adp form with an ADODB.Recordset fetched from a SP and pass input parameters to the SP during the VBA Code call to the SP. I cannot find how to do this. Could someone point me to code examples?? Thank You for your time!
April 25, 2003 at 7:08 am
First create a AdoCommandObject for the stored procedure.
Create the parameters for SP.
Set the values to parameters
Use adoCommandObject.Execute method to get the recordset.
April 25, 2003 at 7:20 am
I have created a sample for you.
Assuming there is a SP in database test_sp which takes an input parameter @p_id and returns a recrodset.
-------------------------------------------
'Create ado command object
Set usrCmd = New ADODB.Command
With usrCmd
.CommandType = adCmdStoredProc
.CommandText = "dbo.test_sp"
Call .Parameters.Append(.CreateParameter("@p_id", adInteger, adParamInput))
Set .ActiveConnection = usrCn
.Parameters("@p_id").Value = 1
Set usrRs = .Execute
End With
-------------------------------------------
Hope this helps
April 25, 2003 at 9:38 am
The below code seems to run fine...no errors
but how do i get the data?? I can't seem to populate my form with the recordset returned by the SP.
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "procPopulateCurrentSectionsFrm_1"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@SelectedWtrLatLon", adChar, adParamInput, 12)
cmd.Parameters.Append prm
prm.Value = GlobalWtrLatLon
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "procPopulateCurrentSectionsFrm_1"
.Open cmd, Options:=adCmdStoredProc
End With
/*NOW THIS CODE BELOW GIVES ERROR AND I CANNOT GET TO THE RECORDSET*/
With Forms!frmLatestSectionsForCurrentSectioningDisplay
Set .Recordset = rst
End With
April 25, 2003 at 10:08 am
Can you please give the error details displayed.
Thanks
April 25, 2003 at 10:38 am
Here is the error I get:
"The object you entered is not a valid recordset Property"
Run Time 7965
Thank You for your help!
April 25, 2003 at 11:31 am
quote:
Here is the error I get:"The object you entered is not a valid recordset Property"
Run Time 7965
It's been a while, but you could try:
With Forms!frmLatestSectionsForCurrentSectioningDisplay
Set .Recordset = rst.RecordsetClone
End With
The RecordsetClone property should return a pointer to the recordset object.
April 25, 2003 at 1:30 pm
Additional insight into my posting: I have been working and researching this ALL DAY!
I now get an error stating the recordset is "Closed"...I can't do a thing with a closed recordset. I have a different SP that runs fine and sends back two output parameters with no problem...So connection is fine. I can also Debug the SP in Query Analyzer, provide the input parameter and I get 22 records in the display grid (correct results). But still the coding I provided above to will not work. Help Anyone???
April 25, 2003 at 1:34 pm
ADO is returning the text showing recordcount return as a recordset. Put SET NOCOUNT ON in your proc.
April 25, 2003 at 2:00 pm
jpipes,
Thank You! As you can tell I'm new to this stuff. I reviewed many examples today and not one pointed out the SET NOCOUNT ON issue (or I missed it). The form was populated... It Worked fine with SET NOCOUNT ON! Again, thanks for your experience and thoughtfullness in responding to my ?
RussB
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply