[AccessD] Dynamic SQL statement

[Stuart McLachlan]

Where some fields may not be required (Null), I  use one Insert statement which inserts 
every column every time but wrap every insert parameter in a function appropriate to the 
datatype which handles empty fields and escapes strings. 


(This is actually PB code, so replace the "FUNCTION = " with the function name and you 
may need to modify the functions slightly to accept a Variant as the argument to cater for 
Nulls, but the principle will be the same)

FUNCTION SQLNum(strIn AS STRING) AS STRING
    IF strIn = "" THEN
        FUNCTION = "Null"
    ELSE
        FUNCTION = strIn
    END IF
END FUNCTION

FUNCTION SQLDate(strIn AS STRING) AS STRING
    IF strIn = "" THEN
        FUNCTION = "Null"
    ELSE
        FUNCTION = "'" & LEFT$(strIn,4) & "-" & MID$(strIn,5,2) & "-" & MID$(strIn,7,2) & "'"
    END IF
END FUNCTION

FUNCTION sqlText(strIn AS STRING) AS STRING
   LOCAL strT AS STRING
   IF strIn = "" THEN
       FUNCTION = "Null"
   ELSE
       strT = strIn
       REPLACE "'" WITH "''" IN strT
       FUNCTION = "'" & strT & "'"
   END IF
END FUNCTION


Then I build my SQL along the lines of:

           sSQL = "INSERT INTO tblElectors VALUES( " & _
            sqlText(arrResults(x,1)) & _    'GUID
            "," & _
            sqlnum(arrResults(x,2)) & _          'ElectorID
           "," & _
            sqlText(arrResults(x,3)) & _   'LNAme
            "," & _
            sqlText(arrResults(x,4)) & _   'FNAme
            "," & _
            sqlDate(arrResults(x,5)) & _ 'DOB
 

On 7 Dec 2015 at 15:54, FW Salato Center wrote:

> I have two problems really: 
> 
> 1.) Empty fields that must be accommodated in the SQL because they
> aren't required. So, I have delimited or non-delimited results to
> accommodate each time. 2.) I wrote four different INSERT INTO
> statements that accommodated all possibilities because this statement
> is a bit inflexible.
> 
> There's no SELECT, so I'm not sure what you're suggesting. I'm
> listening. :) 
> 
> I fully admit to be a bit of a linear thinker. I don't always see the
> possibilities because I'm looking down a straight line of here's what
> I've got, here's where I need to be... code, code, code... till I get
> there. I seldom see other routes until I'm done and then with that in
> my head, I suddenly see other shorter routes. It's a ... curse. :) But
> in my mind, with enough variables and concatenation, I see the
> possibility of reducing this all to a few lines, but maybe not. :) 
> 
> It's why I never could play softball -- the ball simply never took the
> route I expected and was flying by my shoulder before I knew it had
> left the pitcher's mound. 
> 
> John's most likely right about the class suggestion and if I thought
> I'd need more than one more input form for this, it might be a cool
> route to take, if for no other purpose than to stretch my brain. You
> know, they're recommending that us old folks color and do puzzles now.
> However... the gray matter's not as springy as it used to be. It might
> snap back and hurt me.  
> 
> Susan H. 
> 
> 
> How about two separate strings - one for the INSERT clause and one for
> the SELECT clause.  Assemble them in parallel adding to both the
> fields you want and then append the SELECT to the INSERT at the end?
> 
> R
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>