Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    104

    Unanswered: Problem with empty values in INSERT INTO

    Hi
    I am trying to solve a problem that I have.
    Why in a sql statement INSERT INTO ... VALUES ... whenever we have empty values in non requirement field we have a message "Syntax error in INSERT INTO statement..."

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ignoring your philosophical question, can you work around it by replacing:
    possiblyEmptyValue
    with
    iif(isnull(possiblyEmptyValue), something, possiblyEmptyValue)

    ....where something is a value your field can understand ("", 0, #1/1/100#, etc)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Posts
    104
    I am sorry but the problem it doesn't in the field (when I try to insert a record with empty values in non requirement field on Table (ACCESS) all are OK). Notice that the form is not bound.
    Thanks

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Given the table,

    TableA(A, B, C, D)

    Insert into table (A, B, C) values (a, b, c);

    The value of column "D" for the above INSERT statement will be set to the column default, if supplied, otherwise it will be set to null.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Posts
    104
    Originally posted by r123456
    Given the table,

    TableA(A, B, C, D)

    Insert into table (A, B, C) values (a, b, c);

    The value of column "D" for the above INSERT statement will be set to the column default, if supplied, otherwise it will be set to null.
    Ok but the value on D doesn't always null!

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Can you please explain your problem further, specifically the INSERT statement you are using and the data type of the column in question.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Feb 2004
    Posts
    104
    "INSERT INTO MyTable (fld1,fld2) VALUES (" & txt1 & "," & txt2 & ")". fld2 is Numeric and it isn't required by MyTable. Some times txt2 haven't anything (it is Null). In this case I have problem. if the fld2 were an alphanumeric field then the problem could solve with quotes in txt2 like INSERT... & ",'" & txt2 & "')" but it isn't, it is Numeric and I don't want a zero as default value in fld2.

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    TableA (var1 Number, var2 Number);
    var1 DEFAULT NULL
    var2 DEFAULT 0

    Insert into tableA (var2) values (2);
    Code:
    var1  var2
    -------------
    NULL    2
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Feb 2004
    Posts
    104
    This means that I have to make a different SQL for each numeric field which have the possibility to be Null. But... I want to avoid this because I have 16 non requirement numeric fields (Oh my got!).

Posting Permissions

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