Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    23

    Unanswered: Insert query with null values for integer data type

    Hi,

    I have a query as below:
    queryStr = "INSERT INTO [Student] (StudentID, Height, AGE) VALUES (" & txtStudentId.Value & ", " & Nz(txtHeight.Value, " ") & ", " & Nz(comboAge.Value, "") & ")"

    This query gets executed when the Fields txtStudentId, txtHeight, comboAge are filled in the form and save button is clicked.

    Datatypes for the fields in table are StudentID-Text, Height-Number, Age-Number.

    There are cases when txtHeight, comboAge can be NULL. when the query gets executed for those values, I'm getting the error: "Syntax error in INSERT INTO statement" as the values txtHeight, comboAge are NULL. It works fine when they are non NULL.

    Can any one let me know on how to resolve this issue. thanks.

  2. #2
    Join Date
    May 2009
    Posts
    258
    What happens if you leave the Nz's off the values?
    Code:
    queryStr = "INSERT INTO [Student] (StudentID, Height, AGE) VALUES (" & txtStudentId.Value & ", " & txtHeight.Value & ", " & comboAge.Value & ")"
    As you have it, if a value is null, it will return an empty string, which doesn't convert well to a number. You should probably also put single quotes around the ID in your query, if it is indeed stored as a text field.

    Regards,

    Ax

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    If nulls are acceptable, why are you using nz to replace a null value?

    As ax says you are forcing

    1 A space to be assigned to Height if its control value is NULL - Is height a datatype string? I doubt it?

    2 A zero-length string for age if its value is NULL - Again, I doubt that age is a string value.

    Ditch the NZ's if NULLS are allowed and ensure that your table defenitions allow NULLs as acceptable values.

    Also - Are txtStudentID txtHeight and comboAge.Value variables or are you refering to form values - you should refer to these with me.controlname.value in that case.

    Can you post a .zip of this form or the code in its entirity - quite simple to fix this.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Jun 2009
    Posts
    23

    Re: Insert query with null values for integer data type

    @ax
    I'm getting the same issue when i remove the Nz also.

    @garet
    Height's datatype is Number.
    I'm not finding any property named 'Allow Zero Length, Allow Nulls" in table for number fields.
    The fields txtStudentId, txtHeight, comboAge are form values, I think we can use it in this way instead of me.controlname.value

    Find the attached DB. Please let me know on how to fix this.
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    queryStr = "INSERT INTO Student ( StudentID, Height, Age ) " & _
    "SELECT " & Me.txtStudentId & " AS StudentID, " & Nz(Me.Height, "NULL") & " AS Height, " & Nz(Me.txtHeight, "NULL") & " AS Age;"
    <This is the syntax you need...

    However, there are other issues you need to address;

    StudentID must be unique - You should really have a primary key in this table which is automatically generated - Have a good think about your table design.

    Currently if you try and save a record with the same StudentID you will get an error.

    Prefix all your object names so you know what type of control / datatype the objects are - this will avoid accidentally using 'reserved words' (Google the phrase if you don't understand). This is not an issue in this case but is 'good practice'.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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