Solved

convert string to number

Posted on 2006-06-30
25
9,736 Views
Last Modified: 2011-08-18
i have a text field that is populated with a field from my access db that is numeric.
it stores it as a string variable and i need to convert it back to numeric as an addnew command is giving me a type mismatch if have search ee and can't find any answer to my issue

any help is appreciated

P
0
Comment
Question by:p_davis
  • 14
  • 6
  • 5
25 Comments
 
LVL 19

Expert Comment

by:nschafer
ID: 17020215
Hi p_davis,

Look at the cint() , cdbl() and clng() functions they convert a string variable into an integer, double, or long type variable respectively.

Hope this helps,
Neal.
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020250
thanks for the quick post..
i have tried to use these functions but am still getting a type mismatch

for more info--
the text field that has the number is then passed to another asp page.--
do i need to make it numeric on the first page or on the one that it is passed to?

i have actually tried it both ways but still can't get it to work

P
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17020276
please post your code
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020292
do you want for both pages?
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17020308
please
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020337
here is the loop in which the text field is created --classid is the one that i am concerned with

<%do%>

</br>

<input type="checkbox" id = "chk" name="chk<%=rslistclasses(0)%>">
<%
dim scheddate, starttime, endtime
scheddate = formatdatetime(rslistclasses(2),2)
starttime = formatdatetime(rslistclasses(3),3)
endtime = formatdatetime(rslistclasses(4),3)
%>



<input type="number" name = "classid" size="4" value ="<%=response.write(rslistclasses(0))%>" readonly>
<%response.write space(5)%>
<input type ="text" name ="description" value ="<%=response.write(rslistclasses(1))%>">
<%response.write space(3)%>
<input type="text" name="date" size = "7" value ="<%=response.write(scheddate)%>">
<%response.write space(3)%>
<input type ="text" name = "start" size = "9" value ="<%=response.write(starttime)%>">
<%response.write space(3)%>
<input type = "text" name = "end" size = "9" value = "<%=response.write(endtime)%>">
<%response.write space(3)%>
<input type = "text" name= "location" value ="<%response.write(rslistclasses(5))%>">
<%
 rslistclasses.movenext
loop while rslistclasses.eof = 0
 %>

<%end if%>

===========================
here is the next page                          ==
===========================

<%

specialistid = request.form("specialist")
team = request.form("team_id")
classnumber = request.form("classid")

dim adocon
dim registerclass
dim strsql
dim question
dim item


set adocon = server.createobject("ADODB.connection")

adocon.open"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Training Classes.mdb")




strsql = "select * from roster;"

For Each Item in Request.Form

    If Left(Item, 3) = "chk" Then
       If Request.Form(Item) = "on" Then
         
       Response.Write Item & ": " & Request.Form(Item) & "</br>"

set registerclass = server.createobject("ADODB.Recordset")

registerclass.cursortype=2
registerclass.locktype=3


registerclass.open strsql, adocon


registerclass.addnew
registerclass.fields("Team_ID")= team
registerclass.fields("Specialist_id")= specialistid

registerclass.fields("ID")=classnumber

registerclass.update

response.write("You have register for the selected classes")



else
response.write("registration failed")

 
    End If
registerclass.close
set registerclass = nothing

end if


Next

set adocon = nothing
%>



Sorry that it is messy but i have be t/s'ing this for a few days

thanks
P
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020345
if you need more of the first page let me know.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17020377
please see here.
http://www.w3schools.com/tags/tag_input.asp

there is no <input type="number", so you will have to change it to text.  Also I noticed you made this field readonly....is there a reason?

then you will have to change:
registerclass.fields("ID")=classnumber

to:
registerclass.fields("ID")=cint(classnumber) 'or clng(classnumber), or cdbl(classnumber) in order to convert it to a number.   Please double check in the database and make sure that field is actually a number field
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17020425
p_davis,

Perhaps this will work for you.

I am assuming that all three fields are numeric.  
This is a different way of adding a record without first creating a recordset.
Since the generated strsql variable will not have quotes around the values they will be inserted as numbers.


==============================
specialistid = request.form("specialist")
team = request.form("team_id")
classnumber = request.form("classid")

dim adocon
dim registerclass
dim strsql
dim question
dim item

set adocon = server.createobject("ADODB.connection")
adocon.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Training Classes.mdb")

For Each Item in Request.Form

    If Left(Item, 3) = "chk" Then
      If Request.Form(Item) = "on" Then
        Response.Write Item & ": " & Request.Form(Item) & "</br>"
        strsql = "insert into roster (team_id,specialist_id,id) values (" & team & "," & specialistid & "," & classnumber & ")"
        conn.execute(strsql)
        response.write("You have register for the selected classes")
      else
        response.write("registration failed")
      end If
    end if
Next

set adocon = nothing
%>


Neal.
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020454
sorry ("number") was left over from my desparation---it was originally text
and i have already tried to use the cint function on classnumber and it gives me a type mismatch 'cint'

