conversion from string to type date is not valid

Question

I really need your help on this matter. I have search many similar topics about this but cannot find the right one for my program. Ill try to simplify my question and exclude other details which is not related on my concern.

My vb program has a datetimepicker named dtpDate, 2 buttons named btnRecord_Save and btnClear_Date.

My sql has a table named Voucher with a table named Voucher_Date (Datatype = Date)

When my dtpdate has a date value, i have no issue as it works perfectly fine, but if i clear the date on dtpdate by pressing btnClear_date, it gives me an error (as mentioned on above title). All i wanted is if dtpDate is blank, it will store 'NULL' value on my sql (Column Name = Voucher_date).

Here is my code for your reference, i have edited and excluded unnecessary information.

CLASS CODE (SQLControl)

Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class SQLControl

 Public SQLCon As New SqlConnection With {.ConnectionString i got this!!!}
 Public SQLDA As SqlDataAdapter
 Public SQLDS As DataSet
 Public Params As New List(Of SqlParameter)
 Public RecordCount As Integer
 Public Exception As String
 Private SQLCmd As SqlCommand
 Public Sub AddToVoucher(Voucher_Date As Date)
    Try
        Dim strInsert As String = "INSERT INTO Voucher (Voucher_Date) " & _
                                  "VALUES (" & _
                                  "'" & Voucher_Date & "') "
        SQLCon.Open()
        SQLCmd = New SqlCommand(strInsert, SQLCon)
        SQLCmd.ExecuteNonQuery()
        SQLCon.Close()
        MsgBox("Successfully Added Into Records!")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
 End Sub
End Class


Public Class FormMain

 Dim sql As New SQLControl

 Private Sub dtpDate_ValueChanged(sender As System.Object, e As System.EventArgs) Handles dtpDate.ValueChanged
    dtpDate.CustomFormat = "dd/MM/yyyy"
 End Sub

 Private Sub btnClearDate_Click(sender As System.Object, e As System.EventArgs) Handles btnClearDate.Click
    dtpDate.CustomFormat = "        "
    dtpDate.Format = DateTimePickerFormat.Custom
 End Sub

 Private Sub btnRecord_Save_Click(sender As System.Object, e As System.EventArgs) Handles btnRecord_Save.Click
        sql.AddToVoucher(dtpDate.Text)
 End Sub

End Class

Use SQL parameters rather than gluing strings together. If `Voucher_Date` is an actual Date type column send a DateTime type to it. No "formatting" is required because DateTime types do not have a format. If `Voucher_Date` is not a Date type it should be — Ňɏssa Pøngjǣrdenlarp, Jan 03 '15 at 15:51
The error message is pretty clear: *Cannot convert string to date*. So you need to scrap the old string concatenation when creating sql queries and start using [parameterized sql queries](http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i). — Bjørn-Roger Kringsjå, Jan 03 '15 at 15:54
..also, A DateTimePicker always has a value, so they are never "blank". There is a Nullable DTP at CodeProject though — Ňɏssa Pøngjǣrdenlarp, Jan 03 '15 at 16:01
As these comments conclude, a DTP has always a value and it cannot be made NULL.so i Prefer Doing some code Work in your save button click(like an if statement) and specify Null in command itself. — akhil kumar, Jan 03 '15 at 16:11
and also please refer this previous post.you may find something.[setting a DTP to Null](http://stackoverflow.com/questions/5947726/set-datetimepicker-value-to-be-null) — akhil kumar, Jan 03 '15 at 16:20

score 0 · Accepted Answer · answered Jan 03 '15 at 22:05

I just did some experimenting and it is true that DateTimePicker (using your code)

dtpDate.CustomFormat = "        "
dtpDate.Format = DateTimePickerFormat.Custom

will still retain the value. But it will have string of spaces in Text property. So here is what happening to your code:

when you have string representation of date, it gets converted into Date implicitly in parameter Voucher_Date As Date
When you have " " - it can't convert to Date, hence the error.

Now, I will not tell you all the wrongs of your code. I will point how to exit situation that you have in your own way of coding. Unfortunately, you better go parametrization route, it is just easier.

In your class declare constant for custom format

Private const _cust_Format AS String = "        "

And then do this

Private Sub btnClearDate_Click(sender As System.Object, e As System.EventArgs) Handles btnClearDate.Click
    dtpDate.CustomFormat = _cust_Format ' <-- so, you always use same number of spaces
    dtpDate.Format = DateTimePickerFormat.Custom
End Sub

Public Sub AddToVoucher(Voucher_Date As DateTime?)
    Try
        Dim strInsert As String = "INSERT INTO Voucher (Voucher_Date) VALUES (@1)"

        SQLCon.Open()
        SQLCmd = New SqlCommand(strInsert, SQLCon)
        ' New code:
        SQLCmd.Parameters.AddWithValue("@1", If(Voucher_Date Is Nothing, DbNull.Value, Voucher_Date)) 
        ' Interestingly, Sql Client doesn't like to set Nothing/Null to value, it likes DBNull.Value. ODP likes both. hmmm...
        SQLCmd.ExecuteNonQuery()
        SQLCon.Close()
        MsgBox("Successfully Added Into Records!")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub 

Private Sub btnRecord_Save_Click(sender As System.Object, e As System.EventArgs) Handles btnRecord_Save.Click
    ' new code:
    Dim d As DateTime? = Nothing
    If dtpDate.Text <> _cust_Format Then
        d = dtpDate.Value   '<-- notice this
    End If  
    sql.AddToVoucher(d)
End Sub

I have not tested it but this should make your code work for now.

It works perfectly great. Under btnRecord_Save_Click, i just change dtpDate.text to dtpDate.CustomFormat. — Nadz, Jan 04 '15 at 05:23
based on your comment, how will i improve my coding? Can you suggest any site or tutorial for proper and decent code construction? I am a newbie programmer (like 2months), I just learn and watch tutorial from youtube. Anyways, i really appreciate for taking time to resolve my problem. Thanks T.S. — Nadz, Jan 04 '15 at 05:27
@Nadz Just read Microsoft guides. Small stuff - naming conventions. Then, using public class member - read on encapsulation. Using `Using` with object implementing `IDisposable` - in your case command and connection. For example, if your `SQLCmd.ExecuteNonQuery()` fails, where do you destroy command and connection? - nowhere. Parametarization of All of your SQL - kills Sql Injection, and also it is better for performance of DB engines. Check Design Patterns. There is a lot. You will learn as you go. I am happy it worked for you. — T.S., Jan 04 '15 at 05:37

conversion from string to type date is not valid

1 Answers1