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
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
    LVL 7

    Accepted Solution

    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
    LVL 7

    Expert Comment

    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 .

    LVL 4

    Assisted Solution

    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

    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...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

     Java Android Coding Bundle

    Whether you're an Apple user or Android addict, learning to code for the Android platform is an extremely valuable, in-demand skill. It all starts with Java, the language behind the apps and games that make Android the top platform it is today.

    It's sometimes a bit tricky to use date functions in Oracle BPEL. I'll explain quickly how you can add N days to the current date. In a BPEL process this can be useful, and you can adapt it to fit your needs. First of all, let's see how to add 1 …
    Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
    Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
    The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

    884 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

    16 Experts available now in Live!

    Get 1:1 Help Now