Varchar to Numeric

[RobA]

Sorry for posting again, I am having a bad day.

I have field in the SQL database defined as a "decimal."

I am trying to update using the following:- but I just keep getting the
Varchar to Numeric error - the other fields are nvarchar but I am not
updating them.

I tried removing the quotes from the value but then I get an unrecognised
column error.

Any help will be greatly appreicated.


Dim cmd1, strSQL1, UID, QIDVal,ScoreVal
ScoreVal = 0


UID = Session("UID")
QIDVal = Request.Form("QUID")
ScoreVal = Request.Form("Value")







Set cmd1 = CreateObject ("ADODB.Command")

strSQL1 = "UPDATE iaVals SET "
strSQL1 = strSQL1 & "eScore= 'ScoreVal'"
strSQL1 = strSQL1 & " WHERE iauID = 'UID' AND QID = 'QIDVal'"

cmd.ActiveConnection = objConn
cmd.CommandText = strSQL1

cmd.Execute

 

[Thomas A. Rowe]

Have you tried just setting the field to text or a plain number, etc.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

 

[RobA]

I will need to recall the values to use in a calculations later so I thought
it would be best to store them as such. I have tried setting the field to
numeric but I am still getting the same error.

Error converting data type varchar to numeric.
/ia/QEntered_1.asp, line 66

 

[Ronx]

Try
strSQL1 = "UPDATE iaVals SET "
strSQL1 = strSQL1 & "eScore= '" & ScoreVal & "'"
strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QID = '" & QIDVal & "'"

Note there are no single quotes around UID (numeric), but there are around
QIDVal (string), and around ScoreVal (assumed to be string)
'" == ' "
"'" == " ' " without spaces.

 

[RobA]

You are a star - I will sleep tonight. Have a great day.

I just cannot get the hang of writting these SQL strings - I can do it in
database but damned if I can write them in a webpage.

Cheers again.

Rob

 

[RobA]

Could you possibly help me with one more thing.

I pulling these values back in a recordset like this:-
strSQL1 = "SELECT QID, aScore, eScore, iScore FROM iaVals "
strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QIDGroup = '" & QIDVal
& "'"
Set aScrRst = objConn.Execute (strSQL1)

I am then trying run calculations like this

Do While not aScrRst.EOF
aTot = aScrRst("aScore")
eTot = aScrRst("eScore")
iTot = aScrRst("iScore")

SubTotal = aTot + eTot * iTot ---- But this line throws up a Type mismatch
error.

the individual values a return fine but I can't use these in calculations
like the one above.

Many thanks again.

Rob

 

[Stefan B Rusynko]

Test your variables w/ the Typename(var)
Response.write Typename(aTot)

If they really are numeric integers, force them to integers using
aTot = Cint(aScrRst("aScore"))
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Could you possibly help me with one more thing.
|
| I pulling these values back in a recordset like this:-
| strSQL1 = "SELECT QID, aScore, eScore, iScore FROM iaVals "
| strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QIDGroup = '" & QIDVal
| & "'"
| Set aScrRst = objConn.Execute (strSQL1)
|
| I am then trying run calculations like this
|
| Do While not aScrRst.EOF
| aTot = aScrRst("aScore")
| eTot = aScrRst("eScore")
| iTot = aScrRst("iScore")
|
| SubTotal = aTot + eTot * iTot ---- But this line throws up a Type mismatch
| error.
|
| the individual values a return fine but I can't use these in calculations
| like the one above.
|
| Many thanks again.
|
| Rob
|
| | > You are a star - I will sleep tonight. Have a great day.
| >
| > I just cannot get the hang of writting these SQL strings - I can do it in
| > database but damned if I can write them in a webpage.
| >
| > Cheers again.
| >
| > Rob
| > | >> Try
| >> strSQL1 = "UPDATE iaVals SET "
| >> strSQL1 = strSQL1 & "eScore= '" & ScoreVal & "'"
| >> strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QID = '" & QIDVal &
| >> "'"
| >>
| >> Note there are no single quotes around UID (numeric), but there are
| >> around
| >> QIDVal (string), and around ScoreVal (assumed to be string)
| >> '" == ' "
| >> "'" == " ' " without spaces.
| >> --
| >> Ron Symonds - Microsoft MVP (FrontPage)
| >> Reply only to group - emails will be deleted unread.
| >> FrontPage Support: http://www.frontpagemvps.com/
| >>
| >>
| >> | >>> Sorry for posting again, I am having a bad day.
| >>>
| >>> I have field in the SQL database defined as a "decimal."
| >>>
| >>> I am trying to update using the following:- but I just keep getting the
| >>> Varchar to Numeric error - the other fields are nvarchar but I am not
| >>> updating them.
| >>>
| >>> I tried removing the quotes from the value but then I get an
| >>> unrecognised
| >>> column error.
| >>>
| >>> Any help will be greatly appreicated.
| >>>
| >>>
| >>> Dim cmd1, strSQL1, UID, QIDVal,ScoreVal
| >>> ScoreVal = 0
| >>>
| >>>
| >>> UID = Session("UID")
| >>> QIDVal = Request.Form("QUID")
| >>> ScoreVal = Request.Form("Value")
| >>>
| >>>
| >>>
| >>>
| >>>
| >>>
| >>>
| >>> Set cmd1 = CreateObject ("ADODB.Command")
| >>>
| >>> strSQL1 = "UPDATE iaVals SET "
| >>> strSQL1 = strSQL1 & "eScore= 'ScoreVal'"
| >>> strSQL1 = strSQL1 & " WHERE iauID = 'UID' AND QID = 'QIDVal'"
| >>>
| >>> cmd.ActiveConnection = objConn
| >>> cmd.CommandText = strSQL1
| >>>
| >>> cmd.Execute
| >>>
| >>>
| >>
| >>
| >
| >
|
|

 

