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?

[Webinar] Streamline your web hosting managementRegister Today

rohanbairat3Connect With a Mentor Commented:
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
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 .

rlibrandiConnect With a Mentor Commented:
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
gladxmlConnect With a Mentor Commented:
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...
All Courses

From novice to tech pro — start learning today.