How to insert blank or null value in date filed in ms access by VB.NET

Question

1.00/5 (1 vote)

See more:

HI, I have start_date and end_date. sometimes end_date may be blank or null. the datatype of end_date in Ms access database is "Date/time". to select date, i am using datetimepicker in vb.net
How can I insert null value in end_date in database by vb.net
I am using below code but it gives me below error:

value of type 'DBNull'can not be converted into 'Date'

Please guide me. Heaps thanks in advance

What I have tried:

I am using this code

VB

Dim enddate As Date
        If end_dateDateTimePicker1.Checked Then
            enddate = end_DateTimePicker1.Value.Date
        Else
            endddate = System.DBNull.Value
        End If

       Dim command As OleDbCommand
        str = "insert into Construction_Progress_Table(end_date) values('" & endddate & "'"
        command = New OleDbCommand(str, PO_con)
        command.ExecuteNonQuery()

Posted 3-Feb-21 16:47pm

vish2778

Updated 4-Feb-21 0:58am

RickZeeland

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2021-02-04T00:58:00

Solution 3

Simple - use a properly parameterized command. That way, you don't have to worry about formatting the values properly, and you'll avoid any SQL Injection[^] vulnerabilities too.

VB.NET

Using command As New OleDbCommand("INSERT INTO Construction_Progress_Table (end_date) VALUES (?)", PO_con)
    If end_dateDateTimePicker1.Checked Then
        command.Parameters.AddWithValue("@EndDate", end_DateTimePicker1.Value.Date)
    Else
        command.Parameters.AddWithValue("@EndDate", System.DBNull.Value)
    End If

    command.ExecuteNonQuery()
End Using

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Posted 4-Feb-21 0:58am

Richard Deeming

Comments

vish2778 4-Feb-21 23:19pm

hi Richard, thanks for your support. first of all there is not only 1 filed(end_date). there are 6 datetimepicker fields in database so insert query will have 6 datetimerpicker fields.
Second thing " end_dateDateTimePicker1.Checked " uses end_dateDateTimePicker1 default value even if user does not select date or does not click on that. Please let me know. Heaps thanks

Richard Deeming 5-Feb-21 3:33am

So use six parameters. What's the problem?

Using command As New OleDbCommand("INSERT INTO Construction_Progress_Table (col1, col2, col3, ...) VALUES (?, ?, ?, ...)", PO_con)
    If end_dateDateTimePicker1.Checked Then
        command.Parameters.AddWithValue("@EndDate1", end_DateTimePicker1.Value.Date)
    Else
        command.Parameters.AddWithValue("@EndDate1", System.DBNull.Value)
    End If

    If end_dateDateTimePicker2.Checked Then
        command.Parameters.AddWithValue("@EndDate2", end_DateTimePicker2.Value.Date)
    Else
        command.Parameters.AddWithValue("@EndDate2", System.DBNull.Value)
    End If

    If end_dateDateTimePicker3.Checked Then
        command.Parameters.AddWithValue("@EndDate3", end_DateTimePicker3.Value.Date)
    Else
        command.Parameters.AddWithValue("@EndDate3", System.DBNull.Value)
    End If
    ...
    command.ExecuteNonQuery()
End Using

vish2778 5-Feb-21 5:25am

You are genius mate, all good. Actually end_dateDateTimePicker1.Checked property was "False" so it was not picking up right thing. now sorted. heaps thanks mate

Richard Deeming 5-Feb-21 5:36am

No problem. :)