INSERTING A NULL VALUE DTPICKER TO MS ACCESS DB WITH A DATE/TIME FIELD TYPE

13 years ago By shaolin

hi every one here.

i hope some expert here in vb6.0 could help me on my problem handling null values of dtpicker.

i'm bothering about this error how to solve this. this is my code:

         sql = "INSERT INTO NOSOld ("

         sql = sql & "ID, "

         sql = sql & "Name, "

         sql = sql & "Specialization, "

         sql = sql & "Date_of_NOS, "

         sql = sql & "Date_Encoded, "

         sql = sql & "Case_Officer, "

         sql = sql & "Notes, "

         sql = sql & "Status"

         sql = sql & ")"

         sql = sql & "VALUES "

         sql = sql & "("

         sql = sql & "'" & lngID & "', "

         sql = sql & "'" & Text2.Text & "', "

         sql = sql & "'" & Text3.Text & "', "

         If DTPicker2.CheckBox = True Then

            sql = sql & "'" & CDate(DTPicker2.Value) & "', "

         Else

            sql = sql & Null & ", "

         End If

         If DTPicker1.CheckBox = True Then

            sql = sql & "'" & CDate(DTPicker1.Value) & "', "

         Else

            sql = sql & Null & ", "

         End If

         sql = sql & "'" & Combo1.Text & "', "

         sql = sql & "'" & Text4.Text & "', "

         sql = sql & "'" & Combo2.Text & "' "

         sql = sql & ")"

         cn.Execute (sql)

when i hit enter the error message pop upping saying "INVALID USE OF NULL". i've tried other suggestions but didn't lucky and still getting the same error. 

 

please need some help.

 

thanks in advance,

i declare my sql as string (dim sql As String) i format my dtpicker as mm/dd/yyyy ive also tried to use: sql = "INSERT INTO NOSOld (" sql = sql & "ID, " sql = sql & "Name, " sql = sql & "Specialization, " sql = sql & "Date_of_NOS, " sql = sql & "Date_Encoded, " sql = sql & "Case_Officer, " sql = sql & "Notes, " sql = sql & "Status" sql = sql & ")" sql = sql & "VALUES " sql = sql & "(" sql = sql & "'" & lngID & "', " sql = sql & "'" & Text2.Text & "', " sql = sql & "'" & Text3.Text & "', " 'If DTPicker2.CheckBox = False Then If IsNull(DTPicker2.Value) Then sql = sql & Null & ", " Else sql = sql & "'" & CDate(DTPicker2.Value) & "', " End If 'If DTPicker1.CheckBox = False Then If IsNull(DTPicker1.Value) Then sql = sql & Null & ", " Else sql = sql & "'" & CDate(DTPicker1.Value) & "', " End If sql = sql & "'" & Combo1.Text & "', " sql = sql & "'" & Text4.Text & "', " sql = sql & "'" & Combo2.Text & "' " sql = sql & ")" Debug.Print sql cn.Execute (sql) i tried different ways but still giving me the same error "INVALID USE OF NULL". anyway thanks for your reply, hope can give me another sight to solve my problem. thanks,

sorry sir, by the way this is the result of debug.print. this is the result of immediate window when i unchecked my two dtpickers: INSERT INTO NOSOld (ID, Name, Specialization, Date_of_NOS, Date_Encoded, Case_Officer, Notes, Status) VALUES('109', 'BROWNE, KRISTINE LEAH', 'ACCOUNTANT', , , 'MIRANDA, FLORENDO', 'ENCODED BY JONAS', 'OLD' ) this is the result when i unchecked one of them(either of two): INSERT INTO NOSOld (ID, Name, Specialization, Date_of_NOS, Date_Encoded, Case_Officer, Notes, Status) VALUES('109', 'BROWNE, KRISTINE LEAH', 'ACCOUNTANT', '10/20/210', , 'MIRANDA, FLORENDO', 'ENCODED BY JONAS', 'OLD' ) INSERT INTO NOSOld (ID, Name, Specialization, Date_of_NOS, Date_Encoded, Case_Officer, Notes, Status) VALUES('109', 'BROWNE, KRISTINE LEAH', 'ACCOUNTANT', , '10/20/2010', 'MIRANDA, FLORENDO', 'ENCODED BY JONAS', 'OLD' ) Thanks sir again... hope you could help me, it bothering me since monday this week. Many Thanks.

Now, you see? There's actually no "Null" value in the result of your SQL statement. Try to enclose the Null value with quotation and see what will happen.

i tried to enclosed it with a qoutation but the result is the following error: "data type mismatch in criteria expression". If IsNull(DTPicker2.Value) Then sql = sql & " ' " & Null & " ', " Else sql = sql & " ' " & CDate(DTPicker2.Value) & " ', " End If If IsNull(DTPicker1.Value) Then sql = sql & " ' " & Null & " ', " Else sql = sql & " ' " & CDate(DTPicker1.Value) & " ', " End If value of sql: INSERT INTO NOSOld (ID, Name, Specialization, Date_ofINSERT INTO NOSOld (ID, Name, Specialization, Date_of_NOS, Date_Encoded, Case_Officer, Notes, Status)VALUES ('112', 'MORENO, JOSAPED', 'HACKER', '', '10/21/2010', 'AVECILLA, PAUL VINCENT', 'ENCODED BY SEM', 'OLD' ) ========================================================================= another approach: If IsNull(DTPicker2.Value) Then sql = sql & " " & Null & ", " Else sql = sql & " ' " & CDate(DTPicker2.Value) & " ', " End If If IsNull(DTPicker1.Value) Then sql = sql & " " & Null & ", " Else sql = sql & " ' " & CDate(DTPicker1.Value) & " ', " End If value of sql: INSERT INTO NOSOld (ID, Name, Specialization, Date_of_NOS, Date_Encoded, Case_Officer, Notes, Status)VALUES ('112', 'MORENO, JOSAPED', 'HACKER', , '10/21/2010', 'AVECILLA, PAUL VINCENT', 'ENCODED BY SEM', 'OLD' ) RENO, JOSAPED', 'HACKER', '', '10/21/2010', 'AVECILLA, PAUL VINCENT', 'ENCODED BY SEM', 'OLD' )

revised code: If IsNull(DTPicker2.Value) Then sql = sql & " Null " & ", " Else sql = sql & "'" & CDate(DTPicker2.Value) & "', " End If If IsNull(DTPicker1.Value) Then sql = sql & " Null " & ", " Else sql = sql & "'" & CDate(DTPicker1.Value) & "', " End If i've successfully get it done sir many thanks to you... best regards.

Add new comment

About text formats