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

Conversion From String To Type 'Date' Is Not Valid

提问by Darren

I have 2 datetimepicker that allow user to choose the start date and end date. So after choosing the dates, user must click a button to run the sql query. So the output is filtered based on the chosen date.

我有 2 个日期时间选择器，允许用户选择开始日期和结束日期。所以在选择日期后，用户必须单击一个按钮来运行 sql 查询。所以输出是根据所选日期过滤的。

However there are error when i declare the date as follows (this code below is put under the button click event) :

但是，当我如下声明日期时出现错误（下面的代码放在按钮单击事件下）：

Dim startdate As DateTime = Format(DateTimePicker1.Value, "dd/MM/yyyy")
Dim enddate As DateTime = Format(Microsoft.VisualBasic.DateAdd(DateInterval.Day, 1, DateTimePicker2.Value), "dd/MM/yyyy")

query = "Select * from records where recorddate between '" & startdate & "' and '" & enddate & "'"

The error is here :

错误在这里：

Dim startdate As DateTime = Format(DateTimePicker1.Value, "dd/MM/yyyy")

The error is :

错误是：

Conversion from string "13/02/2016" to type 'Date' is not valid.

The sample date of my records are like this:

我的记录的样本日期是这样的：

12/2/2016 7:28:26 PM

I don't know what i missed here. Please help. Thanks

我不知道我在这里错过了什么。请帮忙。谢谢

采纳答案by ??ssa P?ngj?rdenlarp

There are several issues in your code.

您的代码中有几个问题。

Dim startdate As DateTime = Format(DateTimePicker1.Value, "dd/MM/yyyy")

If you hold the mouse over FormatIntellisense will tell you that it returns a string. Since you cant assign a string result to a variable declared as DateTime, you get the error. If you turn on Option Strictthe compiler will tell you about these.

如果您将鼠标悬停在FormatIntellisense 上，它会告诉您它返回一个字符串。由于您无法将字符串结果分配给声明为 的变量DateTime，因此您会收到错误消息。如果你打开Option Strict编译器会告诉你这些。

Secondly, the query converts the dates to string also:

其次，查询还将日期转换为字符串：

"...between '" & startdate & "' and '" & enddate & "'"

Ticks are not all-purpose SQL delimiters, they are a way of designating text/string data. SQL Parameters are safer and prevent accidental data type changes as well as SQL injection attacks. They also make the code easier to read. If you have a name column in your db, try to INSERT a name like Tim O'Brien, D'Angelo Barksdaleor Betty's Cupcake Factory. The query will crash. SQL Parameters prevent this.

刻度不是通用的 SQL 分隔符，它们是一种指定文本/字符串数据的方式。SQL 参数更安全，可以防止意外的数据类型更改以及SQL 注入攻击。它们还使代码更易于阅读。如果您的数据库中有名称列，请尝试插入一个名称，如Tim O'Brien,D'Angelo Barksdale或Betty's Cupcake Factory。查询会崩溃。SQL 参数阻止了这种情况。

I have no idea which database, so I guessed Access. That doesn't matter because the DB Providers all work much the same:

我不知道是哪个数据库，所以我猜是 Access。这并不重要，因为 DB Providers 的工作方式几乎相同：

' Dim startdate As DateTime = DateTimePicker1.Value
Dim enddate As DateTime = DateTimePicker2.Value.AddDays(1)

Dim SQL = "Select * from records where recorddate between @p1 AND @p2"

Using dbCon As New OleDbConnection(connstr)
    Using cmd As New OleDbCommand(SQL, dbCon)
        dbCon.Open()
        cmd.Parameters.Add("@p1", OleDbType.DBDate).Value = startdate
        cmd.Parameters.Add("@p2", OleDbType.DBDate).Value = DateTimePicker1.Value

        dt = New DataTable
        dt.Load(cmd.ExecuteReader)
    End Using
End Using

NoteThis assumes the column type in the DB is Date and not string. The BETWEENclause is unlikely to work with dates as string. If you want them to act as dates, save them as Date. You do not "need to convert to String in order to use it in SQL". Ever.

注意这假定数据库中的列类型是日期而不是字符串。该BETWEEN子句不太可能将日期作为字符串使用。如果您希望它们充当日期，请将它们另存为Date. 您不需要“需要转换为字符串才能在 SQL 中使用它”。曾经。

• No processing is required for the Date variables. The NET providers know how to pass a DateTimetype to the data base.
• There is no real need to assign the DateTimePicker1.Valueto a new variable, because .Valueisa DateTimetype.
• The Usingblocks declare and create the DB objects, then close and dispose of them to free resources
• When defining the parameter, the code tells it to expect a date (OleDbType.DBDate), then the Value set is an actual date type.

• 不需要对日期变量进行处理。NET 提供程序知道如何将DateTime类型传递给数据库。
• 没有真正需要将 分配DateTimePicker1.Value给一个新变量，因为它.Value是一个DateTime类型。
• 这些Using块声明并创建 DB 对象，然后关闭并处理它们以释放资源
• 在定义参数时，代码告诉它期望一个日期 ( OleDbType.DBDate)，那么 Value 集是一个实际的日期类型。

With Access/OleDB it is important to set the parameter values in the exact same order as they appear in the SQL. OleDBallows named parameters but assigns the values in the order they appear in the SQL.

使用 Access/OleDB 时，按照参数值出现在 SQL 中的完全相同的顺序设置参数值非常重要。 OleDB允许命名参数，但按它们在 SQL 中出现的顺序分配值。

To emphasize that, MSDN (and others) encourage the use of ?in place of other forms ("@p1", "@firstname", "@DateOfBirth"). Especially in queries with more than a few columns, I like "@p1"because the numeral helps index the related column in the SQL and helps make sure they are in order (visually).

为了强调这一点，MSDN（和其他人）鼓励使用?代替其他形式（"@p1"、"@firstname"、"@DateOfBirth"）。特别是在多列的查询中，我喜欢"@p1"因为数字有助于索引 SQL 中的相关列，并有助于确保它们是有序的（视觉上）。

Finally, whether your BETWEEN clause works as you want will depend on several things including that DataType parameter. The dates from the DateTimePickerwill include the time. In order to not to exclude some rows because the time portion is earlier than whatever you got from the DateTimePicker, you will need to use the correct one.

最后，您的 BETWEEN 子句是否如您所愿将取决于包括该 DataType 参数在内的几件事。DateTimePicker遗嘱中的日期包括时间。为了不排除某些行，因为时间部分早于您从 中获得的任何内容DateTimePicker，您需要使用正确的行。

回答by John Pick

DateTimePicker.Value is of type DateTime, which you need to convert to String in order to use it in SQL:

DateTimePicker.Value 是 DateTime 类型，您需要将其转换为 String 才能在 SQL 中使用它：

Dim startdate as String = DateTimePicker1.Value.ToString("dd/MM/yyyy")

However, your database server may not be configured for that format. It is better to use the default formatinstead:

但是，您的数据库服务器可能未针对该格式进行配置。最好使用默认格式：

Dim startdate As String = DateTimePicker1.Value.ToString()
Dim enddate As String = Microsoft.VisualBasic.DateAdd(DateInterval.Day, 1, DateTimePicker2.Value).ToString()
query = "Select * from records where recorddate between '" & startdate & "' and '" & enddate & "'"