How to get Textbox dates into Stored Procedure

[Terry]

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 = "@StartDate"
.Type = adDate
.Size = 2
.Value = strStartDate ' '2008-10-31 00:00:00'
End With
With param2
.Name = "@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 = @RETURN_VALUE
Direction = 4
Type = 3
Size = 0
Precision = 10
NumericScale = 0

name = @startdate
Direction = 1
Type = 135
Size = 0
Precision = 0
NumericScale = 0

name = @enddate
Direction = 1
Type = 135
Size = 0
Precision = 0
NumericScale = 0

======Stored Procedure=====
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

 

[Sylvain Lafontaine]

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 >= @startdate) AND
(Floyd.dbo.POPOrderReturn.DocumentDate < (@enddate + 1)))

For performance reason, you should use a temporary variable:

declare @enddate2 datetime
set @enddate2 = @enddate + 1


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

 

[Terry]

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]

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 @startdate as startdate, @enddate as enddate ...

 

[Tore]

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

 

[Terry]

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