When trying to insert NUMERIC data into a SQL7 database, I get the following
error. Does NOT occur when datatype is Integer. Any thoughts?
I am using an ASP form (dataadd.asp) to post the values to dataaddconf.asp .

'***********************************
'Error:
'***********************************
Microsoft OLE DB Provider for SQL Server error '80040e21'
Invalid character value for cast specification.

/dataaddconf.asp, line 54


'***********************************
'ASP code in dataaddconf.asp:
'***********************************

<%@ Language=VBScript %>
<!-- #INCLUDE File="adovbs.inc" -->
<%
DIM Data_Metric
DIM Data_Month
DIM Data_Year
DIM Data_Actual
DIM Data_Plan
DIM Data_Comments
DIM Data_UserName
DIM DateSubmitted
DIM TimeSubmitted

Data_Metric = Request.Form("Data_Metric")
Data_Month = Request.Form("Data_Month")
Data_Year = Request.Form("Data_Year")
Data_Actual = Request.Form("Data_Actual")
Data_Plan = Request.Form("Data_Plan")
Data_Comments = Request.Form("Data_Comments")
Data_UserName = Request.Form("Data_UserName")
DateSubmitted = Request.Form("DateSubmitted")
TimeSubmitted = Request.Form("TimeSubmitted")

Set Command = Server.CreateObject( "adodb.Command" )
Set Command.ActiveConnection = Con
Command.CommandType = adCmdStoredProc
Command.CommandText = "sp_dataadd"
Command.Parameters.Append
Command.CreateParameter("ReturnCode",adInteger,adParamReturnValue)
Command.Parameters.Append
Command.CreateParameter("datametric",adVarchar,adParamInput,50)
Command.Parameters.Append
Command.CreateParameter("monthid",adInteger,adParamInput)
Command.Parameters.Append
Command.CreateParameter("yearid",adInteger,adParamInput)
Command.Parameters.Append
Command.CreateParameter("actual",adNumeric,adParamInput)
Command.Parameters.Append
Command.CreateParameter("plan",adNumeric,adParamInput)
Command.Parameters.Append
Command.CreateParameter("comments",adVarchar,adParamInput,500)
Command.Parameters.Append
Command.CreateParameter("username",adVarchar,adParamInput,50)
Command.Parameters.Append
Command.CreateParameter("datenow",adDate,adParamInput,10)
Command.Parameters.Append
Command.CreateParameter("timenow",adDate,adParamInput,10)

Command.Parameters("datametric") = Data_Metric
Command.Parameters("monthid") = Data_Month
Command.Parameters("yearid") = Data_Year
Command.Parameters("actual") = Data_Actual
Command.Parameters("plan") = Data_Plan
Command.Parameters("comments") = Data_Comments
Command.Parameters("username") = Data_UserName
Command.Parameters("datenow") = DateSubmitted
Command.Parameters("timenow") = TimeSubmitted

Set RS = Server.CreateObject( "ADODB.Recordset" )
Command.Execute     'THIS IS WHERE THE CODE IS ERRING-Line 54
%>

'***********************************
'Stored Procedure:
'***********************************

CREATE proc sp_dataadd
(

 @datametric varchar (50),
 @monthid int,
 @yearid int,
 @actual numeric,
 @plan numeric,
 @comments varchar (500),
 @username varchar (50),
 @datenow datetime,
 @timenow datetime

)
AS
INSERT Data (Data_Metric, Data_Month, Data_Year,Data_Actual2,Data_Plan2,
Data_Comments,Data_UserName,DateSubmitted,TimeSubmitted)
 Values
(@datametric,@monthid,@yearid,@actual,@plan,@comments,@username,@datenow,@ti
menow)
Return @@ERROR

'***********************************
'Database:
'***********************************
The Columns Data_Actual2 & Data_Plan2 are Numeric with a Precision of 18 &
Scale of 2



Jared M. Nunes


Lighthouse Infomatics
www.rocklight.net

750 Bridge Street

