0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

VB.NETでDB操作する

Last updated at Posted at 2024-06-15

前提条件

・DBは"MicroSoftSManagimentStudio"を使用
・BookListというテーブルから値を取得する
・データアダプタを使用

(identity) (nvarchar(50)) (nvarchar(50)) (varchar(19))
BookID BookName Author ReadingCompletionDate
1 プラネタリウムのふたご いしいしんじ 2024/02/01
2 medium 相沢渉呼 2024/02/10
3 ハーモニー 伊藤計劃 2024/03/10

SELECT

・データセットでDB接続されている(dsDataSet.xsd)
 ・データセット名:dsDataSet
・取得した値をaspxに表示する(BookIDをViewStateに保持する)

BookList.vb
Dim DBConStr As String = "接続文字列"
Using ta As New dsDataSetTableAdapters.BookListTableAdapter(DBConStr)
  Dim dt As New dsDataSet.BookListDataTable
  ta.Fill(dt)
  If dt.Count <> 0 Then
    With dt(0)
       .BookID = ViewState(BOOK_ID)
       .BookName = lblBookName.Text
       .Author = lblAuthor.Text
    End  With
   End If
End Using


INSERT

トランザクションにする

BookList.vb
Using tran As New Transactions.TransactionScope(Transactions.TransactionScopeOption.Required)
  Using ta As New dsDataSetTableAdapters.BookListTableAdapter(DBConStr)
    Using dt As dsDataSet.BookListDataTable
      Dim dr = dt.NewBookListRow
      With dr
        .BookName = txtBookName.Text
        .Author = txtAutor.Text
        .ReadingCompletionDate = CStr(DateTime.Now)
      End With
      dt.AddBookListRow(dr)
      
      If ta.Update(dt) = 1 Then
        '登録成功時の処理
      Else
        '登録失敗時の処理
      End If
    END Using
  END Using
  tran.Complete()
END Using

UPDATE

BookList.vb
Using tran As New Transactions.TransactionScope(Transactions.TransactionScopeOption.Required)
  Using ta As New dsDataSetTableAdapters.BookListTableAdapter(DBConStr)
    Using dt As dsDataSet.BookListDataTable
      End If
      With dt(0)
        .BookName = txtBookName.Text
        .Author = txtAutor.Text
      End With
      
      If ta.Update(dt) = 1 Then
        '更新成功時の処理
      Else
        '更新失敗時の処理
      End If
    END Using
  END Using
  tran.Complete()
END Using

既存の場合はUPDATE、新規の場合はINSERT

FillやGetDataで取得し、結果によって分岐させる

BookList.vb
Using tran As New Transactions.TransactionScope(Transactions.TransactionScopeOption.Required)
                Using ta As New dsHR10_46TableAdapters.T_VirtualExtensionGroupTableAdapter(CStr(Session(CommonConst.SESSION_DB_CONSTR)))
                    Dim dt As New dsHR10_46.T_VirtualExtensionGroupDataTable

                    If PageDataSet.T_VirtualExtensionGroup.Rows.Count = 0 Then
                        '新規登録
                        Dim newRow As dsHR10_46.T_VirtualExtensionGroupRow = dt.NewT_VirtualExtensionGroupRow()
                        With newRow
                            .VirtualExtensionGroupName = txtVirtualExtensionGroupName.Text
                            .VirtualExtensionGroupNo = CInt(txtVirtualExtensionGroupNo.Text)
                            .ExtensionNumberGroupID = CInt(ddlExtensionNumberGroup.SelectedValue)
                            .PBXCd = ddlPBXCd.SelectedValue
                            .DelFlg = False
                            .EntryID = CInt(Session(CommonConst.SESSION_LOGIN_EMPLOYEE_ID))
                            .EntryDate = regist_date
                            .EditID = 0
                            .EditDate = ""
                            dt.AddT_VirtualExtensionGroupRow(newRow)
                        End With
                        AddMsg("I00001", "登録")
                    Else
                        '更新
                        With PageDataSet.T_VirtualExtensionGroup(0)
                            .VirtualExtensionGroupName = txtVirtualExtensionGroupName.Text
                            .VirtualExtensionGroupNo = CInt(txtVirtualExtensionGroupNo.Text)
                            .ExtensionNumberGroupID = CInt(ddlExtensionNumberGroup.SelectedValue)
                            .PBXCd = ddlPBXCd.SelectedValue
                            .EditID = CInt(Session(CommonConst.SESSION_LOGIN_EMPLOYEE_ID))
                            .EditDate = regist_date
                        End With
                        AddMsg("I00001", "更新")
                    End If

                    ta.Update(dt)

                End Using
                tran.Complete()
            End Using
Dim dtExtentionSettingSchedule As New dsSE02_08.T_ExtentionSettingScheduleDataTable
Using ta As New dsSE02_08TableAdapters.T_ExtentionSettingScheduleTableAdapter(cn_str)
    ' データを取得
    ta.Fill(dtExtentionSettingSchedule, number)

    ' データを操作(行の追加や更新)
    ' 例:既存の行を更新
    If dtExtentionSettingSchedule.Rows.Count > 0 Then
        Dim row As dsSE02_08.T_ExtentionSettingScheduleRow = dtExtentionSettingSchedule(0)
        row.SomeColumn = "新しい値" ' 列の値を変更
    Else
        ' 新しい行を追加する場合
        Dim newRow As dsSE02_08.T_ExtentionSettingScheduleRow = dtExtentionSettingSchedule.NewT_ExtentionSettingScheduleRow()
        newRow.SomeColumn = "新しい値"
        ' 他の列の値もセットする必要があります
        dtExtentionSettingSchedule.AddT_ExtentionSettingScheduleRow(newRow)
    End If

    ' 変更をデータベースに反映
    ta.Update(dtExtentionSettingSchedule)
End Using
0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?