How to use stored procedure for access data project

I'm trying to open a stored procedure and assign a value to a label on a page in Access data project.  below is the code.  When I run the page I get the following msg:
"run-time error 91: Object variable or With block variable not set "
The stored procedure runs when in sql server management studio

Please assist.  Thanks.

Private Sub Form_Current()
On Error GoTo ProcError
Dim rst As Recordset
Dim cmd As ADODB.Command
Dim strStoredProcedure As String
Dim prmPersonID As ADODB.Parameter
Dim varPersonID As Variant
Dim valPersonID As Variant
Dim prmReturn As Variant

valPersonID = Nz(Me.PersonID, 0)

strStoredProcedure = "stp_APDB_PersonStatusByPersonID"
Set cmd = New ADODB.Command
With cmd
Set prmPersonID = .CreateParameter("varPersonID", adInteger, adParamInput, , valPersonID)

.Parameters.Append prmPersonID

    .ActiveConnection = CurrentProject.Connection
    .CommandText = strStoredProcedure
    .CommandType = adCmdStoredProc
    .Execute

    Me.lblStatus.Caption = rst.Status
    'cleanup the ADO objects
Set prmPersonID = Nothing

Set rst = Nothing
Set cmd = Nothing

End With
end Sub
below is the stored procedure

ALTER PROCEDURE [dbo].[stp_APDB_PersonStatusByPersonID]
	-- Add the parameters for the stored procedure here	
@PersonID int

AS
declare 	@sql    nvarchar(max)  
declare     @params nvarchar(max)
declare		@Status nvarchar(12)

set @sql = ''
set @params = ''
set @sql = N'select [Status] from vw_PersonUnit where PersonID = @PersonID'

set @params = N'@PersonID int'
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	EXEC @Status = sp_executesql @sql,@params,
@PersonID = @PersonID;
print @sql

return @Status
END

Open in new window

LVL 1
jvantassel1Asked:
Who is Participating?
 
jvantassel1Connect With a Mentor Author Commented:
I figured it out.  I needed to add the cmd in the rst.open line.  It should read, rst.open cmd
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't believe you need anything more than this for a Stored Proc, assuming all you're doing is returning the Status based on the PersonID:

ALTER PROCEDURE [dbo].[stp_APDB_PersonStatusByPersonID]
      -- Add the parameters for the stored procedure here      
@PersonID int

AS

BEGIN
  SET NOCOUNT ON;
  SELECT [Status] FROM vw_PersonUnit WHERE PersonID = @PersonID'

END

Now, to "get" that status:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "stp_APDB_PersonStatusByPersonID @PersonID=" & Nz(Me.PersonID, 0),  Currentproject.Connection
Me.Label1.Caption = rst("Status")

This assumes that CurrentProject.Connection is a valid connection to the database housing that Stored Procedure, and that you have permission to use that sTored Procedure.
0
 
jvantassel1Author Commented:
This solution returns the same error.  Both techniques are doing the same thing.  Each of them open the Stored Procedure.  
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Which line is erroring?

You might try this:

Me.Lable1.Caption = rst.Fields(0).Value
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry ... my syntax is wrong:

Should be:

rst.Open "EXEC stp_APDB_PersonStatusByPersonID @PersonID=" & Nz(Me.PersonID, 0),  Currentproject.Connection
0
 
jvantassel1Author Commented:
I attached the code for the on current event of the form.  I'm still getting the same error when the rst is being opened.   It runs fine in sql.  I commented out the other code I was using, but it's really all the same and gives the same erros either way.
Private Sub Form_Current()
On Error GoTo ProcError
Dim rst As Recordset
Dim cmd As ADODB.Command
Dim strStoredProcedure As String
Dim prmPersonID As ADODB.Parameter
Dim varPersonID As Variant
Dim valPersonID As Variant
Dim prmReturn As Variant

valPersonID = Nz(Me.PersonID, 0)

