Invalid character value for cast specification: SQL Server 2000

[Krung Saengpole]

Hi,

I used SQL Server 2000 Personal Edition. I created a stored procedure
having input parameters as smallint,tinyint,char,varchar and
smalldatetime. When I executed it by Query Analyzer, it's ok. But when
I executed it by ASP code that used ADODB.command, it showed error:
Invalid character value for cast specification .

Though SQL Server debugger, it showed:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification

What did I do wrong? How do I fix this problem?
TIA
Krung

PS: One of input parameters was smalldatetime, but I casting it in ASP
as date as:

Const adDate=7
datetxt='2004/6/28'
cmd.Parameters.Append cmd.CreateParameter("CoopId",adChar,
adParamInput, 8, coopid)
cmd.Parameters.Append cmd.CreateParameter("Addr",adVarChar,
adParamInput, 100, addr)
...
cmd.Parameters.Append cmd.CreateParameter("Cdate",adDate,
adParamInput, , datetxt)
....
cmd.execute

[Erland Sommarskog]

Krung Saengpole (sa*******@lycosasia.com) writes:

I used SQL Server 2000 Personal Edition. I created a stored procedure
having input parameters as smallint,tinyint,char,varchar and
smalldatetime. When I executed it by Query Analyzer, it's ok. But when
I executed it by ASP code that used ADODB.command, it showed error:
Invalid character value for cast specification .

Though SQL Server debugger, it showed:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification

What did I do wrong? How do I fix this problem?
TIA
Krung

PS: One of input parameters was smalldatetime, but I casting it in ASP
as date as:

Const adDate=7
datetxt='2004/6/28'

ODBC want dates on the form YYYY-MM-DD, did you try that.

By the way, appearently you are using the MSDASQL provider, that is
OLE DB over ODBC. I recommend you to switch to SQLOLEDB instead. Not
that this would change this particular case, but SQLOLEDB is
recommended in general, and MSDASQL is deprecated for SQL Server
access.

The change to do is to rip out "Driver={SQL Server}" from the connect
string and bump in "Provider=SQLOLEDB". You may need to do more, but
try that for a start.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Krung Saengpole]

I tried both YYYY/MM/DD and YYYY-MM-DD format, and also used SQLOLEDB
provider. I'm not sure trouble happened with datetime point. But other
stored procedures that don't use datetime parameter works ok.

TIA
Krung

Erland Sommarskog <es****@sommarskog.se> wrote in message > > PS: One of input parameters was smalldatetime, but I casting it in ASP

as date as:

Const adDate=7
datetxt='2004/6/28'

ODBC want dates on the form YYYY-MM-DD, did you try that.

By the way, appearently you are using the MSDASQL provider, that is
OLE DB over ODBC. I recommend you to switch to SQLOLEDB instead. Not
that this would change this particular case, but SQLOLEDB is
recommended in general, and MSDASQL is deprecated for SQL Server
access.

The change to do is to rip out "Driver={SQL Server}" from the connect
string and bump in "Provider=SQLOLEDB". You may need to do more, but
try that for a start.

[Erland Sommarskog]

Krung Saengpole (sa*******@lycosasia.com) writes:

Though SQL Server debugger, it showed:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification

What did I do wrong? How do I fix this problem?
TIA
Krung

PS: One of input parameters was smalldatetime, but I casting it in ASP
as date as:

Const adDate=7
datetxt='2004/6/28'
cmd.Parameters.Append cmd.CreateParameter("CoopId",adChar,
adParamInput, 8, coopid)
cmd.Parameters.Append cmd.CreateParameter("Addr",adVarChar,
adParamInput, 100, addr)
...
cmd.Parameters.Append cmd.CreateParameter("Cdate",adDate,
adParamInput, , datetxt)
....
cmd.execute

Sorry, I missed the real error. You should probably use adDBTimeStamp (135),
adDate. adDate values are floats.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp