85问答库 > vb2010链接Access数据库如何自定义SQL查询，并将结果显示在datagradview上啊

vb2010链接Access数据库如何自定义SQL查询，并将结果显示在datagradview上啊

2024-12-14 04:27:35

推荐回答（1个）

回答1：

Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb

Public Class Form1
Dim odCnn As OleDbConnection
Dim odApter As OleDbDataAdapter
Dim dataPath As String
Dim passWord As String
Dim cnnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data source="
Dim selectTabel As String
Dim sqlStr As String
Dim dataSet As DataSet
Dim dataTable As DataTable
Dim openFileDlg As OpenFileDialog
Dim dataGridView As DataGridView
Dim CB As OleDbCommandBuilder

Dim myTable As DataTable

Function dlg(ByVal index As Integer) As String
openFileDlg = New OpenFileDialog
With openFileDlg
.Title = "张志晨数据库程序V2012"
.Filter = "Aaccess07-10数据库|*.accdb|access97-03数据库|*.mdb| 所有类型|*.*"
.FilterIndex = index
.ShowDialog()
Return .FileName
End With
End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Label1.Text = dlg(1)
dataPath = Label1.Text
passWord = ""
cnnStr = cnnStr & dataPath & ";JET OLEDB:Database Password=" & passWord
odCnn = New OleDbConnection(cnnStr)
odCnn.Open()

getTableName()

odApter = New OleDbDataAdapter

End Sub
Function getTableName()
' 获取数据表列表
'Type 有："TABLE,VIEW,ACCESS TABLE,SYSTEM TABLE",
Dim dt As DataTable
Dim i As Integer
dt = odCnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
'DataGridView2.DataSource = dt '.......显示表的所有信息
For i = 0 To dt.Rows.Count - 1 '记录数，也就是行数
ComboBox1.Items.Add(dt.Rows(i).Item("TABLE_NAME"))
'显示字段名为　TABLE_NAME　列的行值　就是用户创建的表的名
Next
ComboBox1.Text = ComboBox1.Items(0) '设置第一个表为列表框的值，也就是显示第一个表
MsgBox("数据库中共有" & dt.Rows.Count & "个表")
End Function

Function fillData()
Dim sql As String = "select * from " & selectTabel '建立查询　字符型
''''请注意这里：Dim sql As String = "select * from MyTanle When [年龄]>45 And [学历]=大本"
odApter = New OleDb.OleDbDataAdapter(sql, odCnn) '桥接器
dataSet = New DataSet '内存中的虚拟数据库实例
odApter.Fill(dataSet, "table") '上面的实例被填充
dataGridView.DataSource = dataSet.Tables("table")
dataGridView.AutoResizeColumns()
dataGridView.AutoResizeRows()

'myTable = New DataTable
'odApter.Fill(myTable)
'dataGridView.DataSource = myTable

'获取字段名，并填充列表
getField()

CB = New OleDb.OleDbCommandBuilder(odApter) '用于自动记录你对数据的更改
If CB Is Nothing Then
CB.DataAdapter = New OleDb.OleDbDataAdapter
odApter.DeleteCommand = CB.DataAdapter.DeleteCommand
odApter.InsertCommand = CB.DataAdapter.InsertCommand
odApter.UpdateCommand = CB.DataAdapter.UpdateCommand
End If
'MsgBox("共有记录条数：" & dataSet.Tables("table").Rows.Count, vbOKOnly, "打开成功")
End Function
Function getField()
For i As Integer = 0 To dataSet.Tables("table").Columns.Count - 1
ComboBox2.Items.Add(dataSet.Tables("table").Columns(i).Caption)
Next

End Function

'创建一个网格
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dataGridView = New DataGridView
Me.Controls.Add(dataGridView)
With dataGridView
.Top = 60
.Left = 0
.Width = Me.Width - 20
.Height = Me.Height - 40 - 60
.Visible = True
End With

End Sub

'打开指定的表
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
selectTabel = ComboBox1.Text
fillData()
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
save_sub()
End Sub
'保存数据
Function save_sub()
Dim temptb As DataTable
temptb = dataGridView.DataSource.GetChanges() '获取更改信息
If temptb IsNot Nothing Then '如果有更改
odApter.Update(dataGridView.DataSource) '就保存更改，在实质上是把网格中的数据回发给数据库
MsgBox("您的更改已经回发到数据库！", , "通用数据库程序")
Else
MsgBox("您没有更改任何数据！", , "通用数据库程序")
Exit Function
End If
End Function

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim sr As New DataView
sr = dataSet.Tables("table").DefaultView
sr.RowFilter = ComboBox2.Text & "='" & TextBox1.Text & "'" '这是另一种自定义查询
ListBox1.DataSource = sr
End Sub
End Class

最新问答

关于信息传递在生态系统中的作用的叙述，错误的是

自考英语二怎么考？

我想问下海贼王路飞还有索隆、乌索普这几个人识字吗？文化程度相当于什么？谁教他们这些的？

跪求 QQ炫舞情侣装自定义（透明）图片

如果我的炒股证券账户佣金是1000分之3我该怎么往下调呢？

东风风行换了鼓风机电阻还是不行

envy超级本安装系统是出现“windows无法对计算机进行启动到下一个安装阶段的准备”怎么办？

材料一：唐太宗说：“秦始皇平六国，隋炀帝富有四海，既骄且逸，一朝而败，吾亦何得自骄也？”（1）根据

本人小白，求老司机手把手教如何通信自己给自己传精灵（口袋妖怪xy汉化版到红宝石复刻）两个我都没买正

男生锅盖头斜刘海可以这样么求锅盖头斜刘海图片