strStoredProcedure = "stp_APDB_PersonStatusByPersonID"
'Set cmd = New ADODB.Command
'With cmd
'Set prmPersonID = .CreateParameter("varPersonID", adInteger, adParamInput, , valPersonID)
'
'.Parameters.Append prmPersonID
'
'    .ActiveConnection = CurrentProject.Connection
'    .CommandText = strStoredProcedure
'    .CommandType = adCmdStoredProc
'    .Execute
'
'    rst.MoveFirst
'rst.Open strStoredProcedure, valPersonID
'Me.lblStatus = "exec stp_APDB_PersonStatusByPersonID " & valPersonID
    'cleanup the ADO objects
    
rst.Open "stp_APDB_PersonStatusByPersonID @PersonID=" & Nz(Me.PersonID, 0), CurrentProject.Connection
Me.lblStatus.Caption = rst(0).Value
Set prmPersonID = Nothing

Set rst = Nothing
Set cmd = Nothing


If IsNull(PersonID) = False Then
        Me.Caption = Me!txtLastName & ", " & Me!txtFirstName & ": " & right(Me.SSN, 4)
Else
    Me.Caption = "Enter Soldier Information"
End If
If [frmPersonUnit].Form![cboMilEmploymentStatusID] = 4 Then
    [frmPersonUnit].Form![cboStateTerritoryID].Enabled = True
Else
    [frmPersonUnit].Form![cboStateTerritoryID].Enabled = False
End If

Dim sql1, sql2 As String
'On Error Resume Next


sql1 = "Exec stp_APDB_RankLookUpByBranchofService " & [frmPersonUnit].Form![frBranchOfService].Value
sql2 = ""
sql2 = "Exec stp_APDB_SkillLookUpByByBranchofService " & [frmPersonUnit].Form![frBranchOfService].Value

[frmPersonUnit].Form![frmUnitPersonRank].Form![cboRankID].RowSource = sql1
[frmPersonUnit].Form!frmUnitPersonRank.Requery

ExitProc:
   Exit Sub
ProcError:
   MsgBox "Error: " & Err.Number & ". " & Err.Description
   Resume ExitProc

End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try the updated syntax:

rst.Open "EXEC your sp name"

Your error occurs in the rst.Open line?
0
 
jvantassel1Author Commented:
Hi,
My errors do occur on the open line, regardless of which statement I try.  I get the same error each time.
"run-time error 91: Object variable or With block variable not set "
0
 
jvantassel1Author Commented:
I'm taking another swag at this.  I realized one issue is the difference between using execute and open, since I want return a recordset I need to use the rst.open method rather than the execute.  I want to use stored procedures for this.  Below is the code behind.  I'm currently getting a 3709 error: "The connection cannot be used to perform this operation.  It is either closed or invalid in this context."  I don't understand why I'm getting this error.

On Error GoTo ProcError
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strStoredProcedure As String
Dim prmPersonID As ADODB.Parameter
Dim varPersonID As Variant
Dim valPersonID As Variant
Dim prmReturn As Variant
Dim var As Variant

valPersonID = Nz(Me.PersonID, 0)

strStoredProcedure = "stp_APDB_PersonStatusByPersonID"
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = CurrentProject.Connection
   
    If Not IsMissing(varPersonID) Then
        Set prmPersonID = .CreateParameter("varPersonID", adInteger, adParamInput, , valPersonID)
        .Parameters.Append prmPersonID
    End If

   .CommandText = strStoredProcedure
   .CommandType = adCmdStoredProc
End With
Set rst = New ADODB.Recordset
If CurrentProject.IsConnected() = False Then

    MsgBox "help"
Else

rst.Open
End If
var = rst.Fields(0)
Me.lblStatus.Caption = var
    'cleanup the ADO objects
   

Set prmPersonID = Nothing

Set rst = Nothing
Set cmd = Nothing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.