[RobA]

Stefan you are a star everything is working fine now.


Rob

Test your variables w/ the Typename(var)
Response.write Typename(aTot)

If they really are numeric integers, force them to integers using
aTot = Cint(aScrRst("aScore"))
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Could you possibly help me with one more thing.
|
| I pulling these values back in a recordset like this:-
| strSQL1 = "SELECT QID, aScore, eScore, iScore FROM iaVals "
| strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QIDGroup = '" &
QIDVal
| & "'"
| Set aScrRst = objConn.Execute (strSQL1)
|
| I am then trying run calculations like this
|
| Do While not aScrRst.EOF
| aTot = aScrRst("aScore")
| eTot = aScrRst("eScore")
| iTot = aScrRst("iScore")
|
| SubTotal = aTot + eTot * iTot ---- But this line throws up a Type
mismatch
| error.
|
| the individual values a return fine but I can't use these in
calculations
| like the one above.
|
| Many thanks again.
|
| Rob
|
| | > You are a star - I will sleep tonight. Have a great day.
| >
| > I just cannot get the hang of writting these SQL strings - I can do it
in
| > database but damned if I can write them in a webpage.
| >
| > Cheers again.
| >
| > Rob
| > | >> Try
| >> strSQL1 = "UPDATE iaVals SET "
| >> strSQL1 = strSQL1 & "eScore= '" & ScoreVal & "'"
| >> strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QID = '" & QIDVal
&
| >> "'"
| >>
| >> Note there are no single quotes around UID (numeric), but there are
| >> around
| >> QIDVal (string), and around ScoreVal (assumed to be string)
| >> '" == ' "
| >> "'" == " ' " without spaces.
| >> --
| >> Ron Symonds - Microsoft MVP (FrontPage)
| >> Reply only to group - emails will be deleted unread.
| >> FrontPage Support: http://www.frontpagemvps.com/
| >>
| >>
| >> | >>> Sorry for posting again, I am having a bad day.
| >>>
| >>> I have field in the SQL database defined as a "decimal."
| >>>
| >>> I am trying to update using the following:- but I just keep getting
the
| >>> Varchar to Numeric error - the other fields are nvarchar but I am
not
| >>> updating them.
| >>>
| >>> I tried removing the quotes from the value but then I get an
| >>> unrecognised
| >>> column error.
| >>>
| >>> Any help will be greatly appreicated.
| >>>
| >>>
| >>> Dim cmd1, strSQL1, UID, QIDVal,ScoreVal
| >>> ScoreVal = 0
| >>>
| >>>
| >>> UID = Session("UID")
| >>> QIDVal = Request.Form("QUID")
| >>> ScoreVal = Request.Form("Value")
| >>>
| >>>
| >>>
| >>>
| >>>
| >>>
| >>>
| >>> Set cmd1 = CreateObject ("ADODB.Command")
| >>>
| >>> strSQL1 = "UPDATE iaVals SET "
| >>> strSQL1 = strSQL1 & "eScore= 'ScoreVal'"
| >>> strSQL1 = strSQL1 & " WHERE iauID = 'UID' AND QID = 'QIDVal'"
| >>>
| >>> cmd.ActiveConnection = objConn
| >>> cmd.CommandText = strSQL1
| >>>
| >>> cmd.Execute
| >>>
| >>>
| >>
| >>
| >
| >
|
|

 

