Vb.net – Data Manipulation (INSERT,UPDATE) using DataSet Object with VB.NET

datasetinsert-updatevb.net

I am newbie at using DataSet. I am writing a program with VB.NET, there I have to select data from one table. Then I have to update about 4 tables and insert to 2 Tables. Which approach will be ok for me? I'm thinking to use DataSet. If anyone can point out that problem , please show me with Sample code to update DataSet. Thanks you all very much.

Best Answer

This is a link you can refer to:

Public Function CreateCommandAndUpdate( _
    ByVal connectionString As String, _
    ByVal queryString As String) As DataSet

    Dim dataSet As DataSet = New DataSet

    Using connection As New OleDbConnection(connectionString)
        connection.Open()
        Dim adapter As New OleDbDataAdapter()

        adapter.SelectCommand = New OleDbCommand( _
            queryString, connection)

        Dim builder As OleDbCommandBuilder = _
            New OleDbCommandBuilder(adapter)

        adapter.Fill(dataSet)

        ' Code to modify the data in the DataSet here. 

        ' Without the OleDbCommandBuilder this line would fail.
        builder.GetUpdateCommand()
        adapter.Update(dataSet)
    End Using
    Return dataSet
End Function

And here's an example on how to delete:

Private Sub btnDeleteUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteUser.Click
Dim reponse_del As Integer
If txtSearch.Text = "" Then
MessageBox.Show("Please type a user name into the text box")
End If

'clear and refill Dataset
OleDAPass.SelectCommand.Parameters("UserName").Value = txtSearch.Text
DS_Pass1.Clear()
OleDAPass.Fill(DS_Pass1)
'no records of the search name
If DS_Pass1.Tables("PwordStore").Rows.Count = 0 Then
MessageBox.Show("Record not found")
ElseIf DS_Pass1.Tables("PwordStore").Rows.Count = 1 Then 'record exists delete it
MessageBox.Show("Are you sure you wish to delete this user?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
If reponse_del = DialogResult.Yes Then
OleDAPass.SelectCommand.Parameters ("UserName").Value = txtSearch.Text
'delete row
DS_Pass1.Tables("PwordStore").Rows(0).Delete()
OleDAPass.Update(DS_Pass1, "PwordStore")
End If
DS_Pass1.PwordStore.AcceptChanges()
DS_Pass1.Clear()
txtSearch.Text = ""
End If
End Sub
Related Topic