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_PersonStatusByPe
Set cmd = New ADODB.Command
With cmd
Set prmPersonID = .CreateParameter("varPerso
.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
"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_PersonStatusByPe
Set cmd = New ADODB.Command
With cmd
Set prmPersonID = .CreateParameter("varPerso
.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
ASKER
This solution returns the same error. Both techniques are doing the same thing. Each of them open the Stored Procedure.
Which line is erroring?
You might try this:
Me.Lable1.Caption = rst.Fields(0).Value
You might try this:
Me.Lable1.Caption = rst.Fields(0).Value
Sorry ... my syntax is wrong:
Should be:
rst.Open "EXEC stp_APDB_PersonStatusByPer
Should be:
rst.Open "EXEC stp_APDB_PersonStatusByPer
ASKER
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
Try the updated syntax:
rst.Open "EXEC your sp name"
Your error occurs in the rst.Open line?
rst.Open "EXEC your sp name"
Your error occurs in the rst.Open line?
ASKER
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 "
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 "
ASKER
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_PersonStatusByPe
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
If Not IsMissing(varPersonID) Then
Set prmPersonID = .CreateParameter("varPerso
.Parameters.Append prmPersonID
End If
.CommandText = strStoredProcedure
.CommandType = adCmdStoredProc
End With
Set rst = New ADODB.Recordset
If CurrentProject.IsConnected
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
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_PersonStatusByPe
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
If Not IsMissing(varPersonID) Then
Set prmPersonID = .CreateParameter("varPerso
.Parameters.Append prmPersonID
End If
.CommandText = strStoredProcedure
.CommandType = adCmdStoredProc
End With
Set rst = New ADODB.Recordset
If CurrentProject.IsConnected
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ALTER PROCEDURE [dbo].[stp_APDB_PersonStat
-- 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_PersonStatusByPe
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.