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

Question

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.

Answer 1

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

Answer 2

You should work with data adapter - best way to learn - look for some examples in MSDN or Google it, then - try to solve your problem - then - if you encounter a problem - post it here.

in basics: you need a connection, a command, a dataadapter and a dataset.

you use datadapter fill to get the data from DB to the dataset, and update to put the data back to the database. you need to tell the data adapter how to do that, or use a wizard to auto-generate the commands (INSERT, UPDATE ect...)