在我的数据库中，使用SQL Server 2014，一个表TableOberge和名为Date-Intype的列Date以及第二个名为Hour-Intype的列Time(7)。通过此查询，我想在DatagridView1中显示已达到其日期和时间的记录...但是我无法正确显示它们。
任何帮助请：
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dbb_Connection()
Using InfoAdapter As New SqlDataAdapter("SELECT * FROM TABLEOBERGE WHERE [DATE_IN] >= CONVERT(date, GETDATE()) AND [HOUR_IN] >= convert(time(0),getDate())", StrCon)
InfoTable = New DataTable
InfoAdapter.Fill(InfoTable)
DataGridView1.DataSource = InfoTable
End Using
End Sub
我的添加记录代码：
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dbb_Connection()
Using Command As New SqlCommand With {.Connection = StrCon}
With Command.Parameters
Command.CommandText = "INSERT INTO [TABLEOBERGE] ([ID], [FIRSTNAME], [PHONE], [ADRESSE], [DATE_OUT], [HOUR_OUT], [DATE_IN], [HOUR_IN]) VALUES (@ID, @FIRSTNAME, @PHONE, @ADRESSE, @DATE_OUT, @HOUR_OUT, @DATE_IN, @HOUR_IN)"
.AddWithValue("@ID", SqlDbType.Int).Value = TextBox1.Text
.AddWithValue("@FIRSTNAME", SqlDbType.NVarChar).Value = TextBox2.Text
.AddWithValue("@PHONE", SqlDbType.NVarChar).Value = TextBox3.Text
.AddWithValue("@ADRESSE", SqlDbType.NVarChar).Value = TextBox4.Text
.AddWithValue("@DATE_OUT", SqlDbType.Date).Value = TextBox5.Text
.AddWithValue("@HOUR_OUT", SqlDbType.Time).Value = TextBox6.Text
.AddWithValue("@DATE_IN", SqlDbType.Date).Value = TextBox7.Text
.AddWithValue("@HOUR_IN", SqlDbType.Time).Value = TextBox8.Text
End With
If StrCon.State = ConnectionState.Closed Then StrCon.Open()
If Command.ExecuteNonQuery() = 1 Then
MsgBox("SUCCED ADD", MsgBoxStyle.MsgBoxRtlReading, "SUCCES")
Else
MsgBox("ERROR FATAL", MsgBoxStyle.MsgBoxRtlReading, "ERROR")
End If
StrCon.Close()
End Using
End Sub
http://www.vbforums.com/showthread.php?862331-Display-record-with-condition-of-date-and-time和这里https://www.developpez.net/forums/d1851524/dotnet/langages / vb-net / afficher-records-conditions-date-time /
我认为您可能会使相等比较器的方向错误。如果您要查找过期日期（例如过去的日期），则该日期应小于当前日期。
SELECT *
FROM TABLEOBERGE
WHERE [DATE_IN] <= CAST(getdate() as Date) AND [HOUR_IN] <= CAST(getDate() AS TIME)
IMO如果您将日期和时间使用单个字段而不是将其分成2DateIn个类型，则设计会更好DateTime2(7)。如果有必要，有很多SQL函数可让您严格根据时间或日期进行过滤。这样可以简化编写上述查询的过程。
SELECT * FROM TABLEOBERGE WHERE [DATE_IN] <= GETDATE()
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句