|
|
Problem 1
I have a form with two dates, StartDate and EndDate. I need to pass these to
a stored procedure in SQL2005 and open a recordset based on it, see below
for stored procedure.
Problem2
I need some help with getting the syntax right in VBA to pass these dates
and open a recordset based on the sp. At present I am getting error 3021
which is because the recordset is empty. The below VBA is not syntactically
correct! and could be better. The dates from the TextBoxes are in the format
dd/mm/yy so I'm guessing that they may need to be converted correctly at
some point.
Regards
======VBA so far=======
<snip> Dim's etc
Set cnn = New ADODB.Connection
strdb = "Q:\ManagementInformation.adp"
' Open connection
With cnn
.ConnectionString = CurrentProject.BaseConnectionString
.CursorLocation = adUseClient
.Open
End With
Set cmd = New ADODB.Command
Set param1 = New ADODB.Parameter
Set param2 = New ADODB.Parameter
With param1
.Name = "[ at ]StartDate"
.Type = adDate
.Size = 2
.Value = strStartDate ' '2008-10-31 00:00:00'
End With
With param2
.Name = "[ at ]EndDate"
.Type = adDate
.Size = 2
.Value = strEndDate
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 '<==== This line gets an error 3021
======Parameters and Properties of sp======
name = [ at ]RETURN_VALUE
Direction = 4
Type = 3
Size = 0
Precision = 10
NumericScale = 0
name = [ at ]startdate
Direction = 1
Type = 135
Size = 0
Precision = 0
NumericScale = 0
name = [ at ]enddate
Direction = 1
Type = 135
Size = 0
Precision = 0
NumericScale = 0
======Stored Procedure=====
ALTER PROCEDURE dbo.floydsp_POPOrderChaser
(
[ at ]startdate datetime,
[ at ]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 >= [ at ]startdate) AND
(Floyd.dbo.POPOrderReturn.DocumentDate <= [ at ]enddate))
RETURN
|
|
I just tried your exact sample of code and it works perfectly here. You are
probably getting the error 3021 because the returned recordset is empty;
ie., there are no row to fulfill the conditions of the query.
Check for the EOF condition on the recordset before trying to access any of
its record(s).
A possible explanation would be that the field DocumentDate has a time value
in it, so you should either remove it or change your query to something
like:
.... (Floyd.dbo.POPOrderReturn.DocumentDate >= [ at ]startdate) AND
(Floyd.dbo.POPOrderReturn.DocumentDate < ([ at ]enddate + 1)))
For performance reason, you should use a temporary variable:
declare [ at ]enddate2 datetime
set [ at ]enddate2 = [ at ]enddate + 1
.... (Floyd.dbo.POPOrderReturn.DocumentDate >= [ at ]startdate) AND
(Floyd.dbo.POPOrderReturn.DocumentDate < [ at ]enddate2))
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Terry" <it[ at ]REMOVETHISfloydautomatic.co.uk> wrote in message
news:uRbi8nPQJHA.4492[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Problem 1
> I have a form with two dates, StartDate and EndDate. I need to pass these
> to a stored procedure in SQL2005 and open a recordset based on it, see
> below for stored procedure.
> Problem2
> I need some help with getting the syntax right in VBA to pass these dates
> and open a recordset based on the sp. At present I am getting error 3021
> which is because the recordset is empty. The below VBA is not
> syntactically correct! and could be better. The dates from the TextBoxes
> are in the format dd/mm/yy so I'm guessing that they may need to be
> converted correctly at some point.
> Regards
>
> ======VBA so far=======
> <snip> Dim's etc
>
> Set cnn = New ADODB.Connection
> strdb = "Q:\ManagementInformation.adp"
> ' Open connection
> With cnn
> .ConnectionString = CurrentProject.BaseConnectionString
> .CursorLocation = adUseClient
> .Open
> End With
>
> Set cmd = New ADODB.Command
> Set param1 = New ADODB.Parameter
> Set param2 = New ADODB.Parameter
> With param1
> .Name = "[ at ]StartDate"
> .Type = adDate
> .Size = 2
> .Value = strStartDate ' '2008-10-31 00:00:00'
> End With
> With param2
> .Name = "[ at ]EndDate"
> .Type = adDate
> .Size = 2
> .Value = strEndDate
> 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 '<==== This line gets an error 3021
>
> ======Parameters and Properties of sp======
> name = [ at ]RETURN_VALUE
> Direction = 4
> Type = 3
> Size = 0
> Precision = 10
> NumericScale = 0
>
> name = [ at ]startdate
> Direction = 1
> Type = 135
> Size = 0
> Precision = 0
> NumericScale = 0
>
> name = [ at ]enddate
> Direction = 1
> Type = 135
> Size = 0
> Precision = 0
> NumericScale = 0
>
> ======Stored Procedure=====
> ALTER PROCEDURE dbo.floydsp_POPOrderChaser
>
> (
>
> [ at ]startdate datetime,
>
> [ at ]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 >= [ at ]startdate) AND
> (Floyd.dbo.POPOrderReturn.DocumentDate <= [ at ]enddate))
>
> RETURN
>
>
|
|
Hi,
I'm guessing that it is the dates being passed from the Textboxes to the sp.
Whether the format is wrong, or something else is what I will have to
determine. The data in the table is formated as yy-mm-dd 00:00:00 and Access
is passing dd/mm/yy. I will do some further testing with formats as there
should be rows returned from the table. Thanks for the help.
Regards
Terry
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23hRr51QQJHA.3404[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] >I just tried your exact sample of code and it works perfectly here. You
>are probably getting the error 3021 because the returned recordset is
>empty; ie., there are no row to fulfill the conditions of the query.
>
> Check for the EOF condition on the recordset before trying to access any
> of its record(s).
>
> A possible explanation would be that the field DocumentDate has a time
> value in it, so you should either remove it or change your query to
> something like:
>
> ... (Floyd.dbo.POPOrderReturn.DocumentDate >= [ at ]startdate) AND
> (Floyd.dbo.POPOrderReturn.DocumentDate < ([ at ]enddate + 1)))
>
> For performance reason, you should use a temporary variable:
>
> declare [ at ]enddate2 datetime
> set [ at ]enddate2 = [ at ]enddate + 1
>
>
> ... (Floyd.dbo.POPOrderReturn.DocumentDate >= [ at ]startdate) AND
> (Floyd.dbo.POPOrderReturn.DocumentDate < [ at ]enddate2))
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Terry" <it[ at ]REMOVETHISfloydautomatic.co.uk> wrote in message
> news:uRbi8nPQJHA.4492[ at ]TK2MSFTNGP06.phx.gbl...
>> Problem 1
>> I have a form with two dates, StartDate and EndDate. I need to pass these
>> to a stored procedure in SQL2005 and open a recordset based on it, see
>> below for stored procedure.
>> Problem2
>> I need some help with getting the syntax right in VBA to pass these dates
>> and open a recordset based on the sp. At present I am getting error 3021
>> which is because the recordset is empty. The below VBA is not
>> syntactically correct! and could be better. The dates from the TextBoxes
>> are in the format dd/mm/yy so I'm guessing that they may need to be
>> converted correctly at some point.
>> Regards
>>
>> ======VBA so far=======
>> <snip> Dim's etc
>>
>> Set cnn = New ADODB.Connection
>> strdb = "Q:\ManagementInformation.adp"
>> ' Open connection
>> With cnn
>> .ConnectionString = CurrentProject.BaseConnectionString
>> .CursorLocation = adUseClient
>> .Open
>> End With
>>
>> Set cmd = New ADODB.Command
>> Set param1 = New ADODB.Parameter
>> Set param2 = New ADODB.Parameter
>> With param1
>> .Name = "[ at ]StartDate"
>> .Type = adDate
>> .Size = 2
>> .Value = strStartDate ' '2008-10-31 00:00:00'
>> End With
>> With param2
>> .Name = "[ at ]EndDate"
>> .Type = adDate
>> .Size = 2
>> .Value = strEndDate
>> 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 '<==== This line gets an error 3021
>>
>> ======Parameters and Properties of sp======
>> name = [ at ]RETURN_VALUE
>> Direction = 4
>> Type = 3
>> Size = 0
>> Precision = 10
>> NumericScale = 0
>>
>> name = [ at ]startdate
>> Direction = 1
>> Type = 135
>> Size = 0
>> Precision = 0
>> NumericScale = 0
>>
>> name = [ at ]enddate
>> Direction = 1
>> Type = 135
>> Size = 0
>> Precision = 0
>> NumericScale = 0
>>
>> ======Stored Procedure=====
>> ALTER PROCEDURE dbo.floydsp_POPOrderChaser
>>
>> (
>>
>> [ at ]startdate datetime,
>>
>> [ at ]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 >= [ at ]startdate) AND
>> (Floyd.dbo.POPOrderReturn.DocumentDate <= [ at ]enddate))
>>
>> RETURN
>>
>>
>
>
|
|
It's quite possible that the transformation of the textboxes to the date
format is not done right depending on the localisation of your machine and
the way you are doing it. However, as you give no detail about this, it's
impossible to tell. Access can read a text value as either dd/mm/yy (not
sure about that) or mm/dd/yy but it cannot pass it as dd/mm/yy to the
sql-server when using the Date or the DateTime format for the ADO parameter.
Make a quick test by printing the values of the dates before calling the SP.
You can also check what's passed on to the SP by taking a look with the
SQL-Server Profiler or by returning the values using a Select statement:
select [ at ]startdate as startdate, [ at ]enddate as enddate ...
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Terry" <newsgrps[ at ]REMOVETHISwhite-light.me.uk> wrote in message
news:uxBEjrqQJHA.1172[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > Hi,
>
> I'm guessing that it is the dates being passed from the Textboxes to the
> sp. Whether the format is wrong, or something else is what I will have to
> determine. The data in the table is formated as yy-mm-dd 00:00:00 and
> Access is passing dd/mm/yy. I will do some further testing with formats as
> there should be rows returned from the table. Thanks for the help.
> Regards
> Terry
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:%23hRr51QQJHA.3404[ at ]TK2MSFTNGP05.phx.gbl...
>>I just tried your exact sample of code and it works perfectly here. You
>>are probably getting the error 3021 because the returned recordset is
>>empty; ie., there are no row to fulfill the conditions of the query.
>>
>> Check for the EOF condition on the recordset before trying to access any
>> of its record(s).
>>
>> A possible explanation would be that the field DocumentDate has a time
>> value in it, so you should either remove it or change your query to
>> something like:
>>
>> ... (Floyd.dbo.POPOrderReturn.DocumentDate >= [ at ]startdate) AND
>> (Floyd.dbo.POPOrderReturn.DocumentDate < ([ at ]enddate + 1)))
>>
>> For performance reason, you should use a temporary variable:
>>
>> declare [ at ]enddate2 datetime
>> set [ at ]enddate2 = [ at ]enddate + 1
>>
>>
>> ... (Floyd.dbo.POPOrderReturn.DocumentDate >= [ at ]startdate) AND
>> (Floyd.dbo.POPOrderReturn.DocumentDate < [ at ]enddate2))
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Terry" <it[ at ]REMOVETHISfloydautomatic.co.uk> wrote in message
>> news:uRbi8nPQJHA.4492[ at ]TK2MSFTNGP06.phx.gbl...
>>> Problem 1
>>> I have a form with two dates, StartDate and EndDate. I need to pass
>>> these to a stored procedure in SQL2005 and open a recordset based on it,
>>> see below for stored procedure.
>>> Problem2
>>> I need some help with getting the syntax right in VBA to pass these
>>> dates and open a recordset based on the sp. At present I am getting
>>> error 3021 which is because the recordset is empty. The below VBA is not
>>> syntactically correct! and could be better. The dates from the TextBoxes
>>> are in the format dd/mm/yy so I'm guessing that they may need to be
>>> converted correctly at some point.
>>> Regards
>>>
>>> ======VBA so far=======
>>> <snip> Dim's etc
>>>
>>> Set cnn = New ADODB.Connection
>>> strdb = "Q:\ManagementInformation.adp"
>>> ' Open connection
>>> With cnn
>>> .ConnectionString = CurrentProject.BaseConnectionString
>>> .CursorLocation = adUseClient
>>> .Open
>>> End With
>>>
>>> Set cmd = New ADODB.Command
>>> Set param1 = New ADODB.Parameter
>>> Set param2 = New ADODB.Parameter
>>> With param1
>>> .Name = "[ at ]StartDate"
>>> .Type = adDate
>>> .Size = 2
>>> .Value = strStartDate ' '2008-10-31 00:00:00'
>>> End With
>>> With param2
>>> .Name = "[ at ]EndDate"
>>> .Type = adDate
>>> .Size = 2
>>> .Value = strEndDate
>>> 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 '<==== This line gets an error 3021
>>>
>>> ======Parameters and Properties of sp======
>>> name = [ at ]RETURN_VALUE
>>> Direction = 4
>>> Type = 3
>>> Size = 0
>>> Precision = 10
>>> NumericScale = 0
>>>
>>> name = [ at ]startdate
>>> Direction = 1
>>> Type = 135
>>> Size = 0
>>> Precision = 0
>>> NumericScale = 0
>>>
>>> name = [ at ]enddate
>>> Direction = 1
>>> Type = 135
>>> Size = 0
>>> Precision = 0
>>> NumericScale = 0
>>>
>>> ======Stored Procedure=====
>>> ALTER PROCEDURE dbo.floydsp_POPOrderChaser
>>>
>>> (
>>>
>>> [ at ]startdate datetime,
>>>
>>> [ at ]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 >= [ at ]startdate) AND
>>> (Floyd.dbo.POPOrderReturn.DocumentDate <= [ at ]enddate))
>>>
>>> RETURN
>>>
>>>
>>
>>
>
>
|
|
I have had a problem like yours. I ended up with making a small function in
VBA on the client side that translated any date to a text string. Then I
passed the text string to the stored procedure as a parameter.
I used functions YEAR, DATE, DAY in access vba to extract data form the date
textboxes and put together a string in vba that sql server understand.
Then in SQL server, i used CONVERT to convert incoming varchar date
parameters to smalldatetime. Help on SQL server CONVERT lists all possible
date values. I guess this procedure is quite safe no matter what kind of date
format you have selected in windows regional settings etc.
Regards
Tore
|
|
Thanks Tore,
Sorry for the late reply. I did manage to figure out the problem. Good
suggestion to use a function, think I will use that.
Regards
Terry
"Tore" <tore[ at ]nospam.nospam> wrote in message
news:6D74259E-7099-4E82-8AD9-7BDDF73DBCA7[ at ]microsoft.com...
[Quoted Text] >I have had a problem like yours. I ended up with making a small function in
> VBA on the client side that translated any date to a text string. Then I
> passed the text string to the stored procedure as a parameter.
>
> I used functions YEAR, DATE, DAY in access vba to extract data form the
> date
> textboxes and put together a string in vba that sql server understand.
>
> Then in SQL server, i used CONVERT to convert incoming varchar date
> parameters to smalldatetime. Help on SQL server CONVERT lists all possible
> date values. I guess this procedure is quite safe no matter what kind of
> date
> format you have selected in windows regional settings etc.
>
> Regards
>
> Tore
|
|
|