vb.net 使用日期时间选择器从 sql 数据库读取数据

Read data from sql database using datetime picker

提问by Thanzeem

How to read data from database using datetimepicker value. I have a datetimepicker and a datagridview in my form. I want to get data from Sql databse table with the selected datetimepicker value. I try with this code

如何使用 datetimepicker 值从数据库读取数据。我的表单中有一个 datetimepicker 和一个 datagridview。我想使用选定的 datetimepicker 值从 Sql 数据库表中获取数据。我尝试使用此代码

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click
    getConnect()
    Dim editdate As String
    DTPEDITAT.Value= Format(DTPEDITAT.Value, "dd/MM/yyyy")
    editdate = DTPEDITAT.Value
    MessageBox.Show(editdate)
    Try
        Conn.Open()
        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = '" & editdate & "' ORDER BY EMP_NAME ASC"
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, Conn)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "ATTENDANCE")
        Dim dt As DataTable = ds.Tables("ATTENDANCE")
        Dim row As DataRow
        Dim atstat As String
        For Each row In dt.Rows
            If row("AT_STATUS") = 1 Then
                atstat = "Present"
            ElseIf row("AT_STATUS") = 0 Then
                atstat = "Absent"
            ElseIf row("AT_STATUS") = 0.5 Then
                atstat = "Halfday"
            Else
                atstat = "Error"
            End If
            For x As Integer = 0 To ATCEDITGRID.Rows.Count - 1
                ATCEDITGRID.Rows(x).Cells(2).Value = row("EMP_ID")
                ATCEDITGRID.Rows(x).Cells(3).Value = row("EMP_NAME")
                ATCEDITGRID.Rows(x).Cells(0).Value = atstat
                ATCEDITGRID.Rows(x).Cells(1).Value = row("AT_REMARK")
            Next x
        Next row
    Catch ex As SqlException
        MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
    End Try
End Sub

Datagridview not display anything and there is no error...

Datagridview 不显示任何内容，也没有错误...

采纳答案by Thanzeem

I change my code like this

我像这样改变我的代码

Private Sub BTNFIND_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNFIND.Click
    ATCEDITGRID.Rows.Clear()
    getConnect()
    Dim editdate As String
    DTPEDITAT.Value = Format(DTPEDITAT.Value, "dd/MM/yyyy")
    editdate = DTPEDITAT.Value
    Try
        Conn.Open()
        Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = '" & editdate & "' ORDER BY EMP_NAME ASC"
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, Conn)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "ATTENDANCE")
        Dim dt As DataTable = ds.Tables("ATTENDANCE")
        Dim row As DataRow
        Dim atstat As String
        For Each row In dt.Rows
            If row("AT_STATUS") = 1 Then
                atstat = "Present"
            ElseIf row("AT_STATUS") = 0 Then
                atstat = "Absent"
            ElseIf row("AT_STATUS") = 0.5 Then
                atstat = "Halfday"
            Else
                atstat = "Error"
            End If
            Me.ATCEDITGRID.Rows.Add(row("EMP_ID"), row("EMP_NAME"), atstat, row("AT_REMARK"))
        Next row
        ATCEDITGRID.TopLeftHeaderCell.Value = "Sr.No."
        Me.ATCEDITGRID.RowHeadersDefaultCellStyle.Padding = New Padding(3)
        ATCEDITGRID.AllowUserToAddRows = False
        AddRowHeadersEdit()
        Conn.Close()
    Catch ex As SqlException
        MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
    End Try
End Sub

Itz working Good....

Itz 工作良好....

回答by Steve

We see these questions here every day. They stems from the same problem.

我们每天都在这里看到这些问题。它们源于同样的问题。

NEVER USE STRING CONCATENATION TO BUILD SQL QUERIES.

永远不要使用字符串连接来构建 SQL 查询。

It's a big problem. Of course you have already met the first effect. How to convert strings in an acceptable way to the effective data type? You need to solve parsing problems with embedded quotes, correct representation of dates and decimal numbers for the underlying database system. But the second and more subtle side effect of string concatenation is SQL Injection(This is just an instructive link because SQL Injection is a very large topic)

这是一个大问题。当然你已经遇到了第一个效果。如何以可接受的方式将字符串转换为有效的数据类型？您需要通过嵌入引号、正确表示底层数据库系统的日期和十进制数来解决解析问题。但是字符串连接的第二个更微妙的副作用是SQL 注入（这只是一个有指导意义的链接，因为 SQL 注入是一个非常大的话题）

