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

Submitted by: 

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,

Can you post here the value of sql?

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,

Just give me the value of the sql. Not the code.

To get the value use the following code:

debug.print sql

then go to the immediate window and copy the result.

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.

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.

ignore this comment please. thanks

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' )

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.