Stored Procedure Based Recordset

[Terry]

I need to get a recordset from a stored procedure, however I think my coding
is wrong and get an empty recordset as an error 3021 is returned from the
recordcount line.
Regards

Set cnn = New ADODB.Connection
strdb = "Q:\ManagementInformation.adp"
' Open connection
With cnn
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = adUseClient
.Open
End With

Dim dteStartDate As Date
Dim dteEndDate As Date
dteStartDate = Forms!frmReports!txtStartDate
dteEndDate = Forms!frmReports!txtEndDate
Set cmd = New ADODB.Command
Set param1 = New ADODB.Parameter
Set param2 = New ADODB.Parameter
With param1
.Name = "@StartDate"
.Type = adDate
.Size = 2
.Value = dteStartDate
End With
With param2
.Name = "@EndDate"
.Type = adDate
.Size = 2
.Value = dteEndDate
End With
With cmd
.Parameters.Append param1
.Parameters.Append param2
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "floydsp_POPOrderChaser"
End With

' Open recordset
Set rst = New ADODB.Recordset
Set rst = cmd.Execute
intCount = rst.RecordCount

 

[Sylvain Lafontaine]

Hi, first, the official newsgroup for ADP is
microsoft.public.access.adp.sqlserver. Another interesting newsgroup for
this question could also be microsoft.public.data.ado.

Second, there are many potential problems with your code. To begin with, I'm
not sure if adDate is the right type here but as you don't give us the
version of SQL-Server that you are using as well as the code of your SP; it'
impossible to tell. However, here's a little procedure to display the
parameters and their properties for calling a SP:

Sub ListOfParameters (ProcedureName As String)

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = ProcedureName

cmd.Parameters.Refresh

Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

Set cmd = Nothing

End Sub

For using this procedure, you must *not* give the prefix dbo. to the name of
the SP. See http://www.asp101.com/articles/john/adovbs/adojavas.inc for the
numerical values of the constants.

Also, when you call conn.Execute as a function, you must add a set
parenthesis:

Set rst = cmd.Execute()

It also make no sense to create a new recordset just before that. The way
that you are using your connection object also looks suspicious. Instead of
reusing CurrentProject.Connection, you create a new connection and after
that, you create again a second new one by not putting the Set instruction
before « .ActiveConnection = cnn ». However, these two little problems are
not probably the cause of your problem with the error 3021.

 

[Terry]

Hi,
Thanks for the help. I have noted the other newsgroups, but thought this one
appropiate because I'm using an Access project. My background is from Access
mdb with DAO so I'm fairly new with SQL and ADO.

The version of SQL server is 2005. The sub is run in a form module from an
OnClick event of a CommandButton and takes in two dates from Textboxes on
the form.

The sp is below. Thanks for the sub ListOfParameters, very useful, I'll run
it and see what is returned.

Regards
Terry
ALTER PROCEDURE dbo.floydsp_POPOrderChaser

(

@startdate datetime,

@enddate datetime

)

AS

(SELECT TOP (100) PERCENT Floyd.dbo.PLSupplierAccount.SupplierAccountNumber
AS popoc_SupplierAccountNumber,

Floyd.dbo.PLSupplierAccount.SupplierAccountName AS
popoc_SupplierAccountName,

Floyd.dbo.POPOrderReturn.POPOrderReturnID AS popoc_POPOrderReturnID,
Floyd.dbo.POPOrderReturn.DocumentNo AS popoc_DocumentNo,

Floyd.dbo.POPOrderReturn.DocumentDate AS popoc_DocumentDate,

Floyd.dbo.POPOrderReturn.RequestedDeliveryDate AS
popoc_RequestedDeliveryDate,

Floyd.dbo.PLSupplierLocation.AddressLine1 AS popoc_AddressLine1,
Floyd.dbo.PLSupplierLocation.AddressLine2 AS popoc_AddressLine2,

Floyd.dbo.PLSupplierLocation.AddressLine3 AS popoc_AddressLine3,
Floyd.dbo.PLSupplierLocation.AddressLine4 AS popoc_AddressLine4,

Floyd.dbo.PLSupplierLocation.PostCode AS popoc_PostCode,

PLSupplierTelephoneVw.plst_SupplierTelehoneNumber AS
popoc_SupplierTelephoneNumber,

PLSupplierFaxVw.plst_SupplierFaxNumber AS popoc_SupplierFaxNumber,

Floyd.dbo.PLSupplierAccount.SYSCountryCodeID AS popoc_SYSCountryCodeID,
Floyd.dbo.SYSCountryCode.Name AS popoc_CountryName

FROM Floyd.dbo.SYSCountryCode INNER JOIN

Floyd.dbo.PLSupplierAccount INNER JOIN

Floyd.dbo.POPOrderReturn ON Floyd.dbo.PLSupplierAccount.PLSupplierAccountID
= Floyd.dbo.POPOrderReturn.SupplierID INNER JOIN

Floyd.dbo.PLSupplierLocation ON
Floyd.dbo.PLSupplierAccount.PLSupplierAccountID =
Floyd.dbo.PLSupplierLocation.PLSupplierAccountID ON

Floyd.dbo.SYSCountryCode.SYSCountryCodeID =
Floyd.dbo.PLSupplierAccount.SYSCountryCodeID LEFT OUTER JOIN

PLSupplierTelephoneVw ON Floyd.dbo.PLSupplierAccount.PLSupplierAccountID =
PLSupplierTelephoneVw.plst_PLSupplierAccountID LEFT OUTER JOIN

PLSupplierFaxVw ON Floyd.dbo.PLSupplierAccount.PLSupplierAccountID =
PLSupplierFaxVw.plst_PLSupplierAccountID LEFT OUTER JOIN

POPOrderChaserExcludedOrdersVw ON

Floyd.dbo.POPOrderReturn.POPOrderReturnID =
POPOrderChaserExcludedOrdersVw.popeo_POPOrderReturnID

WHERE (Floyd.dbo.POPOrderReturn.POPOrderReturnID IS NULL) AND
(Floyd.dbo.POPOrderReturn.DocumentTypeID <> 1) AND

(Floyd.dbo.POPOrderReturn.DocumentDate >= @startdate) AND
(Floyd.dbo.POPOrderReturn.DocumentDate <= @enddate))

RETURN