資料庫管理程式 ( 補充教材 -Part2) 使用 ADO.NET 連結資料庫 ( 自行撰寫程式碼 以實現新增 刪除 修改等功能 )
Private Sub InsertButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertButton.Click ' 宣告相關的 Connection 物件 (OleDbConnection) Dim conn As New OleDbConnection() ' 宣告並設定連接字串, 設定連結資料位置與檔名 conn.connectionstring = "Provider=Microsoft.Jet.OleDB.4.0; Data Source=Northwind.mdb" ' 建立與資料庫之間的連線 ( 呼叫 Open 方法 ) conn.open() ' 宣告並設定 SQL 查詢字串 Dim SQLCommandString As String SQLCommandString = "Insert Into Employees(FirstName,LastName,Title,TitleOfCourtesy,BirthDate,HireDate,City, Address,Country,HomePhone) SQLCommandString = SQLCommandString & " Values ('" & Text_FirstName.Text & "','" & Text_LastName.Text & "','" & Text_Title.Text & "','" & Text_TitleOfCourtesy.Text & "',#" & Text_BirthDate.Text & "#,#" & Text_HireDate.Text & "#,'" & Text_City.Text & "','" & Text_Address.Text & "','" & Text_Country.Text & "','" & Text_HomePhone.Text & "')"
' 建立資料庫執行指令物件 OleDBCommand Dim cmd As OleDbCommand = New OleDbCommand(SQLCommandString, conn) Dim ex As New Exception ' 建立 Exception 變數, 補捉錯誤的訊息種類 ' 攔截例外狀況 Try 'Code that may raise an error. ' 執行資料庫執行指令物件 cmd.executenonquery() Catch ex 'Code to handle the error. (ex 變數可補捉 ) '( 失敗都會執行這一段 ) MessageBox.Show(" 新增資料失敗, 請確認輸入資料的型態是否錯誤 " & Chr(13) & Chr(13) & " 錯誤訊息 :" & ex.message, " 程式執行錯誤 ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Exit Sub Finally 'Code to do any final clean up. '( 無論成功或失敗都會執行這一段 ) End Try MessageBox.Show(" 新增資料成功 ") Call BrowseAllData() End Sub
Private Sub DeleteButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteButton.Click ' 宣告相關的 Connection 物件 (OleDbConnection) Dim conn As New OleDbConnection() ' 宣告並設定連接字串, 設定連結資料位置與檔名 conn.connectionstring = "Provider=Microsoft.Jet.OleDB.4.0; Data Source=Northwind.mdb" ' 建立與資料庫之間的連線 ( 呼叫 Open 方法 ) conn.open() ' 宣告並設定 SQL 查詢字串 Dim SQLCommandString As String SQLCommandString = "Delete * From Employees " SQLCommandString = SQLCommandString & " Where EmployeeID=" & Text_EmployeeID.Text ' 建立資料庫執行指令物件 OleDBCommand Dim cmd As OleDbCommand = New OleDbCommand(SQLCommandString, conn)
Dim ex As New Exception ' 建立 Exception 變數, 補捉錯誤的訊息種類 ' 攔截例外狀況 Try 'Code that may raise an error. ' 執行資料庫執行指令物件 cmd.executenonquery() Catch ex 'Code to handle the error. (ex 變數可補捉 ) '( 失敗都會執行這一段 ) MessageBox.Show(" 刪除資料失敗 " & Chr(13) & Chr(13) & " 錯誤訊息 :" & ex.message, " 程式執行錯誤 ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Exit Sub Finally 'Code to do any final clean up. '( 無論成功或失敗都會執行這一段 ) End Try MessageBox.Show(" 刪除資料成功 ") Call BrowseAllData() End Sub
Private Sub ModifyButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ModifyButton.Click ' 宣告相關的 Connection 物件 (OleDbConnection) Dim conn As New OleDbConnection() ' 宣告並設定連接字串, 設定連結資料位置與檔名 conn.connectionstring = "Provider=Microsoft.Jet.OleDB.4.0; Data Source=Northwind.mdb" ' 建立與資料庫之間的連線 ( 呼叫 Open 方法 ) conn.open() ' 宣告並設定 SQL 查詢字串 Dim SQLCommandString As String SQLCommandString = "Update Employees Set " SQLCommandString = SQLCommandString & "FirstName='" & Text_FirstName.Text & "', LastName='" & Text_LastName.Text & "', Title='" & Text_Title.Text & "', TitleOfCourtesy='" & Text_TitleOfCourtesy.Text & "'" SQLCommandString = SQLCommandString & ", BirthDate='" & Text_BirthDate.Text & "', HireDate='" & Text_HireDate.Text & "'" SQLCommandString = SQLCommandString & ", City='" & Text_City.Text & "', Address='" & Text_Address.Text & "', Country='" & Text_Country.Text & "', HomePhone='" & Text_HomePhone.Text & "'" SQLCommandString = SQLCommandString & " Where EmployeeID=" & Text_EmployeeID.Text ' 建立資料庫執行指令物件 OleDBCommand Dim cmd As OleDbCommand = New OleDbCommand(SQLCommandString, conn)
Dim ex As New Exception ' 建立 Exception 變數, 補捉錯誤的訊息種類 ' 攔截例外狀況 Try 'Code that may raise an error. ' 執行資料庫執行指令物件 cmd.executenonquery() Catch ex 'Code to handle the error. (ex 變數可補捉 ) '( 失敗都會執行這一段 ) MessageBox.Show(" 修改資料失敗 " & Chr(13) & Chr(13) & " 錯誤訊息 :" & ex.message, " 程式執行錯誤 ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Exit Sub Finally 'Code to do any final clean up. '( 無論成功或失敗都會執行這一段 ) End Try MessageBox.Show(" 修改資料成功 ") Call BrowseAllData() End Sub