PGlabo

« [VB.NET] DataSetの連結 | メイン | [VB.NET] ListViewへ項目の追加 »

2005年10月21日

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

Imports System.Data.Odbc
    Private Conn As OdbcConnection
    Private DA As OdbcDataAdapter
    Private DS As DataSet

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

        Dim Cmd_Select As OdbcCommand

        Dim Cmd_Update As OdbcCommand

        Dim Cmd_Delete As OdbcCommand

        Dim Cmd_Insert As OdbcCommand

        Dim strConn As String = "Driver={Microsoft Access Driver (*.mdb)};DBQ=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 OdbcConnection(strConn)

            DA = New OdbcDataAdapter

            '--- SELECT

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

            Cmd_Select = New OdbcCommand(SQL_Select, Conn)

            DA.SelectCommand = Cmd_Select

            '--- UPDATE

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

            Cmd_Update = New OdbcCommand(SQL_Update, Conn)

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

            Cmd_Update.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")

            DA.UpdateCommand = Cmd_Update

            '--- DELETE

            SQL_Delete = "DELETE FROM TEST WHERE TESTFIELD1 = ?"

            Cmd_Delete = New OdbcCommand(SQL_Delete, Conn)

            Cmd_Delete.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")

            DA.DeleteCommand = Cmd_Delete

            '--- INSERT

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

            Cmd_Insert = New OdbcCommand(SQL_Insert, Conn)

            Cmd_Insert.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")

            Cmd_Insert.Parameters.Add("@Field2", OdbcType.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 / 21