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

    Unanswered: long SQL strings..

    I wonder how to make this code shorter..(smarter)
    and (or) how to do a linebreak in the SQL string, it gets to long in the end.
    Thanks//Martin
    fnr_flera = request.queryString("Fnr_flera")
    myArray=split(fnr_flera,",")
    'As the selections are different every time (1-75 records)
    if UBound(myArray)+1 = 1 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'order by NAMN"
    elseif UBound(myArray)+1 = 2 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"'order by NAMN"
    elseif UBound(myArray)+1 = 3 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'order by NAMN"
    elseif UBound(myArray)+1 = 4 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'order by NAMN"
    elseif UBound(myArray)+1 = 5 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'order by NAMN"
    elseif UBound(myArray)+1 = 6 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'OR Fnr='"&myArray(5)&"'order by NAMN"
    elseif UBound(myArray)+1 = 7 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'OR Fnr='"&myArray(5)&"'OR Fnr='"&myArray(6)&"'order by NAMN"
    elseif UBound(myArray)+1 = 8 then
    SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'OR Fnr='"&myArray(5)&"'OR Fnr='"&myArray(6)&"' OR Fnr='"&myArray(7)&"'order by NAMN"
    elseif UBound(myArray)+1 = 9 then ....
    it goes on until 75... :O
    Last edited by Td04; 10-01-04 at 09:38.
    "Never underestimate a large number of morons"

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    1. Using IN will shorten your SQL a little :

    Code:
    SQL = "SELECT * from owner_alla WHERE Fnr IN ('"&myArray(0)&"','"&myArray(1)&"','"&myArray(2)&"','"&myArray(3)&"','"&myArray(4)&"','"&myArray(5)&"','"&myArray(6)&"' ,'"&myArray(7)&"') order by NAMN"
    2. You can try something like this (not sure about the correct syntax, but here is the idea) :

    Code:
    SQL = "SELECT * from owner_alla WHERE Fnr IN("
    
    FOR i=0 TO UBound(myArray)-1 
        IF (i < UBound(myArray)-1) THEN
            SQL = SQL & "'"&myArray(i)&"',"
        ELSE
            SQL = SQL & "'"&myArray(i)&"'"
        END IF
    NEXT i
    
    SQL = SQL & ") order by NAMN"
    Hope that helps,

    Regards,

    RBARAER

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    That loop was brilliant! It works excellent after just removeing the
    i after next..
    Thanks a million RBARAER, that saved alot of headache
    //Martin :-)
    "Never underestimate a large number of morons"

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Glad it helped you !

    Regards,

    RBARAER

Posting Permissions

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