Hello,
I am getting the message Implicit conversion from string to date...
Please help
Protected Sub btnShowDetails_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles btnShowDetails.Click
Dim startdate As String = txtStartDate.Text
Dim enddate As String = txtEndDate.Text
'startdate = Convert.ToDateTime(startdate as date)
GRCallHistory.DataSource = getcallhistory("PhoneNumber",
"StartDate", "EndDate")
GRCallHistory.DataBind()
End Sub
Function getcallhistory(ByVal Phonenumber As String, _
ByVal StartDate As DateTime, _
ByVal enddate As DateTime) As
System.Data.SqlClient.SqlDataReader
Dim Connectionstring As String =
"Server=192.168.225. 8;Database=Wiband;uid=Wiband;password=op
tiplex"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection(Connectionstring)
Dim querystring As String = "select SUBSTRING(CAST(calldate AS
VARCHAR(20)), 1, 11) AS CallDate, SUBSTRING(CAST(calldate AS
VARCHAR(20)), 12, 13) AS CallTime, PhoneNumber, minutes,
calldescription, charge from
voipcalls.dbo.fw_fnVOIPGetCustCallHistory('" & Phonenumber & "', '" &
StartDate & "', '" & enddate & "', 0, -1)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand(querystring, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader =
sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End FunctionWell, what does the string you are trying to cast look like? You can't
expect people here to take your .NET code, build an application, and make
assumptions about what is actually being passed into values for StartDate
and EndDate. Heck, a lot of people here don't even have .NET installed.
So, please try to demonstrate what you're trying to convert to a DATETIME
with something a little more direct, e.g.
SELECT CONVERT(DATETIME, 'some string')
A
<lizansi@.gmail.com> wrote in message
news:1127400225.270131.95980@.g49g2000cwa.googlegroups.com...
> Hello,
> I am getting the message Implicit conversion from string to date...
> Please help
> Protected Sub btnShowDetails_Click(ByVal sender As Object, ByVal e As
> EventArgs) Handles btnShowDetails.Click
> Dim startdate As String = txtStartDate.Text
> Dim enddate As String = txtEndDate.Text
> 'startdate = Convert.ToDateTime(startdate as date)
>
> GRCallHistory.DataSource = getcallhistory("PhoneNumber",
> "StartDate", "EndDate")
> GRCallHistory.DataBind()
> End Sub
> Function getcallhistory(ByVal Phonenumber As String, _
> ByVal StartDate As DateTime, _
> ByVal enddate As DateTime) As
> System.Data.SqlClient.SqlDataReader
> Dim Connectionstring As String =
> "Server=192.168.225. 8;Database=Wiband;uid=Wiband;password=op
tiplex"
> Dim sqlConnection As System.Data.SqlClient.SqlConnection = New
> System.Data.SqlClient.SqlConnection(Connectionstring)
> Dim querystring As String = "select SUBSTRING(CAST(calldate AS
> VARCHAR(20)), 1, 11) AS CallDate, SUBSTRING(CAST(calldate AS
> VARCHAR(20)), 12, 13) AS CallTime, PhoneNumber, minutes,
> calldescription, charge from
> voipcalls.dbo.fw_fnVOIPGetCustCallHistory('" & Phonenumber & "', '" &
> StartDate & "', '" & enddate & "', 0, -1)"
> Dim sqlCommand As System.Data.SqlClient.SqlCommand = New
> System.Data.SqlClient.SqlCommand(querystring, sqlConnection)
> sqlConnection.Open()
> Dim dataReader As System.Data.SqlClient.SqlDataReader =
> sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
> Return dataReader
> End Function
>|||I hope your SQL server sits behind a DMZ (for your sake)
Anyway the problem is here
dbo.fw_fnVOIPGetCustCallHistory('" & Phonenumber & "', '" &
StartDate & "', '" & enddate & "', 0, -1)"
StartDate and enddate are not formatted well
http://sqlservercode.blogspot.com/
"lizansi@.gmail.com" wrote:
> Hello,
> I am getting the message Implicit conversion from string to date...
> Please help
> Protected Sub btnShowDetails_Click(ByVal sender As Object, ByVal e As
> EventArgs) Handles btnShowDetails.Click
> Dim startdate As String = txtStartDate.Text
> Dim enddate As String = txtEndDate.Text
> 'startdate = Convert.ToDateTime(startdate as date)
>
> GRCallHistory.DataSource = getcallhistory("PhoneNumber",
> "StartDate", "EndDate")
> GRCallHistory.DataBind()
> End Sub
> Function getcallhistory(ByVal Phonenumber As String, _
> ByVal StartDate As DateTime, _
> ByVal enddate As DateTime) As
> System.Data.SqlClient.SqlDataReader
> Dim Connectionstring As String =
> "Server=192.168.225. 8;Database=Wiband;uid=Wiband;password=op
tiplex"
> Dim sqlConnection As System.Data.SqlClient.SqlConnection = New
> System.Data.SqlClient.SqlConnection(Connectionstring)
> Dim querystring As String = "select SUBSTRING(CAST(calldate AS
> VARCHAR(20)), 1, 11) AS CallDate, SUBSTRING(CAST(calldate AS
> VARCHAR(20)), 12, 13) AS CallTime, PhoneNumber, minutes,
> calldescription, charge from
> voipcalls.dbo.fw_fnVOIPGetCustCallHistory('" & Phonenumber & "', '" &
> StartDate & "', '" & enddate & "', 0, -1)"
> Dim sqlCommand As System.Data.SqlClient.SqlCommand = New
> System.Data.SqlClient.SqlCommand(querystring, sqlConnection)
> sqlConnection.Open()
> Dim dataReader As System.Data.SqlClient.SqlDataReader =
> sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
> Return dataReader
> End Function
>|||sorry aaron, this was the first time i ever joined google
group...basically it's taking two arguments from a text box that is
startdate and enddate and pass it to the sql function. this function
has both the parameters as datetime.|||Yes, I understand how the program works. Now show some example input from
the text box. And do whatyou can to standardize this, e.g. have them pick
from a calendar and build the date for them. If you allow them free text
entry, they're going to enter all kinds of invalid crap. What date is
05/06/04? Is that May 6th 2004, or June 5th 2004, or April 6th 2005? How
about 05/13/03? And 7/5/2006? 04/13/13?
<lizansi@.gmail.com> wrote in message
news:1127401135.824457.280650@.g43g2000cwa.googlegroups.com...
> sorry aaron, this was the first time i ever joined google
> group...basically it's taking two arguments from a text box that is
> startdate and enddate and pass it to the sql function. this function
> has both the parameters as datetime.
>|||aaron, I have people input the date in format 08/01/2005...this is
mandatory|||Okay, so show an actual value that fails!
<lizansi@.gmail.com> wrote in message
news:1127403625.223935.182810@.g43g2000cwa.googlegroups.com...
> aaron, I have people input the date in format 08/01/2005...this is
> mandatory
>|||select SUBSTRING(CAST (calldate AS VARCHAR(20)), 1, 11) AS CallDate,
SUBSTRING(CAST(calldate AS VARCHAR(20)), 12, 13) AS CallTime,
PhoneNumber, minutes, calldescription, charge
from voipcalls.dbo. fw_fnVOIPGetCustCallHistory('2549462784'
,
'08/01/2005', '09/21/2005', 0, -1)|||What is fw_fnVOIPGetCustCallHistory ? Can you give enough DDL and sample
data so that we can at least try to understand WHERE the conversion is
failing?
<lizansi@.gmail.com> wrote in message
news:1127404442.502836.42140@.g14g2000cwa.googlegroups.com...
> select SUBSTRING(CAST (calldate AS VARCHAR(20)), 1, 11) AS CallDate,
> SUBSTRING(CAST(calldate AS VARCHAR(20)), 12, 13) AS CallTime,
> PhoneNumber, minutes, calldescription, charge
> from voipcalls.dbo. fw_fnVOIPGetCustCallHistory('2549462784'
,
> '08/01/2005', '09/21/2005', 0, -1)
>|||fw_fnVOIPGetCustCallHistory is a function that returns a table with the
info of phone.
here's the function:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Function dbo.fw_fnVOIPGetCustCallHistory
(
@.PhoneNumber as VarChar(80),
@.StartDate DateTime,
@.EndDate DateTime,
@.Direction Int,
@.Billable Int
)
RETURNS @.CDR TABLE
(
uniqueid VarChar(32),
calldate DateTime,
PhoneNumber VarChar(80),
CallDescription Varchar(50),
Direction VarChar(20),
Minutes Int,
Charge Money,
Billable Bit
)
AS
Begin
Declare @.CDRWork TABLE
(
uniqueid VarChar(32),
calldate DateTime,
PhoneNumber VarChar(80),
L3 VarChar(3),
S42 VarChar(2),
S43 VarChar(3),
S44 VarChar(4),
CallDescription Varchar(50),
Direction VarChar(20),
Minutes Int,
BillRate Money Default 0,
Charge Money Default 0,
BillTypeID Int Default 0, --0=Non-Billable 1=PerMinute 2=PerCall
Billable Bit,
PhoneID Int,
Updated Bit Default 0
)
Set @.EndDate=DateAdd(d,1,@.EndDate)
If @.Direction in (-1,1)
Begin
Insert into @.CDRWork (uniqueid, PhoneNumber, Minutes, CallDate,
Direction)
select
uniqueid, dst as PhoneNumber, Ceiling(Cast(billsec as Decimal)/60)
as Minutes,
calldate, 'Outgoing' as Direction
from
cdr
Where
disposition='4' and billsec>0 and
src=@.PhoneNumber and
calldate>=@.StartDate and calldate<@.EndDate
End
If @.Direction in (-1,0)
Begin
Insert into @.CDRWork (uniqueid, PhoneNumber, Minutes, CallDate,
Direction, Charge, Billable)
select
uniqueid, src as PhoneNumber, Ceiling(Cast(billsec as Decimal)/60)
as Minutes,
calldate, 'Incoming' as Direction,
0 as Charge, 0 as Billable
from
cdr
Where
disposition='4' and billsec>0 and
dst=@.PhoneNumber and src<>@.PhoneNumber and
calldate>=@.StartDate and calldate<@.EndDate
End
---
Update @.CDRWork
Set
L3 =Left(PhoneNumber,3),
S42=Substring(PhoneNumber,4,2),
S43=Substring(PhoneNumber,4,3),
S44=Substring(PhoneNumber,4,4)
Where
Left(PhoneNumber,1)<>'1'
Update @.CDRWork
Set
L3 =Substring(PhoneNumber,2,3),
S42=Substring(PhoneNumber,5,2),
S43=Substring(PhoneNumber,5,3),
S44=Substring(PhoneNumber,5,4)
Where
Left(PhoneNumber,1)='1'
---
/*intl calls using 011 prefix and 2-digit country code*/
Update a
Set
a.Updated=1,
a.PhoneID=b.PhoneID,
a.CallDescription=Upper(Rtrim(b.Display)),
a.BillTypeID=
Case
When a.Direction='Outgoing' Then 1
Else 0
End,
a.Billable=
Case
When a.Direction='Outgoing' Then b.Billable
Else 0
End
From
@.CDRWork a, fw_PhoneKey b
where
a.L3= '011' and
b.CountryCode=a.S42
---
/*intl calls using 011 prefix and 3-digit country code*/
Update a
Set
a.Updated=1,
a.PhoneID=b.PhoneID,
a.CallDescription=Upper(Rtrim(b.Display)),
a.BillTypeID=
Case
When a.Direction='Outgoing' Then 1
Else 0
End,
a.Billable=
Case
When a.Direction='Outgoing' Then b.Billable
Else 0
End
From
@.CDRWork a, fw_PhoneKey b
where
a.Updated=0 and
a.L3= '011' and
b.CountryCode=a.S43
---
/*intl calls using 011 prefix and 4-digit country code*/
Update a
Set
a.Updated=1,
a.PhoneID=b.PhoneID,
a.CallDescription=Upper(Rtrim(b.Display)),
a.BillTypeID=
Case
When a.Direction='Outgoing' Then 1
Else 0
End,
a.Billable=
Case
When a.Direction='Outgoing' Then b.Billable
Else 0
End
From
@.CDRWork a, fw_PhoneKey b
where
a.Updated=0 and
a.L3= '011' and
b.CountryCode=a.S44
---
/*normal domestic calls*/
Update a
Set
a.Updated=1,
a.PhoneID=b.PhoneID,
a.CallDescription=Upper(Rtrim(b.Display)+' '+b.State),
a.BillTypeID=
Case
When a.Direction='Outgoing' and b.Billable=1 Then 1
Else 0
End,
a.Billable=
Case
When a.Direction='Outgoing' Then b.Billable
Else 0
End
From
@.CDRWork a, fw_PhoneKey b
where
a.Updated=0 and
a.L3 = b.npa and
a.S43 = b.nxx and
b.nxx not in ('411', '555', '611')
---
/*intl calls like Canada or Caribbean using area codes*/
Update a
Set
a.Updated=1,
a.PhoneID=b.PhoneID,
a.CallDescription=Upper(Rtrim(b.Display)),
a.BillTypeID=
Case
When a.Direction='Outgoing' Then 1
Else 0
End,
a.Billable=
Case
When a.Direction='Outgoing' Then b.Billable
Else 0
End
From
@.CDRWork a, fw_PhoneKey b
where
a.Updated=0 and
A.L3= b.npa and
b.billrate > 0 and
b.nxx is null
---
/*information calls (3-digit flavor)*/
Update a
Set
a.Updated=1,
a.PhoneID=b.PhoneID,
a.CallDescription=Upper(Rtrim(b.Display)),
a.BillTypeID=
Case
When a.Direction='Outgoing' Then 2
Else 0
End,
a.Billable=
Case
When a.Direction='Outgoing' Then b.Billable
Else 0
End
From
@.CDRWork a, fw_PhoneKey b
where
a.Updated=0 and
A.L3= b.nxx and
b.nxx in ('411','555','611')
---
/*information calls (10-digit flavor)*/
Update a
Set
a.Updated=1,
a.PhoneID=b.PhoneID,
a.CallDescription=Upper(Rtrim(b.Display)),
a.BillTypeID=
Case
When a.Direction='Outgoing' Then 2
Else 0
End,
a.Billable=
Case
When a.Direction='Outgoing' Then b.Billable
Else 0
End
From
@.CDRWork a, fw_PhoneKey b
where
a.Updated=0 and
A.S43= b.nxx and
b.nxx in ('411','555', '611') and
a.L3 = b.npa
---
/*Calls not locatable in fw_PhoneKey*/
Update @.CDRWork
Set
CallDescription='UNKNOWN',
Charge=0,
BillTypeID=0,
Billable=0
Where
Updated=0
---
If @.Billable<>-1
Begin
Delete from @.CDRWork where Billable<>@.Billable
End
---
Update @.CDRWork set BillRate=dbo.fw_fnVOIPGetPhoneCallRate(PhoneID,
CallDate)
where Direction='Outgoing' and Billable=1
Update @.CDRWork set Charge=BillRate Where BillTypeID=2 --Per Call
Update @.CDRWork set Charge=Cast(Minutes as Money) * BillRate Where
BillTypeID=1 --Per Minute
---
Insert into @.CDR
(
uniqueid,
calldate,
PhoneNumber,
CallDescription,
Minutes,
Direction,
Charge,
Billable
)
Select
uniqueid,
calldate,
PhoneNumber,
CallDescription,
Minutes,
Direction,
Charge,
Billable
From @.CDRWork
Order By CallDate Desc
Return
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
When I run this sql query in query analyzer, it runs fine, gives me the
correct results. But when I call it from asp.net webpage, it faild.
