1 / 13

VB2005 DataBase Manipulation Command for Access

VB2005 DataBase Manipulation Command for Access. 引用類別定義. I mports 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

pepin
Télécharger la présentation

VB2005 DataBase Manipulation Command for Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VB2005 DataBase Manipulation Commandfor 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.Jet.OLEDB.4.0; Data Source=資料庫來源" • 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物件來移動記憶體資料表之紀錄使用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)

More Related