SQL新增修改刪除

新增

INSERT INTO "表格名" ("欄位1", "欄位2", ...) VALUES ("值1", "值2", ...); 

範例1：使用Connection、SQL字串、Command，執行ExecuteNonQuery()

Dim con As OleDb.OleDbConnection= New OleDb.OleDbConnection(strConn)

Dim strSQL As String = "INSERT INTO 資料表1(學號,姓名,班級,座號) values('0101303','林老母',301,3);"

Dim com As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSQL, con)

Try

      con.Open()

      com.ExecuteNonQuery()

      Label6.Text = "新增成功"

Catch ex As Exception

      Label6.Text = "發生錯誤：" & ex.Message

 End Try

範例2：使用Connection、SQL字串、Adapter，執行InsertCommand.ExecuteNonQuery()

Dim con As OleDb.OleDbConnection= New OleDb.OleDbConnection(strConn)

Dim strSQL As String = "INSERT INTO 資料表1(學號,姓名,班級,座號) values('0101303','林老母',301,3);"

Dim adtp As OleDb.OleDbAdapter = New OleDb.OleDbAdapter(strSQL, con)

Try

      con.Open()

      adtp.InsertCommand.ExecuteNonQuery()

      Label6.Text = "新增成功"

Catch ex As Exception

      Label6.Text = "發生錯誤：" & ex.Message

 End Try

修改：使用同樣物件，只是SQL字串內容換成update ... set ... where

UPDATE "表格名" SET "欄位1" = [新值] WHERE "條件"; 

刪除：使用同樣物件，只是SQL字串內容換成delete from ... where

DELETE FROM "表格名" WHERE "條件"; 

既然都是執行SQL語法，那就建立一個類別來呼叫使用吧。

建立執行SQL的Command物件(類別檔)

類別檔：ClassExecCmd

'類別(物件)屬性 Property

    Public ErrOccur As Boolean '執行錯誤

    Public ErrMsg As String '錯誤訊息

    '類別方法 Method，名稱：execSQL

    Sub execSQL(ByVal tbStr As String)

        Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection(DBSchoolcnStr)

        Dim cmd As New OleDb.OleDbCommand(tbStr, con)

        Try

            con.Open()

            cmd.ExecuteNonQuery()

            con.Close()

            ErrOccur = False

            ErrMsg = ""

        Catch ex As Exception

            ErrOccur = True

            ErrMsg = "物件ClassExecCmd.execSQL方法，執行OleDb.OleDbCommand失敗，"

            ErrMsg &= "語法：" & tbStr & "。錯誤訊息：" & ex.Message

        End Try

    End Sub

請自行建立以Adapter執行SQL的類別(物件)