To solve this kind of problems the only accepted way is to use PARAMETERS.
This means that it is the database engine that solves in an efficient way the question, you need a string with parameter placeholders (the @something) and a collection of Parameters with the exact data type for the value of the parameter.

要解决此类问题，唯一可接受的方法是使用参数。
这意味着它是以有效方式解决问题的数据库引擎，您需要一个带有参数占位符（@something）的字符串和一个参数集合，该集合具有参数值的确切数据类型。

So your code should change in this way

所以你的代码应该以这种方式改变

Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK " + 
                       "FROM ATTENDANCE WHERE AT_DATE = @editdate " + 
                       "ORDER BY EMP_NAME ASC"
Using con = new SqlConnection("constring_here")
    con.Open()
    Using cmd = new SqlCommand(strSQL, con)
        cmd.Parameters.AddWithValue("@editdate", DTPEDITAT.Value)
        ' do whatever you want with the command '
        ' like ExecuteReader or use a DataAdapter to fill datasets'
    End Using
End Using

回答by Luis Quijada

My NOT RECOMMENDEDsolution:

我不推荐的解决方案：

Remove the parenthesis and %characters. Put a space just before the ORDER(though it is not the cause of your syntax error).

删除括号和%字符。在之前放置一个空格ORDER（尽管它不是导致语法错误的原因）。

Fix the select in this way:

以这种方式修复选择：

"SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = '" & _
editDate.ToString("yyyy-MM-dd hh:mm:ss") & _
"' ORDER BY EMP_NAME ASC"

---

---

What I do recommmendyou is to learn to use SQL Parameters.

什么我不recommmend你是要学会使用SQL参数。

The query should looks then like this (note the @editDate parameter place-holder inside):

查询应该看起来像这样（注意里面的@editDate 参数占位符）：

"SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = @editDate ORDER BY EMP_NAME ASC".

"SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = @editDate ORDER BY EMP_NAME ASC".

Then you need just to add the parameters to the SqlCommand. The easiest way is to do that is to use SqlParameterCollection.AddWithValue.

然后你只需要将参数添加到SqlCommand. 最简单的方法是使用SqlParameterCollection.AddWithValue。

yourSqlCommand.Parameters.AddWithValue("@editDate", editDate)

yourSqlCommand.Parameters.AddWithValue("@editDate", editDate)

A complete sample:

一个完整的样本：

    Dim editDate As Date = DTPEDITAT.Value

    Using conn As New SqlConnection(YOUR_CONNECTION_STRING_HERE)
        Using cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = @editDate ORDER BY EMP_NAME ASC"
            cmd.Parameters.AddWithValue("@editDate", editDate)

            adapter.SelectCommand = cmd
            adapter.Fill(ds)

            For Each row As DataRow In ds.Tables(0).Rows 
                [do whatever with the result]
            Next

        End Using
        conn.Close()
    End Using

From MSDNabout Sql Parameters

来自MSDN关于 Sql 参数

Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against "SQL injection"attacks, in which an attacker inserts a command into an SQL statement that compromises security on the server. In addition to the security benefits, parameterized commands provide a convenient method for organizing values passed to a data source.

命令对象使用参数将值传递给 SQL 语句或存储过程，提供类型检查和验证。与命令文本不同，参数输入被视为文字值，而不是可执行代码。这有助于防范“SQL 注入”攻击，在这种攻击中，攻击者将命令插入 SQL 语句中，从而危及服务器的安全性。除了安全优势之外，参数化命令还提供了一种方便的方法来组织传递给数据源的值。

回答by spajce

Try move a one space near at your "%)and I corrected the query. Try to figure out.

尝试在您附近移动一个空格"%)，我更正了查询。揣摩。

Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS, " + _
     "AT_REMARK FROM ATTENDANCE " + _ 
     "WHERE Convert(Varchar(10), AT_DATE, 23) = " + _
     "('" & editdate.ToString("yyyy-MM-dd") & "') ORDER BY EMP_NAME ASC"

回答by Sinoo Yassine

this is my code its working

这是我的代码，它的工作原理

oldbc.CommandText = "select * from recette where " & ComboBox1.Text & " between #" & DateTimePicker1.Text & "# and #" & DateTimePicker2.Text & "#"

oldbc.CommandText = "select * from recette where " & ComboBox1.Text & " between #" & DateTimePicker1.Text & "# and #" & DateTimePicker2.Text & "#"