Trim, Convert String to Numeric

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
sporfexAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Göran AnderssonConnect With a Mentor Commented:
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
 
thunderchickenCommented:
if isnumeric(internaltime) then
   internaltime = cdec(internaltime)
else
   'it isn't numeric
end if
0
 
fritz_the_blankCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sporfexAuthor Commented:
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
 
manihopeverCommented:
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
 
sporfexAuthor Commented:
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
 
Göran AnderssonCommented:
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
 
sporfexAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
sporfexAuthor Commented:
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
 
fritz_the_blankCommented:
I think that you are missing an "into" between insert and OpRegister.

Fritz the Blank
0
 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
Just in case... Are you sure that you are using the correct field name in the Request object?

Fritz the Blank
0
 
sporfexAuthor Commented:
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"

Set DBconn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
strGroup= Cstr(session("iDepartment"))
strName =Cstr(session("iName"))
strUserName =Cstr(session("iUserName"))
DBconn.ConnectionString = "Provider=SQLOLEDB.1;Driver={SQL  Server};SERVER=madeleine;DATABASE=Operationsregister;UID=sa;PWD=;"

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


set remoteconn=server.CreateObject("ADODB.connection")
remoteconn.Open "Provider=SQLOLEDB.1;Driver={SQL  Server};SERVER=madeleine;DATABASE=Operationsregister;UID=sa;PWD=;"

'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>
<head>
<title>OpRegister</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<form name="adddata" method="post" action="operationsregister.asp">
  <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
 
sporfexAuthor Commented:
and the code is on the page operationsregister.asp (the same page that I post the values to.
0
 
Göran AnderssonCommented:
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
 
sporfexAuthor Commented:
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
 
fritz_the_blankCommented:
Doesn't this work?

if VarType(InternalTime) = 8 then
   InternalTime  = CDbl(InternalTime)
end if
0
 
sporfexAuthor Commented:
Fritz,

No.

Type mismatch: 'CDbl'

This line:

InternalTime  = CDbl(InternalTime)
0
 
fritz_the_blankCommented:
Okay,

what happens if you say:

response.write(VarType(InternalTime))

Fritz the Blank
0
 
sporfexAuthor Commented:
VarType is = 8

0
 
Göran AnderssonCommented:
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
 
manihopeverCommented:
your previous comment
----------------------
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
 
sporfexAuthor Commented:
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
 
Göran AnderssonCommented:
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
 
sporfexAuthor Commented:
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
 
loveneesh_bansalCommented:
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
 
manihopeverCommented:
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 ##

instructions to follow during insertion
-----------------------------------------
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
 
lavinderCommented:
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
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
lavinder
EE Cleanup Volunteer
0
 
fritz_the_blankCommented:
I think GreenGhost deserves the points more than I do....

Fritz the Blank
0
All Courses

From novice to tech pro — start learning today.