Yuba City CA 95991
xxx-xxx-xxxx  x2511

Try converting all of your values to the proper type before you try to 
INSERT.  if the SQL7 expects double precision, then use cDBL(yourValue), 
before you execute the query.  ASP makes everything a text type but tries 
to determine what kind of data it is.  sometimes ASP will send the right 
type(as with integers), but dont trust it, it can make mistakes and often 
does.  if converting the types doesnt work, let us know and ill try to 
reproduce the error.  i would convert ALL of the data before i tried to 
execute, just to be sure.


> When trying to insert NUMERIC data into a SQL7 database, I get the 
following
error. Does NOT occur when datatype is Integer. Any thoughts?
I am using an ASP form (dataadd.asp) to post the values to 
dataaddconf.asp .

'***********************************
'Error:
'***********************************
Microsoft OLE DB Provider for SQL Server error '80040e21'
Invalid character value for cast specification.

/dataaddconf.asp, line 54


'***********************************
'ASP code in dataaddconf.asp:
'***********************************

<%@ Language=VBScript %>
<!-- #INCLUDE File="adovbs.inc" -->
<%
DIM Data_Metric
DIM Data_Month
DIM Data_Year
DIM Data_Actual
DIM Data_Plan
DIM Data_Comments
DIM Data_UserName
DIM DateSubmitted
DIM TimeSubmitted

Data_Metric = Request.Form("Data_Metric")
Data_Month = Request.Form("Data_Month")
Data_Year = Request.Form("Data_Year")
Data_Actual = Request.Form("Data_Actual")
Data_Plan = Request.Form("Data_Plan")
Data_Comments = Request.Form("Data_Comments")
Data_UserName = Request.Form("Data_UserName")
DateSubmitted = Request.Form("DateSubmitted")
TimeSubmitted = Request.Form("TimeSubmitted")

Set Command = Server.CreateObject( "adodb.Command" )
Set Command.ActiveConnection = Con
Command.CommandType = adCmdStoredProc
Command.CommandText = "sp_dataadd"
Command.Parameters.Append
Command.CreateParameter("ReturnCode",adInteger,adParamReturnValue)
Command.Parameters.Append
Command.CreateParameter("datametric",adVarchar,adParamInput,50)
Command.Parameters.Append
Command.CreateParameter("monthid",adInteger,adParamInput)
Command.Parameters.Append
Command.CreateParameter("yearid",adInteger,adParamInput)
Command.Parameters.Append
Command.CreateParameter("actual",adNumeric,adParamInput)
Command.Parameters.Append
Command.CreateParameter("plan",adNumeric,adParamInput)
Command.Parameters.Append
Command.CreateParameter("comments",adVarchar,adParamInput,500)
Command.Parameters.Append
Command.CreateParameter("username",adVarchar,adParamInput,50)
Command.Parameters.Append
Command.CreateParameter("datenow",adDate,adParamInput,10)
Command.Parameters.Append
Command.CreateParameter("timenow",adDate,adParamInput,10)

Command.Parameters("datametric") = Data_Metric
Command.Parameters("monthid") = Data_Month
Command.Parameters("yearid") = Data_Year
Command.Parameters("actual") = Data_Actual
Command.Parameters("plan") = Data_Plan
Command.Parameters("comments") = Data_Comments
Command.Parameters("username") = Data_UserName
Command.Parameters("datenow") = DateSubmitted
Command.Parameters("timenow") = TimeSubmitted

Set RS = Server.CreateObject( "ADODB.Recordset" )
Command.Execute     'THIS IS WHERE THE CODE IS ERRING-Line 54
%>

'***********************************
'Stored Procedure:
'***********************************

CREATE proc sp_dataadd
(

 @datametric varchar (50),
 @monthid int,
 @yearid int,
 @actual numeric,
 @plan numeric,
 @comments varchar (500),
 @username varchar (50),
 @datenow datetime,
 @timenow datetime

)
AS
INSERT Data (Data_Metric, Data_Month, Data_Year,Data_Actual2,Data_Plan2,
Data_Comments,Data_UserName,DateSubmitted,TimeSubmitted)
 Values
