Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Inserting Null Value to table

    Access FE, SQL Server BE

    I'm trying to insert data into a sql server table from an unbound user form in my front end using this statement:

    Code:
    strSql = "INSERT INTO PRDCTN_EMP_STATS (EMP_NUM, SHIFT_ID, MINS_OFF, OFF_TYPE_ID, NOTES) " _
            & "VALUES (" & intEmpNum & ", " & intShift & ", " & Nz(Me("txtminlate" & intCtl), 0) & ", " _
            & Me("cmbtor" & intCtl) & ", '" & Me("txtComments" & intCtl) & "')"
    MYCONNECTION.execute strSql
    The field Me("cmbtor" & intCtl) can contain be left empty, and the field
    OFF_TYPE_ID is set to allow nulls. When I try to execute the statement,
    I get "Incorrect syntax near ', ' ". If the field Me("cmbtor" & intCtl) is null,
    how do I get the (non)value Null in the string, so that it will insert Null into the table?
    Inspiration Through Fermentation

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
     
    strSql = "INSERT INTO PRDCTN_EMP_STATS (EMP_NUM, SHIFT_ID, MINS_OFF, OFF_TYPE_ID, NOTES) " _ & "VALUES (" & intEmpNum & ", " & intShift & ", " & Nz(Me("txtminlate" & intCtl), 0) & ", " _ & Iif(Me("cmbtor" & intCtl) = "", "NULL", Me("cmbtor" & intCtl)) & ", '" & Me("txtComments" & intCtl) & "')" MYCONNECTION.execute strSql
    I reckon.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Wouldn't that just insert the string "NULL"?

    This would work, if I could generate a string like:

    "INSERT INTO PRDCTN_EMP_STATS (EMP_NUM, SHIFT_ID, MINS_OFF, OFF_TYPE_ID, NOTES) VALUES (54876, 1, 0, Null, Null)"
    Inspiration Through Fermentation

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RedNeckGeek
    Wouldn't that just insert the string "NULL"?
    What makes you think that? Did you try?


    Quote Originally Posted by RedNeckGeek
    This would work, if I could generate a string like:

    "INSERT INTO PRDCTN_EMP_STATS (EMP_NUM, SHIFT_ID, MINS_OFF, OFF_TYPE_ID, NOTES) VALUES (54876, 1, 0, Null, Null)"
    Precisely
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - to insert a null for a character field you need something else. Sorry thought you meant for numerics only.
    Code:
     
    ...& ", NULLIF('" & Me("txtComments" & intCtl) & "', '')"...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    [embarrassed]
    No I didn't try before questioning you
    [/embarrassed]

    It works, but only after changing it to
    IIf(IsNull(Me("cmbtor" & intCtl)), "NULL", Me("cmbtor" & intCtl))

    I suppose NZ(Me("cmbtor" & intCtl), "NULL") may work too.

    Thanks
    Inspiration Through Fermentation

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RedNeckGeek
    [embarrassed]
    No I didn't try before questioning you
    [/embarrassed]
    Only teasing


    Quote Originally Posted by RedNeckGeek
    It works, but only after changing it to
    IIf(IsNull(Me("cmbtor" & intCtl)), "NULL", Me("cmbtor" & intCtl))

    I suppose NZ(Me("cmbtor" & intCtl), "NULL") may work too.

    Thanks
    Clearly I haven't worked with Access for too long. I thought it read empty text boxes as empty strings . Ho hum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is there a reason not to use a server side stored procedure here? You could define default values for unsupplied parameters, plus you avoid injection attacks.

    <injection method sample removed>
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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