?
Solved

How to use stored procedure for access data project

Posted on 2009-12-21
9
Medium Priority
?
757 Views
Last Modified: 2013-12-05
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

0
Comment
Question by:jvantassel1
  • 5
  • 4
9 Comments
 
LVL 85
ID: 26103753
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
 
LVL 1

Author Comment

by:jvantassel1
ID: 26104614
This solution returns the same error.  Both techniques are doing the same thing.  Each of them open the Stored Procedure.  
0
 
LVL 85
ID: 26107030
Which line is erroring?

You might try this:

Me.Lable1.Caption = rst.Fields(0).Value
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 85
ID: 26107142
Sorry ... my syntax is wrong:

Should be:

rst.Open "EXEC stp_APDB_PersonStatusByPersonID @PersonID=" & Nz(Me.PersonID, 0),  Currentproject.Connection
0
 
LVL 1

Author Comment

by:jvantassel1
ID: 26107983
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
 
LVL 85
ID: 26111762
Try the updated syntax:

rst.Open "EXEC your sp name"

Your error occurs in the rst.Open line?
0
 
LVL 1

Author Comment

by:jvantassel1
ID: 26112145
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
 
LVL 1

Author Comment

by:jvantassel1
ID: 26130733
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
 
LVL 1

Accepted Solution

by:
jvantassel1 earned 0 total points
ID: 26130791
I figured it out.  I needed to add the cmd in the rst.open line.  It should read, rst.open cmd
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question