(@datametric,@monthid,@yearid,@actual,@plan,@comments,@username,@datenow,@t
i
menow)
Return @@ERROR

'***********************************
'Database:
'***********************************
The Columns Data_Actual2 & Data_Plan2 are Numeric with a Precision of 18 &
Scale of 2



Jared M. Nunes


Lighthouse Infomatics
www.rocklight.net

750 Bridge Street

Yuba City CA 95991
xxx-xxx-xxxx  x2511

I tried using 
Command.Parameters("actual") = CDbl(Data_Actual)
Command.Parameters("plan") = CDbl(Data_Plan)

But that didn't work either. I opted as a temporary work around to save 
the values as varChar and then use a stored procedure to move the data 
into a column of numeric datatype.

Did I miss something?

Jared Nunes

> Try converting all of your values to the proper type before you try to 
I> NSERT.  if the SQL7 expects double precision, then use cDBL(yourValue), 
b> efore you execute the query.  ASP makes everything a text type but 
tries 
t> o determine what kind of data it is.  sometimes ASP will send the right 
t> ype(as with integers), but dont trust it, it can make mistakes and 
often 
d> oes.  if converting the types doesnt work, let us know and ill try to 
r> eproduce the error.  i would convert ALL of the data before i tried to 
e> xecute, just to be sure.

> 
>>  When trying to insert NUMERIC data into a SQL7 database, I get the 
f> ollowing
e> rror. Does NOT occur when datatype is Integer. Any thoughts?
I>  am using an ASP form (dataadd.asp) to post the values to 
d> ataaddconf.asp .

> '***********************************
'> Error:
'> ***********************************
M> icrosoft OLE DB Provider for SQL Server error '80040e21'
I> nvalid character value for cast specification.

> /dataaddconf.asp, line 54

> 
'> ***********************************
'> ASP code in dataaddconf.asp:
'> ***********************************

> <%@ Language=VBScript %>
<> !-- #INCLUDE File="adovbs.inc" -->
<> %
D> IM Data_Metric
D> IM Data_Month
D> IM Data_Year
D> IM Data_Actual
D> IM Data_Plan
D> IM Data_Comments
D> IM Data_UserName
D> IM DateSubmitted
D> IM TimeSubmitted

> Data_Metric = Request.Form("Data_Metric")
D> ata_Month = Request.Form("Data_Month")
D> ata_Year = Request.Form("Data_Year")
D> ata_Actual = Request.Form("Data_Actual")
D> ata_Plan = Request.Form("Data_Plan")
D> ata_Comments = Request.Form("Data_Comments")
D> ata_UserName = Request.Form("Data_UserName")
D> ateSubmitted = Request.Form("DateSubmitted")
T> imeSubmitted = Request.Form("TimeSubmitted")

> Set Command = Server.CreateObject( "adodb.Command" )
S> et Command.ActiveConnection = Con
C> ommand.CommandType = adCmdStoredProc
C> ommand.CommandText = "sp_dataadd"
C> ommand.Parameters.Append
C> ommand.CreateParameter("ReturnCode",adInteger,adParamReturnValue)
C> ommand.Parameters.Append
C> ommand.CreateParameter("datametric",adVarchar,adParamInput,50)
C> ommand.Parameters.Append
C> ommand.CreateParameter("monthid",adInteger,adParamInput)
C> ommand.Parameters.Append
C> ommand.CreateParameter("yearid",adInteger,adParamInput)
C> ommand.Parameters.Append
C> ommand.CreateParameter("actual",adNumeric,adParamInput)
C> ommand.Parameters.Append
C> ommand.CreateParameter("plan",adNumeric,adParamInput)
C> ommand.Parameters.Append
C> ommand.CreateParameter("comments",adVarchar,adParamInput,500)
C> ommand.Parameters.Append
C> ommand.CreateParameter("username",adVarchar,adParamInput,50)
C> ommand.Parameters.Append
C> ommand.CreateParameter("datenow",adDate,adParamInput,10)
C> ommand.Parameters.Append
C> ommand.CreateParameter("timenow",adDate,adParamInput,10)

