Pass form variable to stored procedure and return record set

[greggranger]

I am having trouble pasing a variable from a form to a stored procedure, running the stored procedeure and returning the result set to the screen.

I am able to pass the variable from the form to a vb variable, but cannot figure out how to call the stored procedure using the variable??

The code I have to set the variable is:

Dim ccfid1 As String
TxtID.SetFocus
ccfid1 = TxtID.Text

I would prefer to run the stored procedure with the ccfid1 value as the variable and return the results on screen. The name of my stored procedure is "spLSRptVerification".

I know this can't be too complicated and it's driving me crazy not being able to figure it out. Any help is appreciated.

 

[greggranger]

I have come up with the following code, but am receiving the error: "Run Time error '3705': Operation is not allowed when the object is open."

Dim ccfid1 As String
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim adoCommand As New ADODB.Command
Dim adoPar As New ADODB.Parameter

'Set ccfid1 variable to form object
cmbAsOfDate.SetFocus
ccfid1 = cmbAsOfDate.Text



adoConnection.Open _
CurrentProject.Connection.ConnectionString
Set adoCommand.ActiveConnection = adoConnection
adoCommand.CommandType = adCmdStoredProc
adoCommand.CommandText = "dbo.spLSrptVerification"

'Update parameter value with ccfid1 value
Set adoPar = adoCommand.CreateParameter _
("@REPORTDATE", adVarChar, adParamInput, 50, ccfid1)
adoCommand.Parameters.Append adoPar
Set adoRecordset = adoCommand.Execute
'Error occurs on this line
adoRecordset.Open adoCommand

Since I am new to VB, I am having trouble determining what I have done. If anyone could please offer any help, I'd greatley appreciate it.

 

[danvlas]

The easiet way is:

Set YourRecordset = CurrentProject.Connection.Execute("SP_Name(" & Me.ControlName & ")")

That would do. However, if the current user does not have the permission to execute the sp, the error message will be ambiguous: Syntax error...

So, another approach:

Dim oConn as New ADODB.Connection
Dim oCmd as New ADODB.Command
Dim oRst As New ADODB.Recordset

Set oConn = CurrentProject.Connection
With oCmd
   .ActiveConnection = oConn
   .CommandText = "SP_Name"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("@ParamName",adInteger,adParamInput, , Me.ControlName)
   Set oRst = .Execute
End With
Set oCmd = Nothing

'do what you want with the recordset

Set oRst = Nothing
Set oConn = Nothing

In this way, the error message will be the appropriate one (if there is a need for it)

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[greggranger]

I first updated my code to include the reccomended line:

Set YourRecordset = CurrentProject.Connection.Execute("dbo.spLSrptVerification(" & [Forms]![frmLightspeed]![cmbString] & ")")

But I received a syntax error on the line. I'm confident I have the proper permissons to run the SP. After receiving the error I altered the code to:

Set YourRecordset = CurrentProject.Connection.Execute("dbo.spLSrptVerification adoPar")


I receive no error on the "Set" line but when I try to open the record set I receive the error, "Run Time error '3705': Operation is not allowed when the object is open."

Entire Code:

Dim ccfid1 As String
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim adoCommand As New ADODB.Command
Dim adoPar As New ADODB.Parameter

'Set ccfid1 variable to form object
cmbAsOfDate.SetFocus
ccfid1 = cmbAsOfDate.Text



adoConnection.Open _
CurrentProject.Connection.ConnectionString
Set adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "dbo.spLSrptVerification"
adoCommand.CommandType = adCmdStoredProc

'Update parameter value with ccfid1 value
Set adoPar = adoCommand.CreateParameter _
("@REPORTDATE", adVarChar, adParamInput, 50, ccfid1)
adoCommand.Parameters.Append adoPar

Set adoRecordset = CurrentProject.Connection.Execute("dbo.spLSrptVerification adoPar")


'Error occurs here
adoRecordset.Open

When executing the sp, how can I return the reults to screen?

While waiting for your response, I'll try the example code you posted. Thanks for your help

 

[danvlas]

Set adoRecordset = CurrentProject.Connection.Execute("dbo.spLSrptVerification adoPar")
This has opened the recordset, so...
adoRecordset.Open WILL return that error.


Create an UNBOUND continuous form, make as many controls as you need, BIND the controls them to the fields that are to be returned from the stored procedure and, in the appropriate event, write:

Set Me.Recordset=CurrentProject.Connection.Execute("spLSrptVerification(" & [Forms]![frmLightspeed]![cmbString] & ")")

This will requery the form and repopulate it on the basis of the current value of the parameter.

I just hope you have Access 2000 or higher...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cmmrfrds]

I think you are close with the original.

adoCommand.Parameters.Append adoPar
Set adoRecordset = adoCommand.Execute '' DELETE this
'Error occurs on this line
adoRecordset.Open adoCommand,,3,3

The recordset open method will accept a command object, I have only done this a couple of times so the syntax may be a little off. I added cursor type and lock type to the open so that you have an updatable recordset. There are other ways to do this but see if this works.

 

[greggranger]

I deleted the reccomended line and although an error does not appear the results of the stored procedure are not displayed.

This is my current code:

Dim ccfid1 As String
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim adoCommand As New ADODB.Command
Dim adoPar As New ADODB.Parameter

'Set ccfid1 variable to form object
cmbAsOfDate.SetFocus
ccfid1 = cmbAsOfDate.Text



adoConnection.Open _
CurrentProject.Connection.ConnectionString
Set adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "dbo.spLSrptVerification"
adoCommand.CommandType = adCmdStoredProc

'Update parameter value with ccfid1 value
Set adoPar = adoCommand.CreateParameter _
("@REPORTDATE", adVarChar, adParamInput, 50, ccfid1)
adoCommand.Parameters.Append adoPar


adoRecordset.Open adoCommand, , 3, 3

Thnaks for your help -- I am going to try creating an UNBOUND form to display the results.

 

[cmmrfrds]

Set adoPar = adoCommand.CreateParameter _
("@REPORTDATE", adVarChar, adParamInput, 50, ccfid1)
adoCommand.Parameters.Append adoPar
Make sure the parm is what you expect.
Instead of on the create or in addition to do.
adoPar.Value = ccfid1
Debug.Print "parm value = " adoPar.Value

adoRecordset.Open adoCommand, , 3, 3

Check the contents of the returned data.
Debug.Print "first field returned = " adoRecordset(0)

Paste in the first part of your SP showing the definition of @REPORTDATE.