Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Question Unanswered: Help with an SQL SELECT statement in ASP page

    I have built this SQL statement which should
    create the RS I need.

    strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
    Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
    1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"

    This Line:

    objRS1.Open strSQL1, objConn

    Causes this error:

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
    passed to the substring function.

    I have searched google, and found reference to the error meaning it found a
    space in the first position. I tried adding LTRIM into my statement to cure
    it but it made no difference, I may be barking up the wrong tree so to speak
    but I cant find any other information on it.

    If anyone has any ideas why this statement does not work I'd be very
    grateful, the project has to be completed today, and this is the last thing
    to get working now!

    Ta

    Paul McGuire

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Help with an SQL SELECT statement in ASP page

    The length parameter in your LEFT(String, Length) function is

    PatIndex('%821%', [INVENTRY MASTER].BOX_NO) - 1

    PatIndex can return 0, if your string '821' isn't part of your BOX_NO, which makes the length = -1, which isn't allowed as a valid length. Your additional condition does not help, since the expression as a whole will be evaluated.

    Consider to use a view to put your conditon
    [INVENTRY MASTER].BOX_NO LIKE '%821%'
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    Let me expand on what I am trying to achieve to see if you can help further.

    imagine a table collumn

    BOX_NO
    -------------------------
    JHIS 0000821
    JHIS 0000821a
    JHIS 0000821b
    JHIS 00821
    JHIS 00821a
    JHIS 0001821
    JHIS 0001821a
    JHIS 0001821b
    JHIS 01821
    JHIS 01821a

    A user on the internet page I am writing will enter 821 because he wants a list of the boxes:

    JHIS 0000821
    JHIS 0000821a
    JHIS 0000821b
    JHIS 00821

    The JHIS is the users account number the leading 0's can vary which is what has caused the need to use PatIndex in the way which I have? Is there another way?

    All the user knows is box number JHIS 0000821 is box number 821 if he wanted JHIS 0001821 he would enter 1821 and expect to get these boxes returned:

    JHIS 0001821
    JHIS 0001821a
    JHIS 0001821b
    JHIS 01821
    JHIS 01821a

    Any ideas on the best way to do this? Am i on the right lines?

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Why not just

    WHERE BOX_NO LIKE '%0821%'

    ?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Mar 2004
    Posts
    4

    Red face

    erm I am not sure I will put this in now... it seems like this may work if I just add a proceeding '0' to what has been inputted and do a LIKE

    If this works I'm going to be very embarrised but pleased to because its been bugging me for hours!

    I will let you know if this solves the issue

  6. #6
    Join Date
    Mar 2004
    Posts
    4

    Thumbs up

    Thank You very much! I obviously could not see the wood for the trees!

    I changed it slightly because as it stood if 821 was entered 0008210 returned aswell which was not wanted.

    by changing the last % to a [a-z] and adding an OR and just looking for %0821 I get back exactly what I need. And its a nice simple select statement!

    So I offer you a big thanks and a this should keep the managment happy!

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You are welcome.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •