Solved

# Trim, Convert String to Numeric

Posted on 2002-03-05
462 Views
Hi.

I use this to get a value from an inputbox but recive
Error converting data type varchar to numeric message.

InternalTime = replace(trim(Request.Form("InsertInternalTime")),"'","''")

How do I get it to be Numeric?

(InternalTime is not Date, it is Decimal values.)

Rgrds
0
Question by:sporfex
• 11
• 8
• 5
• +4

LVL 11

Expert Comment

ID: 6841379
if isnumeric(internaltime) then
internaltime = cdec(internaltime)
else
'it isn't numeric
end if
0

LVL 46

Expert Comment

ID: 6841402
This will verify that you have a text string and then convert your text to numeric:

if VarType(InternalTime) = 8 then
InternalTime  = CDbl(InternalTime)
end if

Fritz the Blank

0

Author Comment

ID: 6841423
Fritz,

Type mismatch: 'CDbl'

thunderchicken, the same...

My Insert row look like this, maby tha fault is there.

remoteconn.execute "INSERT OpRegister (ProductId, OpLocationId, InternalTime, InternalYield, CustomerTime, CustomerYield, EditedDate) VALUES ('" & ProductId & "','" & OpLocationId & "','" & InternalTime & "','" & InternalYield & "','" & CustomerTime & "','" & CustomerYield & "','" & EditedDate & "')"

ProductId,OpLocationId is Numeric
The rest of them except from EditedDate is Decimal in the SQL 7 table. EditedTime is Date

0

LVL 2

Expert Comment

ID: 6841511
i am bit confused,

InternalTime = replace(trim(Request.Form("InsertInternalTime")),"'","''")

Here you said, "InsertInternalTime" is decimal values.  Then why you are using replace method which used to convert data to string datatype.

In query you have done the same mistake, just remove the apostrophe from the field value of "InternalTime"

old code
-------------
"','" & InternalTime & "','"

replace with
-------------
"'," & InternalTime & ",'"

One more thing, if you are using Cint, Cdbl that should not be blank, so be careful on using these functions.

0

Author Comment

ID: 6841526
manihopever ,

I'm confused also...I replaced the line with:

InternalTime = trim(Request.Form("InsertInternalTime"))

If I use "'," & InternalTime & ",'" as you said I recive:

Line 1: Incorrect syntax near ','.

?
0

LVL 29

Expert Comment

ID: 6841583
Do a

Response.Write "--" & InternalTime & "--"

to really check what you have there. It sound like you have a non-numeric value after all.

As you are using it in a string expression, the data type of the variable does not really matter. The value gets converted to a string anyway.

However, if it is supposed to be a numeric value, I always use CLng or CDbl to make sure that it is. If I don't get the correct data in, it's better that it stops right away, as the rest of the code won't work with wrong data anyway.
0

Author Comment

ID: 6841610
GreenGhost,

The result is ----

No value, strange...
I post the data to the same page as I enter the input the data on. Shall I post it to another asp file instead?

What could be wrong?
0

LVL 46

Expert Comment

ID: 6841635
try this:

strSQL ="INSERT OpRegister (ProductId, OpLocationId, InternalTime, InternalYield, CustomerTime,
CustomerYield, EditedDate) VALUES ('" & ProductId & "','" & OpLocationId & "','" & InternalTime & "','"
& InternalYield & "','" & CustomerTime & "','" & CustomerYield & "','" & EditedDate & "')"

Response.write(strSQL)

That should help to isolate the problem.

Fritz the Blank
0

Author Comment

ID: 6841641
fritz,

Result look like
INSERT OpRegister (ProductId, OpLocationId, InternalTime, InternalYield, CustomerTime, CustomerYield, EditedDate) VALUES ('262694561','6','','55','5','66','3/5/2002 3:52:30 PM')

Missing value. Notice! I dont trim the other values yet.
I have used (below) for that until I have solved InternalTime. Thats why I have values for the other variables. I excpect the same problem if I add trim...to them.

ProductId = 262694561
OpLocationId = 6
InternalTime = trim(Request.Form("InsertInternalTime"))
InternalYield = 55
CustomerTime = 5
CustomerYield = 66
EditedDate = Now()

0

LVL 46

Expert Comment

ID: 6841915
I think that you are missing an "into" between insert and OpRegister.

Fritz the Blank
0

LVL 46

Expert Comment

ID: 6841935
So, I think that you need to validate the values coming from the form. What happens when you say:

Response.write(Request.Form("InsertInternalTime"))

Fritz the Blank
0

LVL 46

Expert Comment

ID: 6841980
Just in case... Are you sure that you are using the correct field name in the Request object?

Fritz the Blank
0

Author Comment

ID: 6843634
Fritz,

When I use Response.write "**" & (Request.Form("InsertInternalTime")) & "**"

..I recive **** The default valu in the box is 99.

I post all the code now.

<%
' Created two variables so I dont need to use another form before this one at this stage
' The values shall later be selected in combo boxes on another page
strProductName ="262694561"
strQuerySelection ="Qry1"

strGroup= Cstr(session("iDepartment"))
strName =Cstr(session("iName"))

Query ="Select * FROM OpLocation WHERE QuerySelection = '" &strQuerySelection& "' ORDER BY SortOrder "
DBconn.Open
RS.Open Query, DBconn, 3, 3

'InsertInternalTime = 0

ProductId = 262694561 'replace(trim(Request.Form("InsertProductId")),"'","''")
OpLocationId = 6 ' replace(trim(Request.Form("InsertOpLocationId")),"'","''")

InternalTime = 66' trim(Request.Form("InsertInternalTime"))

InternalYield = 55 'replace(trim(Request.Form("InsertInternalYield")),"'","''")
CustomerTime = 5 'replace(trim(request.Form("InsertCustomerTime")),"'","''")
CustomerYield = 66 'replace(trim(Request.Form("InsertCustomerYield")),"'","''")
EditedDate = Now() 'replace(trim(Request.Form("InsertEditedDate")),"'","''")

'if isnumeric(InternalTime) then
' InternalTime = cdec(InternalTime)
'else
'it isn't numeric
'end if

'if VarType(InternalTime) = 8 then
'   InternalTime  = CDbl(InternalTime)
'end if

Response.write "**" & (Request.Form("InsertInternalTime")) & "**"
Response.Write "--" & InternalTime & "--"

strSQL="INSERT OpRegister (ProductId, OpLocationId, InternalTime, InternalYield, CustomerTime, CustomerYield, EditedDate) VALUES ('" & ProductId & "','" & OpLocationId & "','" & InternalTime & "','" & InternalYield & "','" & CustomerTime & "','" & CustomerYield & "','" & EditedDate & "')"
Response.write(strSQL)
'remoteconn.execute "INSERT OpRegister (ProductId, OpLocationId, InternalTime, InternalYield, CustomerTime, CustomerYield, EditedDate) VALUES ('" & ProductId & "','" & OpLocationId & "'," & InternalTime & " ,'" & InternalYield & "','" & CustomerTime & "','" & CustomerYield & "','" & EditedDate & "')"
'End if

%>

<html>
<title>OpRegister</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<body bgcolor="#FFFFFF" text="#000000">
<table centerwidth="34%" border="0" width="35%">
<tr>
<td width="37%"> <b>ProductId</b><br>
<% Response.write strProductName %>
</td>
<td width="63%"><b>OpLocation</b><br>
<% Response.write RS("OpLocationName") %>
</td>
</tr>
<tr>
<td><b>InternalTime</b><br>
<input type="text" name="InsertInternalTime" value="99">
</td>
<td><b>InternalYield</b><br>
<input type="text" name="InsertInternalYield">
</td>
</tr>
<tr>
<td><b>CustomerTime</b><br>
<input type="text" name="InsertCustomerTime">
</td>
<td><b>CustomerYield</b><br>
<input type="text" name="InsertCustomerYield">
</td>
</tr>
<tr>
<td height="53" colspan="2">

<input type="submit" name="Submit" value="Submit">
<br>
</td>
</tr>
</table>
<p>&nbsp;</p>
<p>&nbsp;</p>
</form>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><b> </b> </p>
</body>
</html>
0

Author Comment

ID: 6843636
and the code is on the page operationsregister.asp (the same page that I post the values to.
0

LVL 29

Expert Comment

ID: 6843808
When I take your code and comment out the database calls (so that it doesn't complain that I don't have that database), and use this:

InternalTime = Request.Form("InsertInternalTime")

When I post the form, it sure shows **99**--99--.
0

Author Comment

ID: 6843974
GreenGhost, true.

The problem now is that asp doesn't know cdec, it is VisualBasic only if I understand another expert correct.

So what should I replace it with??

if isnumeric(InternalTime) then
InternalTime = cdec(InternalTime)
else
'it isn't numeric
end if
0

LVL 46

Expert Comment

ID: 6844074
Doesn't this work?

if VarType(InternalTime) = 8 then
InternalTime  = CDbl(InternalTime)
end if
0

Author Comment

ID: 6844101
Fritz,

No.

Type mismatch: 'CDbl'

This line:

InternalTime  = CDbl(InternalTime)
0

LVL 46

Expert Comment

ID: 6844250
Okay,

what happens if you say:

response.write(VarType(InternalTime))

Fritz the Blank
0

Author Comment

ID: 6844302
VarType is = 8

0

LVL 29

Expert Comment

ID: 6844990
As you post to the same page, you have to check if there is any data posted to it or not.

If IsEmpty(Request.Form("InsertInternalTime")) Then
' first time
Else
InternalTime = Request.Form("InsertInternalTime")
If IsNumeric(InternalTime) Then
InternalTime=CLng(InternalTime)
Else
' not numeric value!
' handle this some way...
End If
End If
0

LVL 2

Expert Comment

ID: 6846390
----------------------
remoteconn.execute "INSERT OpRegister (ProductId, OpLocationId, InternalTime, InternalYield, CustomerTime,
CustomerYield, EditedDate) VALUES ('" & ProductId & "','" & OpLocationId & "','" & InternalTime & "','"
& InternalYield & "','" & CustomerTime & "','" & CustomerYield & "','" & EditedDate & "')"

ProductId,OpLocationId is Numeric
The rest of them except from EditedDate is Decimal in the SQL 7 table. EditedTime is Date

my comment
------------
In the above comment you said Productid, Oplocationid is numeric then how you can use apostrophe in before that values.  Dont you think it as an error.  This also applies for all decimal fields

Could you please tell me what is datatype for all of your fields especially "Internaltime" field.
0

Author Comment

ID: 6846508
manihopever ,

Ok, this is the SQL 7 setup.
ProductId 'Numeric
OpLocationId 'Numeric
InternalTime 'Decimal
InternalYield 'Decimal
CustomerTime 'Decimal
CustomerYield 'Decimal
EditedDate 'Datetime

I don't know if Decimal is the best datatype for thoose I have set it for. Values are like:

12,556
0,556
6,1111

(3 decimlas)
0

LVL 29

Expert Comment

ID: 6846660
I would suggest using int (32 bit integer) for id's and float (8 byte floating point) - unless you specifically want to limit the precision to 3 decimals of course.

Mind that sql probably expects a decimal point rather than a decimal comma...
0

Author Comment

ID: 6846678
Thanks GreenGhost.

I still have problems. I recive Line 1: Incorrect syntax near ','.

When I removed all apostrophes.

remoteconn.execute "INSERT OpRegister (ProductId, OpLocationId, InternalTime, InternalYield, CustomerTime, CustomerYield, EditedDate) VALUES (" & ProductId & "," & OpLocationId & "," & InternalTime & "," & InternalYield & "," & CustomerTime & "," & CustomerYield & "," & EditedDate & ")"

And I have used your code like this:

If IsEmpty(Request.Form("InsertInternalTime")) Then
' first time
Else
InternalTime = Request.Form("InsertInternalTime")
If IsNumeric(InternalTime) Then
InternalTime=CLng(InternalTime)

Else
' not numeric value!
' handle this some way...
End If
End If

If IsEmpty(Request.Form("InsertInternalYield")) Then
' first time
Else
InternalYield = Request.Form("InsertInternalYield")
If IsNumeric(InternalYield) Then
InternalYield=CLng(InternalYield)
Else
' not numeric value!
' handle this some way...
End If
End If

If IsEmpty(Request.Form("InsertCustomerYield")) Then
' first time
Else
CustomerYield = Request.Form("InsertCustomerYield")
If IsNumeric(CustomerYield) Then
CustomerYield=CLng(CustomerYield)
Else
' not numeric value!
' handle this some way...
End If
End If

If IsEmpty(Request.Form("InsertCustomerTime")) Then
' first time
Else
CustomerTime = Request.Form("InsertCustomerTime")
If IsNumeric(CustomerTime) Then
CustomerTime=CLng(CustomerTime)
Else
' not numeric value!
' handle this some way...
End If
End If
0

LVL 29

Accepted Solution

Göran Andersson earned 60 total points
ID: 6847225
When it's the first time the page is executed, you can't execute that sql, as you don't have any values.

You only have to check one of the form values for existence (IsEmpty). If one exists, they all exist, so you can put them all inside one If...Else...End If. If the values exist, you can check if they are good, then you can execute the insert.

So, the insert query should only be executed if the form values exist, and all values passes validation (IsNumeric or what else is suitable for that specific field).

You can use a flag;
blnValidated=True.
If any value fails validation, you set
blnValidated=False.
Then, when you have checked all values, you check if blnValidated is still True. If so, you execute the insert, if not, you tell the user what's wrong.
0

LVL 1

Expert Comment

ID: 6859581
hi
use the following function

<%
FUNCTION fixQuotes ( theString )
fixQuotes = Replace( theString, "'", "''" )
END FUNCTION
%>

now get the values by using the following command

InternalTime = fixquotes(trim(Request.Form("InsertInternalTime")))

it will work
0

LVL 2

Expert Comment

ID: 6860000
Hi sporfex,

yet you didn't find a solution,
i just get back to EE..!

In your query i found that, you have removed all apostrophes.  yes its correct only for numeric datatypes.  Anyway i here listed out the delemeters you need to follow in SQL query.

Data type       delimeter              example
----------------------------------------------------
Numeric          <no delimeter>       Productid=30
(int/ float)

String           ''                   Name='sporfex'
date/time        '' or ##             EditedDate='1/19/02'
EditedDate=#1/19/02#

If its SQL Server for date/time datatype you should use only ## for dates.  If its Access you can use '' or ##

-----------------------------------------
Check for the date/time values if any blank.  If its blank then you need to insert null character.

ex: EditedDate=#null#

hope this might be an useful thing for you..!
get back soon.

0

LVL 7

Expert Comment

ID: 8855190
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

accept a comment - fritz

lavinder
EE Cleanup Volunteer
0

LVL 46

Expert Comment

ID: 8857304
I think GreenGhost deserves the points more than I do....

Fritz the Blank
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.