Solved

Trim, Convert String to Numeric

Posted on 2002-03-05
30
457 Views
Last Modified: 2007-12-19
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
Comment
Question by:sporfex
  • 11
  • 8
  • 5
  • +4
30 Comments
 
LVL 11

Expert Comment

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

Expert Comment

by:fritz_the_blank
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

by:sporfex
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

by:manihopever
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

by:sporfex
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

by:Göran Andersson
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

by:sporfex
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

by:fritz_the_blank
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

by:sporfex
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

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

Fritz the Blank
0
 
LVL 46

Expert Comment

by:fritz_the_blank
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

by:fritz_the_blank
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

by:sporfex
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"

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
 

Author Comment

by:sporfex
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

by:Göran Andersson
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:sporfex
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

by:fritz_the_blank
ID: 6844074
Doesn't this work?

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

Author Comment

by:sporfex
ID: 6844101
Fritz,

No.

Type mismatch: 'CDbl'

This line:

InternalTime  = CDbl(InternalTime)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6844250
Okay,

what happens if you say:

response.write(VarType(InternalTime))

Fritz the Blank
0
 

Author Comment

by:sporfex
ID: 6844302
VarType is = 8

0
 
LVL 29

Expert Comment

by:Göran Andersson
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

by:manihopever
ID: 6846390
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
 

Author Comment

by:sporfex
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

by:Göran Andersson
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

by:sporfex
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

by:
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

by:loveneesh_bansal
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

by:manihopever
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 ##

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
 
LVL 7

Expert Comment

by:lavinder
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
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
lavinder
EE Cleanup Volunteer
0
 
LVL 46

Expert Comment

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

Fritz the Blank
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now