[Stefan B Rusynko]

Just remember Cint will fail on null or empty values
- always check for valid data before using it

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Stefan you are a star everything is working fine now.
|
|
| Rob
|
|
| | > Test your variables w/ the Typename(var)
| > Response.write Typename(aTot)
| >
| > If they really are numeric integers, force them to integers using
| > aTot = Cint(aScrRst("aScore"))
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > To find the best Newsgroup for FrontPage support see:
| > http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
| > _____________________________________________
| >
| >
| > | > | Could you possibly help me with one more thing.
| > |
| > | I pulling these values back in a recordset like this:-
| > | strSQL1 = "SELECT QID, aScore, eScore, iScore FROM iaVals "
| > | strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QIDGroup = '" &
| > QIDVal
| > | & "'"
| > | Set aScrRst = objConn.Execute (strSQL1)
| > |
| > | I am then trying run calculations like this
| > |
| > | Do While not aScrRst.EOF
| > | aTot = aScrRst("aScore")
| > | eTot = aScrRst("eScore")
| > | iTot = aScrRst("iScore")
| > |
| > | SubTotal = aTot + eTot * iTot ---- But this line throws up a Type
| > mismatch
| > | error.
| > |
| > | the individual values a return fine but I can't use these in
| > calculations
| > | like the one above.
| > |
| > | Many thanks again.
| > |
| > | Rob
| > |
| > | | > | > You are a star - I will sleep tonight. Have a great day.
| > | >
| > | > I just cannot get the hang of writting these SQL strings - I can do it
| > in
| > | > database but damned if I can write them in a webpage.
| > | >
| > | > Cheers again.
| > | >
| > | > Rob
| > | > | > | >> Try
| > | >> strSQL1 = "UPDATE iaVals SET "
| > | >> strSQL1 = strSQL1 & "eScore= '" & ScoreVal & "'"
| > | >> strSQL1 = strSQL1 & " WHERE iauID = " & UID & " AND QID = '" & QIDVal
| > &
| > | >> "'"
| > | >>
| > | >> Note there are no single quotes around UID (numeric), but there are
| > | >> around
| > | >> QIDVal (string), and around ScoreVal (assumed to be string)
| > | >> '" == ' "
| > | >> "'" == " ' " without spaces.
| > | >> --
| > | >> Ron Symonds - Microsoft MVP (FrontPage)
| > | >> Reply only to group - emails will be deleted unread.
| > | >> FrontPage Support: http://www.frontpagemvps.com/
| > | >>
| > | >>
| > | >> | > | >>> Sorry for posting again, I am having a bad day.
| > | >>>
| > | >>> I have field in the SQL database defined as a "decimal."
| > | >>>
| > | >>> I am trying to update using the following:- but I just keep getting
| > the
| > | >>> Varchar to Numeric error - the other fields are nvarchar but I am
| > not
| > | >>> updating them.
| > | >>>
| > | >>> I tried removing the quotes from the value but then I get an
| > | >>> unrecognised
| > | >>> column error.
| > | >>>
| > | >>> Any help will be greatly appreicated.
| > | >>>
| > | >>>
| > | >>> Dim cmd1, strSQL1, UID, QIDVal,ScoreVal
| > | >>> ScoreVal = 0
| > | >>>
| > | >>>
| > | >>> UID = Session("UID")
| > | >>> QIDVal = Request.Form("QUID")
| > | >>> ScoreVal = Request.Form("Value")
| > | >>>
| > | >>>
| > | >>>
| > | >>>
| > | >>>
| > | >>>
| > | >>>
| > | >>> Set cmd1 = CreateObject ("ADODB.Command")
| > | >>>
| > | >>> strSQL1 = "UPDATE iaVals SET "
| > | >>> strSQL1 = strSQL1 & "eScore= 'ScoreVal'"
| > | >>> strSQL1 = strSQL1 & " WHERE iauID = 'UID' AND QID = 'QIDVal'"
| > | >>>
| > | >>> cmd.ActiveConnection = objConn
| > | >>> cmd.CommandText = strSQL1
| > | >>>
| > | >>> cmd.Execute
| > | >>>
| > | >>>
| > | >>
| > | >>
| > | >
| > | >
| > |
| > |
| >
| >
|
|