Skip to main content

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

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

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.