[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Trying to insert a string using SQL into a DB2 database. Got ODBC err '80040e14'.  The string was derived from string1|12345678|string2. I got the number part by using the inStr and the mid functions.

Posted on 2004-10-25
Medium Priority
Last Modified: 2010-04-06
'originalString came from a Check Box  
originalString =  string1|12345678|string2
FormFind = Instr(1,originalString,"|")
FormFind = FormFind - 1
School = Mid(originalString),1,FormFind)
FormFind = Formfind + 2
BarCode = Mid(originalString),FormFind,8)
FormFind = FormFind + 9
Program1 = Mid(originalString,FormFind)
MySQL = "INSERT INTO FILE1 (PROGRAM_NAME, BAR_CODE, SCHOOL_NAME) values ('"& Program1 &"', '"& BarCode &'", '" & School &"")

Client Access Express ODBC err - Token - was not valid),.
The problem seems to be the bar code.  When I Response.Write the SQL the value that is in BarCode is '12345678 '.  There is an extra character after the 8. I tried trimming, but that did not work.  How do I replace or remove this extra character.
Question by:Rafasan56
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

rohanbairat3 earned 672 total points
ID: 12404836
You can also use the split function to split the text
 strArr= split(strType, "|")

can be used to get the values

as far is replacing is concerned

str= Replace(str," ","")

But you should be absolutely sure what you want to replace.

Let me know if I answered it right

Expert Comment

ID: 12404899
hi Also if the barcode size is going to be fixed and if the extra charachter is entered in the end you can do the following

str = left(str,8)

but be sure that string is not empty of length is 8 0r more than 8

let me know if you want code for that .


Assisted Solution

rlibrandi earned 664 total points
ID: 12444887
Betcha the Trim didn't work because the rightmost character isn't really a space.  Use RESPONSE.WRITE Asc(Right(BarCode, 1)) to see what the ascii value is.  - just for curiosity sake

Try this

dim arrTemp()
dim intLen
intLen = Len(BarCode)
reDim arrTemp(intLen)

dim i

'Read BarCode into array 1 character at a time
For i = 1 to intLen
   arrTemp(i) = mid(BarCode, i, 1)

dim strTemp
'Read the array into strTemp replacing any non-valid characters with a known

For i = 1 to intLen
   If ASC(arrTemp(i)) >= 48 And ASC(ArrTemp(i)) <= 57 Then
        strTemp = strTemp & arrTemp(i)
   ElseIf ASC(arrTemp(i)) >= 65 And ASC(ArrTemp(i)) <= 90 Then
        strTemp = strTemp & arrTemp(i)
   ElseIf ASC(arrTemp(i)) >= 97 And ASC(arrTemp(i)) <= 122 Then
        strTemp = strTemp & arrTemp(i)
   End If

BarCode = strTemp

This will filter out only valid character data from BarCode
LVL 15

Assisted Solution

gladxml earned 664 total points
ID: 12480194
you can try this....

originalString =  string1|12345678|string2
arraystr = split(originalString,"|")
for i = Lbound(arraystr) to Ubound(arraystr)
if i = 1then
School = trim(arraystr(i))
end if
if i = 1then
BarCode = trim(arraystr(i))
end if
if i = 1then
Program1 = trim(arraystr(i))
end if

I think this is incorrect

MySQL = "INSERT INTO FILE1 (PROGRAM_NAME, BAR_CODE, SCHOOL_NAME) values ('"& Program1 &"', '"& BarCode &'"<error ' must be after the ">, '" & School &"")

you can try this...

MySQL = "INSERT INTO FILE1 (PROGRAM_NAME, BAR_CODE, SCHOOL_NAME) values ('"& Program1 &"', '"& BarCode &"', '" & School &"")


MySQL = "INSERT INTO FILE1 (PROGRAM_NAME, BAR_CODE, SCHOOL_NAME) values ('"& Program1 &"', "& BarCode &", '" & School &"")

if bar_code is a number data type


Happy programming...

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

650 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