> Command.Parameters("datametric") = Data_Metric
C> ommand.Parameters("monthid") = Data_Month
C> ommand.Parameters("yearid") = Data_Year
C> ommand.Parameters("actual") = Data_Actual
C> ommand.Parameters("plan") = Data_Plan
C> ommand.Parameters("comments") = Data_Comments
C> ommand.Parameters("username") = Data_UserName
C> ommand.Parameters("datenow") = DateSubmitted
C> ommand.Parameters("timenow") = TimeSubmitted

> Set RS = Server.CreateObject( "ADODB.Recordset" )
C> ommand.Execute     'THIS IS WHERE THE CODE IS ERRING-Line 54
%> >

> '***********************************
'> Stored Procedure:
'> ***********************************

> CREATE proc sp_dataadd
(> 

>  @datametric varchar (50),
 > @monthid int,
 > @yearid int,
 > @actual numeric,
 > @plan numeric,
 > @comments varchar (500),
 > @username varchar (50),
 > @datenow datetime,
 > @timenow datetime

> )
A> S
I> NSERT Data (Data_Metric, Data_Month, Data_Year,Data_Actual2,Data_Plan2,
D> ata_Comments,Data_UserName,DateSubmitted,TimeSubmitted)
 > Values
(> 
@datametric,@monthid,@yearid,@actual,@plan,@comments,@username,@datenow,@t
i> 
m> enow)
R> eturn @@ERROR

> '***********************************
'> Database:
'> ***********************************
T> he Columns Data_Actual2 & Data_Plan2 are Numeric with a Precision of 18 
&
S> cale of 2

> 

> Jared M. Nunes

> 
L> ighthouse Infomatics
w> ww.rocklight.net

> 750 Bridge Street

> Yuba City CA 95991
5> 30-790-2500 x2511

> 

ok, you may already know this, but "CAST", of course is a conversion 
function and in T-SQL it is the same as "CONVERT".  somewhere your 
procedure is trying to convert a value but it cant because the data is not 
of the right type(like if i enter "hello World" and try to insert it into 
a date field).  

I could not see any CAST functions in your code(i could have missed it), 
which means SQL7 is using implicit or "automatic" conversions.  there is a 
chart in the SQL7 Books Online that outlines which implicit conversions 
are allowed.  check that to make sure you are not trying one that is not 
allowed.  search for "CAST and CONVERT (T-SQL)" in the books online.  

dont forget to double check what data you are sending.  do a 
response.write after each request.form to see what you are sending and 
check that against the chart.

like i said, you may have checked all of this, if so, i am lost too!

hope this helps,

ron





> I tried using 
C> ommand.Parameters("actual") = CDbl(Data_Actual)
C> ommand.Parameters("plan") = CDbl(Data_Plan)

> But that didn't work either. I opted as a temporary work around to save 
t> he values as varChar and then use a stored procedure to move the data 
i> nto a column of numeric datatype.

> Did I miss something?

> Jared Nunes

> > Try converting all of your values to the proper type before you try to 
I> > NSERT.  if the SQL7 expects double precision, then use cDBL
(yourValue), 
b> > efore you execute the query.  ASP makes everything a text type but 
t> ries 
t> > o determine what kind of data it is.  sometimes ASP will send the 
right 
t> > ype(as with integers), but dont trust it, it can make mistakes and 
o> ften 
d> > oes.  if converting the types doesnt work, let us know and ill try to 
r> > eproduce the error.  i would convert ALL of the data before i tried 
to 
e> > xecute, just to be sure.

> > 
>> >  When trying to insert NUMERIC data into a SQL7 database, I get the 
f> > ollowing
e> > rror. Does NOT occur when datatype is Integer. Any thoughts?
I> >  am using an ASP form (dataadd.asp) to post the values to 
d> > ataaddconf.asp .

