VB2005 DataBase Manipulation Command for Access
description
Transcript of VB2005 DataBase Manipulation Command for Access
VB2005 DataBase Manipulation Command
for Access
引用類別定義• Imports System.Data.OleDb
• Imports System.Data.SqlClient
• Imports System.Data.Odbc
• Imports System
宣告公用物件變數• Public acCon As OleDbConnection
• Public acCmd As OleDbCommand
連線• Dim str As String
• str = “Provider=Microsoft.JETOLEDB.4.0; Data Source= 資料庫來源”
• str = “Provider=Microsoft.ACE.OLEDB.12.0; Data Source= 資料庫來源” ---2016 Access
• acCon = New OleDbConnection(str)
• If Not acCon.State = ConnectionState.Open Then acCon.Open()
建立 SQL 指令集物件• Dim str As String
• str=“SQL 指令”• acCmd = New OleDbCommand(str, acCon)
開啟紀錄• Dim dr As OleDbDataReader• acCmd = New OleDbCommand(str, acCon)• dr = acCmd.ExecuteReader()• 相關屬性與方法 :
– FieldCount 欄數– HasRows 有無紀錄– Close() 關閉物件– GetName(i) 取得欄名– GetValue(i) 取得資料– Read() 讀取一筆紀錄
循序輸出紀錄• While dr.Read()
• 讀取欄位資料 dr.Item(“ 欄名” )
• 或是 dr.Item(i) i 為欄位序號• End While
執行 SQL 指令• Dim str As String =“SQL 指令”• acCmd = New OleDbCommand(str, acCon)
acCmd.ExecuteNonQuery()
搭配 DataGridView 顯示資料• Dim ds As New DataSet• Dim da As New OleDbDataAdapter(str, acCon)• da.Fill(ds, “ 資料表名 ")• Dim tbl AS DataTable• tbl = ds.Tables (“ 資料表名 ")• Dim DaView As DataView • daView = tbl.DefaultView
• DataGridView1.DataSource = daView
DataGridView – 過濾資料• Dim strFilter As String
• strFilter = “ 欄名 =‘” & Value & “’”
• daView.RowFilter = strFilter
• 取消過濾• daView. RowFilter = Nothing
DataGridView – 過濾資料• Dim strSort As String
• strSort = “ 欄名 DESC|ASC”
• daView.Sort = strSort
• 取消排序• daView.Sort = Nothing
使用 DataTable 物件 : TBL
• 計算欄數 : TBL.Columns.Count
• 取得欄名 : TBL.Coumns(i).ColumnName
• 計算紀錄數 : TBL.Rows.Count
• 取得欄值 : TBL.Rows(i).Item(“ 欄名” ), 或是 TBL.Rows(i).Item(j)
使用 BindingManagerBase 物件來移動記憶體資料表之紀錄
• Dim BM As BindingManagerbase• BM = Me.BindingContext(DS, TableName)• 附屬方法
– Position 目前位置 ( 0 ~ Count -1)– Count 筆數– AddNew() 新增– RemoveAt(n) 刪除第 n 筆 , 從 0 起算– EndCurrentEdit() 結束目前編輯 , 將控制項資料回寫 DS
• 控制項須搭配下列指令來連結資料欄位– Dim myBind As Binding = New Binding(“Text”, DS,
“TableName,FieldName)– 控至項名 .DataBindings.Add(myBind)