VB2005 DataBase Manipulation Command for Access

Presentation on theme: "VB2005 DataBase Manipulation Command for Access"— Presentation transcript:

1 VB2005 DataBase Manipulation Command for Access

2 引用類別定義 Imports System.Data.OleDb Imports System.Data.SqlClient
Imports System.Data.Odbc Imports System

3 宣告公用物件變數 Public acCon As OleDbConnection Public acCmd As OleDbCommand

4 連線 Dim str As String str = “Provider=Microsoft.JETOLEDB.4.0; Data Source=資料庫來源” str = “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=資料庫來源” Access acCon = New OleDbConnection(str) If Not acCon.State = ConnectionState.Open Then acCon.Open()

5 建立SQL指令集物件 Dim str As String str=“SQL 指令”
acCmd = New OleDbCommand(str, acCon)

6 開啟紀錄 Dim dr As OleDbDataReader acCmd = New OleDbCommand(str, acCon)
dr = acCmd.ExecuteReader() 相關屬性與方法: FieldCount 欄數 HasRows 有無紀錄 Close() 關閉物件 GetName(i) 取得欄名 GetValue(i) 取得資料 Read() 讀取一筆紀錄

7 循序輸出紀錄 While dr.Read() 讀取欄位資料 dr.Item(“欄名”) 或是 dr.Item(i) i為欄位序號
End While

8 執行SQL指令 Dim str As String =“SQL 指令”
acCmd = New OleDbCommand(str, acCon) acCmd.ExecuteNonQuery()

9 搭配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

10 DataGridView – 過濾資料 Dim strFilter As String
strFilter = “欄名 =‘” & Value & “’” daView.RowFilter = strFilter 取消過濾 daView. RowFilter = Nothing

11 DataGridView – 過濾資料 Dim strSort As String strSort = “欄名 DESC|ASC”
daView.Sort = strSort 取消排序 daView.Sort = Nothing

12 使用DataTable物件: TBL 計算欄數: TBL.Columns.Count
取得欄名: TBL.Coumns(i).ColumnName 計算紀錄數: TBL.Rows.Count 取得欄值: TBL.Rows(i).Item(“欄名”), 或是TBL.Rows(i).Item(j)

13 使用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)