Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9756
  • Last Modified:

convert string to number

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
p_davis
Asked:
p_davis
  • 14
  • 6
  • 5
1 Solution
 
nschaferCommented:
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
 
p_davisAuthor Commented:
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
 
kevp75Commented:
please post your code
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
p_davisAuthor Commented:
do you want for both pages?
0
 
kevp75Commented:
please
0
 
p_davisAuthor Commented:
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
 
p_davisAuthor Commented:
if you need more of the first page let me know.
0
 
kevp75Commented:
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
 
nschaferCommented:
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
 
p_davisAuthor Commented:
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
 
p_davisAuthor Commented:
thanks neal,
actually team_id is text (but it is the only one)

i will try this method and post back

P
0
 
nschaferCommented:
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
 
kevp75Commented:
have you tried with clng or cdbl?
0
 
p_davisAuthor Commented:
kevp75

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

still working on neals suggestion

P
0
 
p_davisAuthor Commented:
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
 
p_davisAuthor Commented:
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
 
p_davisAuthor Commented:
actually that is exactly what it is doing --any thoughts on how to correct this?
0
 
kevp75Commented:
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
 
p_davisAuthor Commented:
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
 
nschaferCommented:
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
 
kevp75Commented:
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
 
p_davisAuthor Commented:
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
 
p_davisAuthor Commented:
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
 
p_davisAuthor Commented:
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
 
nschaferCommented:
No problem.  I'm glad I could help.

Neal.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now