Question

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

自带的“添加查询”不能满足自定义的如联合查询等，如何将查出来的数据显示到datagradwiew上呢

Answer 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