tried it again for good measure with the same result

----yes that field is setup as type-- number

thanks
p
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020471
thanks neal,
actually team_id is text (but it is the only one)

i will try this method and post back

P
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17020488
p_davis,

If TeamID is text then change the code as follows:

strsql = "insert into roster (team_id,specialist_id,id) values ('" & team & "'," & specialistid & "," & classnumber & ")"

Neal.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 25

Expert Comment

by:kevp75
ID: 17020653
have you tried with clng or cdbl?
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020660
kevp75

yes and i get the same error that i did with cint

still working on neals suggestion

P
0
 
LVL 22

Author Comment

by:p_davis
ID: 17020917
ok,
had to modify just a bit of neal's code (thank you neal) and now i am getting this error.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
/training/register1.asp, line 29

""""""""""""
""code""""
""""""""""""

for each item in request.form
      if left(item,3)="chk" then
      if request.form(Item) = "on" then
      response.write(Item) & ": " & request.form(Item) & "</br>"
strsql = "insert into roster (Specialist_ID,Team_ID,ID) values (" & specialistid & ",'" & team & "'," & classnumber & ")"

adocon.execute(strsql)
response.write("you have registered for the selected classes")
else
response.write("registration failed")
end if
end if
next

set adocon = nothing

""""""""""""""""""""
table structure""""
"""""""""""""""""""""
no primary key

Specialist_ID              Team_ID              ID
(number)                    (text)                  (number)

thanks again

P
0
 
LVL 22

Author Comment

by:p_davis
ID: 17021051
it appears that the classid is trying to insert every value on the page separated by commas.(ie there are several classes that specialists can register for 192, 193, 194)
0
 
LVL 22

Author Comment

by:p_davis
ID: 17021080
actually that is exactly what it is doing --any thoughts on how to correct this?
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17021094
yes....you will need to change the field the data is stored in  (I had a hunch this is what may be happenning, hence why I asked if the datatype was correct)

if it is ms access use text or memo, if sql then use nvarchar or ntext

you will also need to wrap classid with single quotes
0
 
LVL 22

Author Comment

by:p_davis
ID: 17021134
unfortunately the class id's are a primary key in another table that uses autonumber (ms)  the roster class id (number) refers to this for scheduling information as people register.---this is so the person scheduling classes doesn't have to remember what number they are on and manually type it in

is there possibly another way around this
0
 
LVL 19

Accepted Solution

by:
nschafer earned 500 total points
ID: 17021305
This may be a bit easier:

On your form page:
--------------------------
<%do%>
</br>
<input type="checkbox" id="check1" name="check1" value="<%=rslistclasses(0)%>">
...
=-=-=-=-==-=-=-=-=-

Then on the page you post to:
---------------------------------
specialistid = request.form("specialist")
team = request.form("team_id")
classnumber = request.form("check1")
aClass = split(classnumber,",")
dim adocon
dim registerclass
dim strsql
dim question
dim item
set adocon = server.createobject("ADODB.connection")
adocon.open"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Training Classes.mdb")
for i = 0 to ubound(aClass)
  response.write aClass(i) & ": "
  strsql = "insert into roster (Specialist_ID,Team_ID,ID) values (" & specialistid & ",'" & team & "'," & aClass(i) & ")"
  adocon.execute(strsql)
  response.write("you have registered for the selected classes")
next
%>
=-=-=-=-=-=-=-=-=-=-

What this is doing:

Instead of gathering the names of the checkboxes selected, all of the checkboxes will have the same name (check1).  When a user selects more than 1 check box with the same name the result posted is a comma deliminated string of all of the selected values.  We then use the split function to split this string into an array and then loop through the array to insert into the database.

Neal.

 
 

0
 
LVL 25

Expert Comment

by:kevp75
ID: 17021351
well, unfortunatley when you have multiple checkboxes, with the same name, you will always get a comma seperate list of numbers, text, or other characters.  Since you are trying to store this to a database, you will have to convert the datatype of that field, or do what nschafer posted above.

if you aren't willing to do this, then there is no answer to your question
0
 
LVL 22

Author Comment

by:p_davis
ID: 17021360
neal that is freakin awesome
thanks alot--that worked.

P

didn't know what i was getting myself into when i took on this project but i am learning so much especially with the help of experts like you two---

neal's solution worked so points to him
0
 
LVL 22

Author Comment

by:p_davis
ID: 17021372
kevp75 it is not a matter of what i am willing to, do there are certain restrictions that have been placed on me in this project--this just happens to be one of them---

thanks for your time.
0
 
LVL 22

Author Comment

by:p_davis
ID: 17021937
neal,
just wanted to thank you again
you have introduced a new function to me in asp and explained how to use it.---

P
0
 
LVL 19

Expert Comment

by:nschafer
ID: 17022279
No problem.  I'm glad I could help.

Neal.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 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

18 Experts available now in Live!

Get 1:1 Help Now