vb.net 从字符串到日期类型的转换无效

conversion from string to type date is not valid

提问by Nadz

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.

我的 vb 程序有一个名为 dtpDate 的日期时间选择器，2 个名为 btnRecord_Save 和 btnClear_Date 的按钮。

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

我的 sql 有一个名为 Voucher 的表，其中有一个名为 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).

当我的 dtpdate 有一个日期值时，我没有问题，因为它工作得很好，但是如果我通过按 btnClear_date 清除 dtpdate 上的日期，它会给我一个错误（如上面的标题所述）。我想要的只是如果 dtpDate 为空，它将在我的 sql 中存储“NULL”值（列名 = Voucher_date）。

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

这是我的代码供您参考，我已经编辑并排除了不必要的信息。

CLASS CODE (SQLControl)

类代码 (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

采纳答案by T.S.

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

我只是做了一些实验，确实如此DateTimePicker（使用您的代码）

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

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

仍然会保留价值。但它的Text属性中会有一串空格。所以这是你的代码发生的事情：

• when you have string representation of date, it gets converted into Dateimplicitly in parameter Voucher_Date As Date

• When you have " "- it can't convert to Date, hence the error.

• 当您有日期的字符串表示形式时，它会Date在参数中隐式转换为Voucher_Date As Date

• 当您拥有时" "- 它无法转换为Date，因此出现错误。

Now, I will not tell you all the wrongs of your code. I will point how to exit situation that you have in yourown 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.

我还没有测试过，但这应该可以让你的代码现在工作。