vb.net 在vb.net中从access数据库检索数据到数据网格视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16314070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieve data from access database to data grid view in vb.net
提问by Thanzeem
I try to read a data from access database with specific date to datagrid view in VB.NET. I use a datetime picker for that. Below code i used for retreive data. But i press the find button. nothing display in datagrid view. this is the code
我尝试从具有特定日期的访问数据库中读取数据到 VB.NET 中的数据网格视图。我为此使用了日期时间选择器。下面是我用于检索数据的代码。但是我按下了查找按钮。数据网格视图中不显示任何内容。这是代码
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")
'MessageBox.Show(DTPEDITAT.Value)
'editdate = DTPEDITAT.Value
Try
Conn.Open()
Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK FROM ATTENDANCE WHERE AT_DATE = " & DTPEDITAT.Value & " ORDER BY EMP_NAME ASC"
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(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
'MessageBox.Show(row("EMP_ID"))
'MessageBox.Show(row("EMP_NAME"))
'MessageBox.Show(atstat)
'MessageBox.Show(row("AT_REMARK"))
Me.ATCEDITGRID.Rows.Add(row("EMP_ID"))
Me.ATCEDITGRID.Rows.Add(row("EMP_NAME"))
Me.ATCEDITGRID.Rows.Add(atstat)
Me.ATCEDITGRID.Rows.Add(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 OleDb.OleDbException
MsgBox(ex.Message, MsgBoxStyle.Critical, "DB Error")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub
please check the code. and give me the solution
请检查代码。并给我解决方案
回答by Steve
When you build a sql command concatenating strings you expose your code to two big problems.
当您构建连接字符串的 sql 命令时，您将代码暴露给两个大问题。
- First, you could write the wrong syntax for particular datatype on a particular database
- Second, you expose your code to Sql Injection attacks from a malicious user (well, here should not be a concern, but....)
- 首先，您可以为特定数据库上的特定数据类型编写错误的语法
- 其次，您将您的代码暴露给来自恶意用户的 Sql 注入攻击（好吧，这里不应该是一个问题，但是....）
Instead build a parametrized query and let the framework code interact with the database engine in a secure and correct way
而是构建一个参数化查询，让框架代码以安全正确的方式与数据库引擎交互
So you should write:
所以你应该写：
Conn.Open()
Dim strSQL As String = "SELECT EMP_ID,EMP_NAME,AT_STATUS,AT_REMARK " & _
"FROM ATTENDANCE WHERE AT_DATE = ? ORDER BY EMP_NAME ASC"
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(strSQL, Conn)
da.SelectCommand.Parameters.AddWithValue("@p1", DTPEDITAT.Value)
Dim ds As DataSet = New DataSet
da.Fill(ds, "ATTENDANCE")
Now looking at your code you could have another potential error in the way you treat the value of the field AT_STATUS. From your code it seems that the field is of type decimal or double. If your code compile then you have probably set the OPTION STRICTto OFF. It is a better practice to keep this option ON and convert appropriately the datatypes
现在查看您的代码，您在处理字段 AT_STATUS 的值的方式上可能存在另一个潜在错误。从您的代码看来，该字段的类型是十进制或双精度。如果您的代码编译，那么您可能已将OPTION STRICT设置为 OFF。保持此选项为 ON 并适当转换数据类型是更好的做法
Dim dt As DataTable = ds.Tables("ATTENDANCE")
Dim row As DataRow
Dim atstat As String
Dim status as Decimal
For Each row In dt.Rows
status = Convert.ToDecimal(row("AT_STATUS"))
If status = 1 Then
atstat = "Present"
ElseIf status = 0 Then
atstat = "Absent"
ElseIf status = 0.5 Then
atstat = "Halfday"
Else
atstat = "Error"
End If
......
Next row
