Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303

    Unanswered: Insert Command does not like Null values.

    - I'm a Novice -

    I'd like to use an insert command to insert whatever data that is on the unbound form. The first three fields are required and I check those values prior to executing the code below. But some fields may be null.

    Dim strSQL as string
    .
    .
    code
    .
    .
    strSQL = "INSERT INTO tblCase " & _
    "(Case_SiteName, " & _
    "Case_PatientSequenceNumber, " & _
    "Case_PatientInitials, " & _
    "Case_DOB, " & _
    "Case_ProcedureDate, " & _
    "Case_Investigator, " & _
    "Case_Institution, " & _
    "Case_Value) " & _
    "VALUES ('" & Me.cboSiteName & "', " & _
    Me.txtPatientNumber & ", '" & _
    Me.tbxPatientInitials & "', '" & _
    Me.tbxPatientDOB & "', '" & _
    Me.tbxProcDate & "', '" & _
    Me.tbxInvestigator & "', '" & _
    Me.tbxInstitution & "', " & _
    Me.grpValue & ");"

    CurrentProject.Connection.Execute strSQL

    But, when any of the last four are unanswered, i get an error. Any, or all of the last four can be blank. Any suggestions?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have the table DDL?

    I'm think the date field is NOT NULL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I'm sorry? What is the DDL?

    In the table design, the fields in question are not required - they can be null for the record to be accepted. The problem is using the insert command to pass null data.

    Is there some trick or short syntax that would allow me to send nothing?

    John
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  4. #4
    Join Date
    Dec 2003
    Posts
    15
    DDL = Data Definition Language

    You can't pass an empty string and expect it to represent NULL, you need to use vbNull from VB* in order to pass into a non text field and end up with null.

    Dan

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    DDL - data definition language - basically the definition of the table that you are trying to insert into (data types, null/not null ...)

    You can use vbNull within visual basic.

    What error are you receiving - does this error occur when you send '' in the insert statement to sql server ? This may be because you are inserting '' into a date field which is not a valid null value (if there is such a thing as a valid null value).

  6. #6
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Error when the text field is null
    In the case of a missing text file, it gives the error# -2147467259.
    The field myfield cannot be a zero-length string.

    I like the Nz function. If I wrap the Nz function to include a value if the input is null, then the Insert works.
    e.g., Nz(Me.tbxInvestigator, " ")

    Error when the Date field is null
    If the date field is empty, I get a type mismatch. I guess, because I'm trying to send a zero length string to a date field.

    Re: vbNull. Are you saying I can send 'vbNull'
    I tried Nz(Me.tbxProcDate, vbNull)
    and it didn't work. Same error.

    For my solution, I ended up assmbling the SQL string using if statements based on user inputs.

    If you can tell whether I am misusing the vbNull, please let me know.

    Thanks.
    Last edited by jpshay; 01-05-04 at 19:32.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  7. #7
    Join Date
    Dec 2003
    Posts
    454

    the problem is ''

    The problem is that in your INSERT statement you add '' ('MyValue') for each value. If the value is NULL it causes the problem. In order to avoid this problem you need to check if the value is NULL. If it is, you can assign " " (a space).

  8. #8
    Join Date
    Oct 2003
    Posts
    706

    Angry

    Actually, I'm not sure that an empty-string or a space is interpreted as being equivalent to NULL. As far as I know, the only way to express a null value is with the (non-quoted) keyword NULL.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    Dec 2003
    Posts
    17
    If you want the value NULL stored in the database for the columns that have no values you should consider something like this as the value for the insert:

    IIF(ISNULL(Me.tbxInvestigator), NULL, "'" & Me.tbxInvestigator & "'")

  10. #10
    Join Date
    Dec 2003
    Posts
    454

    solution

    Here is the solution if the last four fields are allowed to have NULL:

    Dim strProcedureDate, strInvestigator, strInstitution, intValue
    If IsNull(Me.tbxProcDate) or Trim(Me.tbxProcDate) = "" Then
    strProcedureDate = NULL
    Else
    strProcedureDate = "'" & Trim(Me.tbxProcDate) & "'"
    End If

    If IsNull(Me.tbxInvestigator) or Trim(Me.tbxInvestigator) = "" Then
    strInvestigator = NULL
    Else
    strInvestigator = "'" & Trim(Me.tbxInvestigator) & "'"
    End If

    If IsNull(Me.tbxInstitution) or Trim(Me.tbxInstitution) = "" Then
    strInstitution = NULL
    Else
    strInstitution = "'" & Trim(Me.tbxInstitution) & "'"
    End If

    If IsNull(Me.grpValue) or Me.grpValue = "" Then
    intValue = NULL
    Else
    intValue = Me.grpValue
    End If

    strSQL = "INSERT INTO tblCase " & _
    "(Case_SiteName, " & _
    "Case_PatientSequenceNumber, " & _
    "Case_PatientInitials, " & _
    "Case_DOB, " & _
    "Case_ProcedureDate, " & _
    "Case_Investigator, " & _
    "Case_Institution, " & _
    "Case_Value) " & _
    "VALUES ('" & Me.cboSiteName & "', " & _
    Me.txtPatientNumber & ", '" & _
    Me.tbxPatientInitials & "', '" & _
    Me.tbxPatientDOB & "', " & _
    strProcedureDate & ", " & _
    strInvestigator & ", " & _
    strInstitution & ", " & _
    intValue & ")"
    Last edited by gyuan; 01-05-04 at 23:57.

  11. #11
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks for the feedback people!

    gyuan - your solution does not work. A date field will not accept the value of " ".

    sundialsvcs - you are correct about gyaun's comment. Though I never sent the unquoted word NULL, I did try vbNull. Unsuccessful.

    sjp - I think I understand your comment correctly. However, burying four of the IIF statements into the concatenation of a sting is a little messy, and I am unsure about the syntax.

    gyuan - And finally back to gyuan - thanks for the second entry. As I
    stated, I don't think your solution would work, because - for whatever
    reason - I am unable to send Nulls with my Insert command. As I
    mentioned above, I did end up assmbling the SQL string using if statements based on user inputs. It looks similar to the good idea you
    offered with the if statements.

    I build the Fields and Values separately as I go...

    strSQLFields = "INSERT INTO tblCase " & _
    "(Case_SiteName, Case_PatientSequenceNumber, Case_PatientInitials, " & _
    "Case_Investigator, Case_Institution, Case_PFO"
    strSQLValues = "VALUES ('" & Me.cboSiteName & "', " & _
    Me.txtPatientNumber & ", '" & _
    Me.tbxPatientInitials & "', '" & _
    Me.tbxInvestigator & " ', '" & _
    Me.tbxInstitution & " ', " & _
    Me.grpPFO

    If Not IsNull(Me.txtPatientSelector) Then
    strSQLFields = strSQLFields & ", Case_PatientCode"
    strSQLValues = strSQLValues & ", '" & Me.txtPatientSelector & "'"
    End If

    If Not IsNull(Me.tbxPatientDOB) Then
    strSQLFields = strSQLFields & ", Case_DOB"
    strSQLValues = strSQLValues & ", '" & Me.tbxPatientDOB & "'"
    End If

    If Not IsNull(Me.tbxProcDate) Then
    strSQLFields = strSQLFields & ", Case_ProcedureDate"
    strSQLValues = strSQLValues & ", '" & Me.tbxProcDate & "'"
    End If

    strSQLFields = strSQLFields & ") "
    strSQLValues = strSQLValues & ");"


    The question remains, why can't i send a null value to a date field in an Access table using code?

    thanks everyone!
    Last edited by jpshay; 01-06-04 at 11:59.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  12. #12
    Join Date
    Dec 2003
    Posts
    454

    It works on MSSQL 2000

    It works on MSSQL 2000 if the date field is allowed to have NULL, but I did not test it on Access.

  13. #13
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks for your help!

    John
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  14. #14
    Join Date
    Feb 2002
    Posts
    2,232
    John - Do you find a solution to pass null ? I thought your first posting was from vb to sql server, but now from what you have posted, it appears to be in access. So is it access to access or access to sql server ?

  15. #15
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    My fault for not specifying. This is a database built in Access. I'm making a thorough and painful transition from DAO to ADO, and eventually the back uend may go to SQL.

    No, I didn't figure out a way to send a Null to an Access table using the insert command.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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