• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
zimmer9
Asked:
zimmer9
  • 5
  • 2
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now