ScottGutman
asked on
DateTime parameter conversion problem
This is a wierd problem. I have solved it but i don't know why and i am afraid that it will crop up again.
I writing this program in Access 2002, and using MS SQL server 2002 on a ms server 2000.
This is a function in my Access *.ADP program
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "GetLeads"
cmd.CommandType = adCmdStoredProc
'Set the parameters
cmd.Parameters.Append cmd.CreateParameter("@Lead
cmd.Parameters.Append cmd.CreateParameter("@Lead
cmd.Parameters.Append cmd.CreateParameter("@AgeD
Set GetLeadList = New ADODB.Recordset
Set GetLeadList = cmd.Execute
I wrote this to pass variables to a stored procedure. Works awesome, super fast.
This worked great for about 3 weeks. Then i started getting an error about converting the variable(don't remember the exact words). I tracked the error to the Agedate variable. The answer that solved it was to shorten the date time string. For exmaple, i removed a space befor am/pm and the '20' from 2003. In other words This string might fail "11/17/2003 10:20:00 PM" but this one "11/17/03 10:20:00PM" would not.
I even wrote this bit of code to do the deed:
Function RemoveTimeSpace(TS)
i = 0
SpaceFlag = 0
NewTime = ""
TS = CStr(TS)
Do Until i = Len(TS)
i = i + 1
CharCheck = Mid(TS, i, 1)
If CharCheck = " " Then SpaceFlag = SpaceFlag + 1
If CharCheck = "2" Then
Test2003 = Mid(TS, i, 4)
If Test2003 = 2003 Then
i = i + 2
CharCheck = Mid(TS, i, 1)
End If
End If
If SpaceFlag = 2 And CharCheck = " " Then
Else
NewTime = NewTime + CharCheck
End If
Loop
RemoveTimeSpace = NewTime
End Function
I am afraid this error will return. I don't know what to remove next or why this even worked. Can some one explain this.
I writing this program in Access 2002, and using MS SQL server 2002 on a ms server 2000.
This is a function in my Access *.ADP program
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "GetLeads"
cmd.CommandType = adCmdStoredProc
'Set the parameters
cmd.Parameters.Append cmd.CreateParameter("@Lead
cmd.Parameters.Append cmd.CreateParameter("@Lead
cmd.Parameters.Append cmd.CreateParameter("@AgeD
Set GetLeadList = New ADODB.Recordset
Set GetLeadList = cmd.Execute
I wrote this to pass variables to a stored procedure. Works awesome, super fast.
This worked great for about 3 weeks. Then i started getting an error about converting the variable(don't remember the exact words). I tracked the error to the Agedate variable. The answer that solved it was to shorten the date time string. For exmaple, i removed a space befor am/pm and the '20' from 2003. In other words This string might fail "11/17/2003 10:20:00 PM" but this one "11/17/03 10:20:00PM" would not.
I even wrote this bit of code to do the deed:
Function RemoveTimeSpace(TS)
i = 0
SpaceFlag = 0
NewTime = ""
TS = CStr(TS)
Do Until i = Len(TS)
i = i + 1
CharCheck = Mid(TS, i, 1)
If CharCheck = " " Then SpaceFlag = SpaceFlag + 1
If CharCheck = "2" Then
Test2003 = Mid(TS, i, 4)
If Test2003 = 2003 Then
i = i + 2
CharCheck = Mid(TS, i, 1)
End If
End If
If SpaceFlag = 2 And CharCheck = " " Then
Else
NewTime = NewTime + CharCheck
End If
Loop
RemoveTimeSpace = NewTime
End Function
I am afraid this error will return. I don't know what to remove next or why this even worked. Can some one explain this.
Post your stored procedure GetLeads. If the @AgeDate is a date parameter than you should be passing a date not a varchar.
Anthony
Anthony
ASKER
CREATE PROCEDURE dbo.GetLeads
(@LeadStart int,
@LeadClass int,
@AgeDate datetime)
AS SELECT TOP 1000 ID, State, Scheduled, Class, Aquired
FROM dbo.Lead
WHERE (Scheduled = 0) AND (ID < @LeadStart) AND (Class = @LeadClass) AND (Aquired < @AgeDate)
ORDER BY ID DESC
dumb question, how do i pass a date and not Varchar.
I got the parameter code by cuttin and pasting. I don't really understand it.
(@LeadStart int,
@LeadClass int,
@AgeDate datetime)
AS SELECT TOP 1000 ID, State, Scheduled, Class, Aquired
FROM dbo.Lead
WHERE (Scheduled = 0) AND (ID < @LeadStart) AND (Class = @LeadClass) AND (Aquired < @AgeDate)
ORDER BY ID DESC
dumb question, how do i pass a date and not Varchar.
I got the parameter code by cuttin and pasting. I don't really understand it.
ASKER
i tried the Cdate conversion. it did not work.
besides the variable agedate is created by
AgeDate = Now()
besides the variable agedate is created by
AgeDate = Now()
This works for me (change the values of the parameters as appropriate):
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.ConnectionString = MY_CONN_STRING
.Open
End With
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "GetLeads"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@LeadSta
.Parameters.Append .CreateParameter("@LeadCla
.Parameters.Append .CreateParameter("@AgeDate
Set rs = .Execute
End With
Set cmd = Nothing
'Rest of your recordset code goes here
rs.Close
cn.Close
Set cn = Nothing
Notice that it is not necessary to instantiate the Recordset parameter prior to using it. The Command object's Execute method does it.
Anthony
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.ConnectionString = MY_CONN_STRING
.Open
End With
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "GetLeads"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@LeadSta
.Parameters.Append .CreateParameter("@LeadCla
.Parameters.Append .CreateParameter("@AgeDate
Set rs = .Execute
End With
Set cmd = Nothing
'Rest of your recordset code goes here
rs.Close
cn.Close
Set cn = Nothing
Notice that it is not necessary to instantiate the Recordset parameter prior to using it. The Command object's Execute method does it.
Anthony
If AgeDate is now() then you do not even have to pass date to this proc.
You can remove agedate parameter and then add (Aquired < getdate()) to proc.
CREATE PROCEDURE dbo.GetLeads
(@LeadStart int,
@LeadClass int)
AS SELECT TOP 1000 ID, State, Scheduled, Class, Aquired
FROM dbo.Lead
WHERE (Scheduled = 0) AND (ID < @LeadStart) AND (Class = @LeadClass) AND (Aquired < getdate())
ORDER BY ID DESC
You can remove agedate parameter and then add (Aquired < getdate()) to proc.
CREATE PROCEDURE dbo.GetLeads
(@LeadStart int,
@LeadClass int)
AS SELECT TOP 1000 ID, State, Scheduled, Class, Aquired
FROM dbo.Lead
WHERE (Scheduled = 0) AND (ID < @LeadStart) AND (Class = @LeadClass) AND (Aquired < getdate())
ORDER BY ID DESC
ASKER
To acperkins:
This is the error I got from using the < .Parameters.Append .CreateParameter("@AgeDate
Run-time error '-2147217913 (80040e07)':
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
This is the error I got from using the < .Parameters.Append .CreateParameter("@AgeDate
Run-time error '-2147217913 (80040e07)':
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
ASKER
To namasi_navaretnam:
I really want to pass the variable for flexibility purposes. I will need to pass dates on other SP's. Thanks for the input.
I really want to pass the variable for flexibility purposes. I will need to pass dates on other SP's. Thanks for the input.
SQL has probably got day and month mixed up.
Does every day from the 13th onwards fail?
In Australia, 11/17/2003 will fail because there is no 17th month. Even though you think you're passing the date.
Does every day from the 13th onwards fail?
In Australia, 11/17/2003 will fail because there is no 17th month. Even though you think you're passing the date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I missed a final paranthesis, it should be:
.Parameters.Append .CreateParameter("@AgeDate
Anthony
.Parameters.Append .CreateParameter("@AgeDate
Anthony
I just re-tested and that also worked for me.
Anthony
Anthony
ASKER
I learned to fix the problem in another question. The problem was fixed by increasing the size of varchar to 255. may be not correct but works great.
Thanks for your help.
(@AgeDate Varchar(255))
Thanks for your help.
(@AgeDate Varchar(255))
>>may be not correct but works great.<<
Fair enough. I do not agree with your solution, but if it works for you that is all that counts ...
Please close this question. Here is how:
I answered my question myself. What do I do?
https://www.experts-exchange.com/help/closing.jsp#4
Thanks,
Anthoy
Fair enough. I do not agree with your solution, but if it works for you that is all that counts ...
Please close this question. Here is how:
I answered my question myself. What do I do?
https://www.experts-exchange.com/help/closing.jsp#4
Thanks,
Anthoy
Thanks for the points.
Anthony
Anthony
cmd.Parameters.Append cmd.CreateParameter("@AgeD
CDate Examples:
MyDate = "October 19, 1962" ' Define date.
MyShortDate = CDate(MyDate) ' Convert to Date data type.
MyTime = "4:35:47 PM" ' Define time.
MyShortTime = CDate(MyTime) ' Convert to Date data type.
Use IsDate to check to see if valid date
IsDate Examples:
Dim MyDate, YourDate, NoDate, MyCheck
MyDate = "October 19, 1962": YourDate = #10/19/62#: NoDate = "Hello"
MyCheck = IsDate(MyDate) ' Returns True.
MyCheck = IsDate(YourDate) ' Returns True.
MyCheck = IsDate(NoDate) ' Returns False.