PGlabo

« [VB.NET] DataSet内のデータの取り出し方 | メイン | [VB.NET] DataSetの連結 »

2005年10月20日

[VB.NET|Access] レコードの削除・挿入・更新（OleDb）

http://support.microsoft.com/kb/301248/

Imports System.Data.OleDb
    Private Conn As OleDbConnection
    Private DA As OleDbDataAdapter
    Private DS As DataSet

    Private Sub frm_OleDB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim Cmd_Select As OleDbCommand

        Dim Cmd_Update As OleDbCommand

        Dim Cmd_Delete As OleDbCommand

        Dim Cmd_Insert As OleDbCommand

        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\テストDB.mdb"

        Dim SQL_Select As String

        Dim SQL_Update As String

        Dim SQL_Insert As String

        Dim SQL_Delete As String

        Try

            Conn = New OleDbConnection(strConn)

            DA = New OleDbDataAdapter

            '--- SELECT

            SQL_Select = "SELECT * FROM TEST ORDER BY TESTFIELD1 ASC"

            Cmd_Select = New OleDbCommand(SQL_Select, Conn)

            DA.SelectCommand = Cmd_Select

            '--- UPDATE

            SQL_Update = "UPDATE TEST SET TESTFIELD2 = ? WHERE TESTFIELD1 = ?"

            Cmd_Update = New OleDbCommand(SQL_Update, Conn)

            Cmd_Update.Parameters.Add("@Field2", OleDbType.VarChar, 50, "TESTFIELD2")

            Cmd_Update.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")

            DA.UpdateCommand = Cmd_Update

            '--- DELETE

            SQL_Delete = "DELETE FROM TEST WHERE TESTFIELD1 = ?"

            Cmd_Delete = New OleDbCommand(SQL_Delete, Conn)

            Cmd_Delete.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")

            DA.DeleteCommand = Cmd_Delete

            '--- INSERT

            SQL_Insert = "INSERT INTO TEST VALUES (?, ?)"

            Cmd_Insert = New OleDbCommand(SQL_Insert, Conn)

            Cmd_Insert.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")

            Cmd_Insert.Parameters.Add("@Field2", OleDbType.VarChar, 50, "TESTFIELD2")

            DA.InsertCommand = Cmd_Insert

            '--- DataSet

            DS = New DataSet

            DA.Fill(DS, "TEST")

            '--- Bind

            'TextBox1.DataBindings.Add("Text", DS, "TEST.TESTFIELD1")

            'TextBox2.DataBindings.Add("Text", DS, "TEST.TESTFIELD2")

            DataGrid1.SetDataBinding(DS, "TEST")

        Catch ex As Exception

            MessageBox.Show(ex.ToString, "エラーメッセージ")

        Finally

            If Not IsNothing(Conn) Then Conn.Close()

        End Try

    End Sub

'--- 削除ボタン
    Private Sub btnDELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDELETE.Click
        Try
            Dim Pos As Integer 'リスト内のポジション
            Pos = BindingContext(DS, "TEST").Position
            BindingContext(DS, "TEST").RemoveAt(Pos)

            DA.DeleteCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)

            DA.Update(DS, "TEST")

        Catch ex As Exception

            MessageBox.Show(ex.ToString, "エラーメッセージ")

        End Try

    End Sub

 '--- 挿入ボタン
   Private Sub btnINSERT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnINSERT.Click
        Try
            Dim Row As DataRow = DS.Tables("TEST").NewRow

            Row(0) = CType(TextBox1.Text, Integer)

            Row(1) = CType(TextBox2.Text, String)

            DS.Tables("TEST").Rows.Add(Row)

            DA.InsertCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)

            DA.InsertCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)

            DA.Update(DS, "TEST")

        Catch ex As Exception

            MessageBox.Show(ex.ToString, "エラーメッセージ")

        End Try

    End Sub

'--- 更新ボタン
    Private Sub btnUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUPDATE.Click
        Try
            Dim Row As DataRow
            Dim RowCnt As Integer

            RowCnt = 0

            For Each Row In DS.Tables("TEST").Rows

                If DS.Tables("TEST").Rows(RowCnt).Item(0) = CType(TextBox1.Text, Integer) Then

                    DS.Tables("TEST").Rows(RowCnt).Item(1) = CType(TextBox2.Text, String)

                    Exit For

                End If

                RowCnt = RowCnt + 1

            Next

            DA.UpdateCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)

            DA.UpdateCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)

            DA.Update(DS, "TEST")

        Catch ex As Exception

            MessageBox.Show(ex.ToString, "エラーメッセージ")

        End Try

    End Sub

2005 / 10 / 20