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.

'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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 .

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
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

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.