jordanking
Programmer
- Sep 8, 2005
- 351
Hello,
I have an access project front end and a SQL server 2005 express back end.
I am having trouble returning a recorset back to VBA ADO from a stored procedure in SQL server.
The specific code that does not work is:
Set rsCust = cmd.Execute
which is outlined in the bottom of the second block of code I post.
The following is the stored procedure in SQL server:
the following is a public function that calls the stored procedure.
and this code is just a test that passes values to the public function above
If I simply execute "cmd.execute" the procedure, it returns all the appropriate output parameters. But if I switch that code to the following:
Set rsCust = cmd.Execute
There is no error, but none of the parameters have values, they are all empty, and the recordcount of the recordset = -1.
After all of that...
does anyone have an idea of where i am going wrong in returning the results fo the procedure to an adodb recordset?
.....
I'd rather be surfing
I have an access project front end and a SQL server 2005 express back end.
I am having trouble returning a recorset back to VBA ADO from a stored procedure in SQL server.
The specific code that does not work is:
Set rsCust = cmd.Execute
which is outlined in the bottom of the second block of code I post.
The following is the stored procedure in SQL server:
Code:
CREATE PROCEDURE [dbo].[spCustomer_SEARCH]
---Declare input variables
@OrderBy nvarchar(50)= NULL, --column name to odrder by
@OrderByDir nvarchar(1) = NULL, --Direction of sort 'A' = ascending 'D' = Descending
@CustomerID int = NULL,
@CompanyID int = NULL,
@FirstName nvarchar(50)= NULL,
@LastName nvarchar(50)= NULL,
@Billing nvarchar(50)= NULL,
@AccountStatus int = NULL,
@SDBID int = NULL,
@ERUserID int = NULL,
@Erequest bit = NULL,
@Mail bit = NULL,
@Created datetime = NULL,
@Name nvarchar(50)= NULL,
@Branch nvarchar(50)= NULL,
@LocalRows int = 0 OUTPUT,
@ReturnValue int = 0 OUTPUT,
@LocalError int = 0 OUTPUT,
@OutMessage nvarchar(500) OUTPUT
AS
BEGIN TRY
---Declare local scope variables
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
---Create the SQL string that will be dynamic
SELECT @sql = 'SELECT dbo.Customer.intCustomerID, dbo.Customer.intCompanyID, dbo.Customer.chrFirstName,
dbo.Customer.chrLastName, dbo.Customer.chrBilling, dbo.Customer.intAccountStatus, dbo.Customer.intSDBID,
dbo.Customer.intERUserID, dbo.Customer.blnErequest, dbo.Customer.blnMail, dbo.Customer.dtCreated,
dbo.Customer.intRecCount, dbo.Company.chrName, dbo.Company.chrBranch
FROM
dbo.Company INNER JOIN dbo.Customer ON dbo.Company.intCompanyID = dbo.Customer.intCompanyID
WHERE 1 = 1'
---Use is null to determin if parameter was passed, if so append to SQL string...
IF @CustomerID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intCustomerID = @CustomerID'
IF @CompanyID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intCompanyID = @CompanyID'
IF @FirstName IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.chrFirstName LIKE ''%'' + @FirstName + ''%'''
IF @LastName IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.chrLastName LIKE ''%'' + @LastName + ''%'''
IF @Billing IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.chrBilling LIKE ''%'' + @Billing + ''%'''
IF @AccountStatus IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intAccountStaus = @AccountStatus'
IF @SDBID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intSDBID = @SDBID'
IF @ERUserID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intERUserID = @ERUserID'
IF @Erequest IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.blnErequest = @Erequest'
IF @Mail IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.blnMail = @Mail'
IF @Created IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.dtCreated >= @Created' --Selects records created after this date
IF @Name IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Company.chrName LIKE ''%'' + @Name + ''%'''
IF @Branch IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Company.chrBranch LIKE ''%'' + @Branch + ''%'''
---Set the order by staement
IF @OrderBy IS NOT NULL
SELECT @sql = @sql + ' ORDER BY' +
CASE
WHEN @OrderBy = 'FirstName' AND @OrderByDir = 'D' THEN ' dbo.Customer.chrFirstName DESC'
WHEN @OrderBy = 'FirstName' AND @OrderByDir != 'D' THEN ' dbo.Customer.chrFirstName'
WHEN @OrderBy = 'LastName' AND @OrderByDir = 'D' THEN ' dbo.Customer.chrLastName DESC'
WHEN @OrderBy = 'LastName' AND @OrderByDir != 'D' THEN ' dbo.Customer.chrLastName'
WHEN @OrderBy = 'Billing' AND @OrderByDir = 'D' THEN ' dbo.Customer.chrBilling DESC'
WHEN @OrderBy = 'Billing' AND @OrderByDir != 'D' THEN ' dbo.Customer.chrBilling'
WHEN @OrderBy = 'SDBID' AND @OrderByDir = 'D' THEN ' dbo.Customer.intSDBID DESC'
WHEN @OrderBy = 'SDBID' AND @OrderByDir != 'D' THEN ' dbo.Customer.intSDBID'
WHEN @OrderBy = 'ERUserID' AND @OrderByDir = 'D' THEN ' dbo.Customer.intERUserID DESC'
WHEN @OrderBy = 'ERUserID' AND @OrderByDir != 'D' THEN ' dbo.Customer.intERUserID'
WHEN @OrderBy = 'AccountStatus' AND @OrderByDir = 'D' THEN ' dbo.Customer.intAccountStatus DESC'
WHEN @OrderBy = 'AccountStatus' AND @OrderByDir != 'D' THEN ' dbo.Customer.intAccountStatus'
WHEN @OrderBy = 'Created' AND @OrderByDir = 'D' THEN ' dbo.Customer.dtCreated DESC'
WHEN @OrderBy = 'Created' AND @OrderByDir != 'D' THEN ' dbo.Customer.dtCreated'
---Use default sort order for returned recordset.
ELSE ' dbo.Company.chrName, dbo.Company.chrBranch, dbo.Customer.chrLastName, dbo.Customer.chrFirstName'
END
ELSE
---Use default sort order for returned recordset.
SELECT @sql = @sql + ' ORDER BY dbo.Company.chrName, dbo.Company.chrBranch, dbo.Customer.chrLastName, dbo.Customer.chrFirstName'
----Set the parameter list for the sp_executesql call
SELECT @paramlist = '@CustomerID int,
@CompanyID int,
@FirstName nvarchar,
@LastName nvarchar(50),
@Billing nvarchar(50),
@AccountStatus int,
@SDBID int,
@ERUserID int,
@Erequest bit,
@Mail bit,
@Created datetime,
@Name nvarchar(50),
@Branch nvarchar(50)'
---Execute the function sp_executesql that takes the following parameters:
--- sql string, parameter list, and then the declared parameters in the same order as the parmaeter list
EXEC sp_executesql @sql, @paramlist,
@CustomerID, @CompanyID, @FirstName, @LastName, @Billing, @AccountStatus,
@SDBID, @ERUserID, @Erequest, @Mail, @Created, @Name, @Branch
---if no errors, set the return value to 0 and return the number of rows in the search results
SELECT @LocalRows = @@ROWCOUNT, @ReturnValue = 0
SELECT @LocalRows, @ReturnValue
END TRY
BEGIN CATCH
---If errors occured - capture error information and return to calling application
SELECT @ReturnValue = 1, @LocalError = ERROR_NUMBER(), @OutMessage = ('ROWS AFFECTED = ' + CAST(@@ROWCOUNT as nvarchar(6)) + ' SEVERITY = ' + CAST(ERROR_SEVERITY() as nvarchar(3)) + ' STATE = ' + CAST(ERROR_STATE() as nvarchar(2)) + ' PROCEDURE = ' + ERROR_PROCEDURE() + ' LINE = ' + CAST(ERROR_LINE() as nvarchar(3)) + ' MESSAGE ' + ERROR_MESSAGE())
SELECT @LocalError, @ReturnValue, @OutMessage
END CATCHthe following is a public function that calls the stored procedure.
Code:
Public Function DMS_Customer_Search(frm As Form, ByRef rsCust As ADODB.Recordset, Optional OrderBy As String, Optional OrderByDir As String, Optional intCompanyID As Integer, Optional chrFirstName As String, Optional chrLastName As String, Optional ChrBilling As String, Optional intAccountStatus As Integer, Optional intSDBID As Integer, Optional intERUserID As Integer, Optional dtCreated As Date) As Integer
'On Error GoTo ErrLog
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As New ADODB.Parameter
'''''''''''connect to SQL server
Set cnn = New ADODB.Connection
cnn.ConnectionString = frm.xProvider & frm.xDataSource
cnn.Open
''''''''''Set comand type and text
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spCustomer_Search"
cmd.CommandType = adCmdStoredProc
cmd.NamedParameters = True
'''explicitly create the input parameters - conditional on variable not being empty
If Len(Nz(OrderBy, "")) > 0 Then
Set prm = cmd.CreateParameter("@OrderBy", adChar, adParamInput, Len(OrderBy), OrderBy)
cmd.Parameters.Append prm
End If
If Len(Nz(OrderByDir, "")) > 0 Then
Set prm = cmd.CreateParameter("@OrderByDir", adChar, adParamInput, Len(OrderByDir), OrderByDir)
cmd.Parameters.Append prm
End If
If Nz(intCompanyID, 0) > 0 Then
Set prm = cmd.CreateParameter("@CompanyID", adInteger, adParamInput, , intCompanyID)
cmd.Parameters.Append prm
End If
If Len(Nz(chrFirstName, "")) > 0 Then
Set prm = cmd.CreateParameter("@FirstName", adChar, adParamInput, Len(chrFirstName), chrFirstName)
cmd.Parameters.Append prm
End If
If Len(Nz(chrLastName, "")) > 0 Then
Set prm = cmd.CreateParameter("@LastName", adChar, adParamInput, Len(chrLastName), chrLastName)
cmd.Parameters.Append prm
End If
If Len(Nz(ChrBilling, "")) > 0 Then
Set prm = cmd.CreateParameter("@Billing", adChar, adParamInput, Len(ChrBilling), ChrBilling)
cmd.Parameters.Append prm
End If
If Nz(intAccountStatus, 0) > 0 Then
Set prm = cmd.CreateParameter("@AccountStatus", adInteger, adParamInput, , intAccountStatus)
cmd.Parameters.Append prm
End If
If Nz(intSDBID, 0) > 0 Then
Set prm = cmd.CreateParameter("@SDBID", adInteger, adParamInput, , intSDBID)
cmd.Parameters.Append prm
End If
If Nz(intERUserID, 0) > 0 Then
Set prm = cmd.CreateParameter("@ERUserID", adInteger, adParamInput, , intERUserID)
cmd.Parameters.Append prm
End If
If Nz(dtCreated, 0) > 0 Then
Set prm = cmd.CreateParameter("@Created", adDate, adParamInput, , Format(dtCreated, "yyyymmdd"))
cmd.Parameters.Append prm
End If
'''explicitly create the output parameters
Set prm = cmd.CreateParameter("@LocalRows", adInteger, adParamOutput)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ReturnValue", adInteger, adParamOutput)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@LocalError", adInteger, adParamOutput)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@OutMessage", adChar, adParamOutput, 500)
cmd.Parameters.Append prm
'execute the command
'Set rsCust = cmd.Execute
cmd.Execute
'rsCust.Open cmd, , adOpenStatic
'Debug.Print rsCust.RecordCount
cmd.CommandText = ""
Debug.Print cmd.Parameters("@LocalRows").Value
'return the results/output
If IsEmpty(cmd.Parameters("@ReturnValue").Value) Or cmd.Parameters("@ReturnValue").Value <> 0 Then
err.Raise 513, , "SQL Server Customer Search Procedure Returned an Error" ''raise custom error to triger error tracking
End If
' Close the connection.
cnn.Close
DMS_Customer_Search = 0
ExitFunction:
Exit Function
ErrLog:
Set DMS_Error = New clsError
err.Source = "mdlPublicFunctions"
DMS_Error.ErrorObjInfo err, rtCustomer, atSearch, , Nz(cmd.Parameters("@LocalError").Value, 0), Nz(cmd.Parameters("@OutMessage").Value, "")
DoCmd.OpenForm "frmErrorLog"
Resume ExitFunction
End Functionand this code is just a test that passes values to the public function above
Code:
Dim rsCust As ADODB.Recordset
Set rsCust = New ADODB.Recordset
Dim iRet As Integer
iRet = DMS_Customer_Search(Me, rsCust, , , , "bi")
'rsCust.Close
'Set rsCust = NothingIf I simply execute "cmd.execute" the procedure, it returns all the appropriate output parameters. But if I switch that code to the following:
Set rsCust = cmd.Execute
There is no error, but none of the parameters have values, they are all empty, and the recordcount of the recordset = -1.
After all of that...
does anyone have an idea of where i am going wrong in returning the results fo the procedure to an adodb recordset?
.....
I'd rather be surfing