How would you pass a date to a stored procedure when the date is originally a text value ?

I am developing an Access application using Access as the front end and SQL Server as the back end database.

On my interface, I have a text box that I named txtDateFrom with a format of Short Date.
I have another text box that I named txtDateTo with a format of Short Date.

I use the following code to execute a Stored Procedure and pass the 2 dates to the Stored Procedure as input paramters. Do you know where I went wrong becauses I am not retrieving the correct range of dates using my Stored Procedure. What value would I use as the size in the CreateParameter ?

Should I perform a conversion on the txtDateFrom and txtDateTo ?

I use a type of Datetime in the Stored Procedure for both input paramters as follows:
@DteFrom datetime and @DteTo datetime.


Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDLAAAAcctsMod"
       .Parameters.Append .CreateParameter("DteFrom", adInteger, adParamInput, 8, txtDateFrom)
       .Parameters.Append .CreateParameter("DteTo", adInteger, adParamInput, 8, txtDateTo)
       .ActiveConnection = CurrentProject.Connection
       Set rstQueryFS = .Execute
End With
----------------------------------------------------------------
My stored procedure is as follows:

CREATE PROCEDURE dbo.procUDLAAAAcctsMod
@DteFrom datetime, @DteTo datetime
AS

If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblUDLAAAAccts' AND TYPE = 'U')
DROP TABLE dbo.tblUDLAAAAccts

SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
CASE WHEN C.TaxIDInd = "1" THEN " " ELSE UPPER(C.FirstName) END AS [First Name],
UPPER(C.MiddleInitial) AS [MI],
CASE WHEN C.TaxIDInd = "1" THEN C.FirstName ELSE UPPER(C.LastName) END AS [Last Name],
UPPER(C.SecondNameFirst) AS [2nd Name First],
UPPER(C.SecondNameMid) AS [2nd Name Mid], UPPER(C.SecondNameLast) AS [2nd Name Last],C.MasterCardNumber, C.DateLost As [Undeliverable Date],
DateDiff(d, C.DateLost, GetDate()) As [Counter (Run Date - Date Lost)], Right(C.OfficeNumber,3) As Branch
INTO dbo.tblUDLAAAAccts  
FROM dbo.tblCustomers1000 As C  INNER JOIN dbo.tblProducts1000 As P  ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber
WHERE C.RedFlag = 'N' AND P.DivPayOpt = '3'
ORDER BY C.Branch, C.MasterCardNumber
GO
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's try this:

Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDLAAAAcctsMod"
       .Parameters.Append .CreateParameter("DteFrom", adDate, adParamInput, , cdate(txtDateFrom.Text))
       .Parameters.Append .CreateParameter("DteTo", adDate, adParamInput, , cdate(txtDateTo.Text))
       .ActiveConnection = CurrentProject.Connection
       Set rstQueryFS = .Execute
End With

Open in new window

0
zimmer9Author Commented:
The compiler stops at the following line:

.Parameters.Append .CreateParameter("DteFrom", adDate, adParamInput, , cdate(txtDateFrom.Text))

with the Runtime error:

Runtime error '2185
You can't reference a property or method for a control unless the control has the focus.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok

.Parameters.Append .CreateParameter("DteFrom", adDate, adParamInput, , cdate(txtDateFrom.Value))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

zimmer9Author Commented:
Okay, using     cdate(txtDateFrom.Value              generate a clean compile.

In the tables that I am performing a SELECT clause on in the Stored Procedure, one of the fields I retrieve is titled Date Lost.
The field Date Lost has values of the 1st of the month.
For example, the records have a Date Lost value of either 12/1/2007, 1/1/2008, 2/1/2008, etc.

If I execute the Stored Procdure and pass along:
txtDateFrom value of 1/1/2008 and a
txtDateTo     value of 1/1/2008, I obviously would expect the Date Lost values in the result set to be exclusively for 1/1/2008.

However, after executing the Stored Procedure, the Result Set has Date Lost values of 11/1/2007, 3/1/2007, 7/1/2004. The parameters do not seem to operate correctly in the SQL statement.

If I execute the contents of the Stored Procedure in the SQL Query Analyzer as follows,
I get exclusively records with a Date Lost value of 1/1/2008 which is the correct Result Set of 2,748 records.

Declare @DteFrom Datetime
Declare @DteTo Datetime
Set @DteFrom = '2008-01-01'
Set @DteTo = '2008-01-01'
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
CASE WHEN C.TaxIDInd = '1' THEN ' ' ELSE UPPER(C.FirstName) END AS [First Name],
UPPER(C.MiddleInitial) AS [MI],
CASE WHEN C.TaxIDInd = '1' THEN C.FirstName ELSE UPPER(C.LastName) END AS [Last Name],
UPPER(C.SecondNameFirst) AS [2nd Name First],
UPPER(C.SecondNameMid) AS [2nd Name Mid], UPPER(C.SecondNameLast) AS [2nd Name Last],C.MasterCardNumber, C.DateLost As [Undeliverable Date],
DateDiff(d, C.DateLost, GetDate()) As [Counter (Run Date - Date Lost)], Right(C.OfficeNumber,3) As Branch
FROM dbo.tblCustomers1000 As C  INNER JOIN dbo.tblProducts1000 As P  ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber
WHERE C.RedFlag = 'N' AND P.DivPayOpt = '3' AND C.DateLost >= @DteFrom AND C.DateLost <= @DteTo
ORDER BY C.Branch, C.MasterCardNumber
-------------------------------------------------------------------------------------------------------------
If I perform the following execution of the Stored Procedure, I get too many records in the Result Set:
exec dbo.procUDLAAAAcctsMod '2008-01-01','2008-01-01'
0
zimmer9Author Commented:
Hold on, I may have dropped a line of code in my stored procedure. I'll get back to you. Sorry.
0
zimmer9Author Commented:
Angel, thanks a million.
0
zimmer9Author Commented:
Thanks a million Angel. Angel Rules.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.