> > '***********************************
'> > Error:
'> > ***********************************
M> > icrosoft OLE DB Provider for SQL Server error '80040e21'
I> > nvalid character value for cast specification.

> > /dataaddconf.asp, line 54

> > 
'> > ***********************************
'> > ASP code in dataaddconf.asp:
'> > ***********************************

> > <%@ Language=VBScript %>
<> > !-- #INCLUDE File="adovbs.inc" -->
<> > %
D> > IM Data_Metric
D> > IM Data_Month
D> > IM Data_Year
D> > IM Data_Actual
D> > IM Data_Plan
D> > IM Data_Comments
D> > IM Data_UserName
D> > IM DateSubmitted
D> > IM TimeSubmitted

> > Data_Metric = Request.Form("Data_Metric")
D> > ata_Month = Request.Form("Data_Month")
D> > ata_Year = Request.Form("Data_Year")
D> > ata_Actual = Request.Form("Data_Actual")
D> > ata_Plan = Request.Form("Data_Plan")
D> > ata_Comments = Request.Form("Data_Comments")
D> > ata_UserName = Request.Form("Data_UserName")
D> > ateSubmitted = Request.Form("DateSubmitted")
T> > imeSubmitted = Request.Form("TimeSubmitted")

> > Set Command = Server.CreateObject( "adodb.Command" )
S> > et Command.ActiveConnection = Con
C> > ommand.CommandType = adCmdStoredProc
C> > ommand.CommandText = "sp_dataadd"
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("ReturnCode",adInteger,adParamReturnValue)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("datametric",adVarchar,adParamInput,50)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("monthid",adInteger,adParamInput)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("yearid",adInteger,adParamInput)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("actual",adNumeric,adParamInput)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("plan",adNumeric,adParamInput)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("comments",adVarchar,adParamInput,500)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("username",adVarchar,adParamInput,50)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("datenow",adDate,adParamInput,10)
C> > ommand.Parameters.Append
C> > ommand.CreateParameter("timenow",adDate,adParamInput,10)

> > Command.Parameters("datametric") = Data_Metric
C> > ommand.Parameters("monthid") = Data_Month
C> > ommand.Parameters("yearid") = Data_Year
C> > ommand.Parameters("actual") = Data_Actual
C> > ommand.Parameters("plan") = Data_Plan
C> > ommand.Parameters("comments") = Data_Comments
C> > ommand.Parameters("username") = Data_UserName
C> > ommand.Parameters("datenow") = DateSubmitted
C> > ommand.Parameters("timenow") = TimeSubmitted

> > Set RS = Server.CreateObject( "ADODB.Recordset" )
C> > ommand.Execute     'THIS IS WHERE THE CODE IS ERRING-Line 54
%> > >

> > '***********************************
'> > Stored Procedure:
'> > ***********************************

> > CREATE proc sp_dataadd
(> > 

> >  @datametric varchar (50),
 > > @monthid int,
 > > @yearid int,
 > > @actual numeric,
 > > @plan numeric,
 > > @comments varchar (500),
 > > @username varchar (50),
 > > @datenow datetime,
 > > @timenow datetime

> > )
A> > S
I> > NSERT Data (Data_Metric, Data_Month, 
Data_Year,Data_Actual2,Data_Plan2,
D> > ata_Comments,Data_UserName,DateSubmitted,TimeSubmitted)
 > > Values
(> > 
@> 
datametric,@monthid,@yearid,@actual,@plan,@comments,@username,@datenow,@t
i> > 
m> > enow)
R> > eturn @@ERROR

> > '***********************************
'> > Database:
'> > ***********************************
T> > he Columns Data_Actual2 & Data_Plan2 are Numeric with a Precision of 
18 
&> 
S> > cale of 2

> > 

> > Jared M. Nunes

> > 
L> > ighthouse Infomatics
w> > ww.rocklight.net

> > 750 Bridge Street

> > Yuba City CA 95991
5> > 30-790-2500 x2511

> >