How to update database using DataRow?

Question

First I have created both the DataSet and MySQLDataAdapter in global. Then in Form_Load event, I query all of my tables like this.

        dS = New DataSet
        dA = New MySqlDataAdapter(myCommand)
        Using sqlConn As New MySqlConnection(connStr)
            myCommand = New MySqlCommand("Select ID, DevCompanyName from developer_name_table; Select ID, DevType from development_type_table; Select ID, Mukim from mukim_table; Select ID, Daerah from daerah_table; Select ID, Negeri from negeri_table; Select * from project_record ORDER BY FloatNo desc limit 1", sqlConn)
            sqlConn.Open()
            MsgBox("Connection open.")
            Dim myCB As New MySqlCommandBuilder(dA)

            dA.SelectCommand = myCommand
            dA.UpdateCommand = myCB.GetUpdateCommand
            dA.InsertCommand = myCB.GetInsertCommand
            dA.DeleteCommand = myCB.GetDeleteCommand

            dA.Fill(dS)
            dA.FillSchema(dS, SchemaType.Source)
            dS.Tables(0).TableName = "developer_name_table"
            dS.Tables(1).TableName = "development_type_table"
            dS.Tables(2).TableName = "mukim_table"
            dS.Tables(3).TableName = "daerah_table"
            dS.Tables(4).TableName = "negeri_table"
            dS.Tables(5).TableName = "project_record"
        End Using

Everything is working fine until I try to add new record into the database like this. The add record code below is under a button click event. First I check if record exits, then I add a new row like this.

        For Each r As DataRow In dS.Tables("project_record").Rows()
            If r.Item("FloatNo") = TextBox1.Text.Trim() Then
                MsgBox("Project exist. Please recheck.")
                Exit Sub
            End If
        Next
        SecurityAdd() 'This is just to fill None if field are empty.
        Dim row = dS.Tables("project_record").NewRow()
        row.Item("FloatNo") = TextBox1.Text.Trim()
        row.Item("DevCompanyName") = ComboBox1.Text.Trim()
        row.Item("DevType") = ComboBox2.Text.Trim()
        row.Item("LotPt") = TextBox2.Text.Trim()
        row.Item("Mukim") = ComboBox3.Text.Trim()
        row.Item("Daerah") = ComboBox4.Text.Trim()
        row.Item("Negeri") = ComboBox5.Text.Trim()
        row.Item("TempReference") = RichTextBox1.Text.Trim()
        row.Item("PermanentNo") = 0
        row.Item("QuotationNo") = 0
        row.Item("InvoiceNo") = 0
        row.Item("Staff") = loggedUser.ToString() 'Just user ID.
        dS.Tables("project_record").Rows.Add(row)
        dA.Update(dS, "project_record")

After the update, I am sure that the DataSet is updated because the second time I click on add record, the messagebox told me that the record existed. But when I check in my database, there are no new record? What am I doing wrong here?

Answer 1

The problem is that you are trying to configure the DataAdapter to work on all 6 tables in the DataSet. If you look at the INSERT or UPDATE commands built, you will see they are an amalgam of all the columns in all the tables. Setting up a DataAdapter to do the grunt work for you works for one table.

I do not know what negeri and the rest translate to, but it looks like only one is a transaction table; the rest, like DevCompanyName appear to be domain/code tables which provide values for the project record table.

Often, some of these are fixed based on business rules, those don't need a way to add/update. Others may well need a way to add new items, but only occassionally. Do those updates by hand so that the DA can be setup and used with the main table the app is focused on. If there is a form or tab for adding Mukim et al, just do the inserts there and refresh.

At a minimum, I'd store those domain table in a separate DataSet just so they dont get in the way, but using just one, you'd configure it something like this:

Using dbcon As New MySqlConnection(MySQLConnStr)

        dsS = New DataSet
        dbcon.Open()

        ' add table 1 - Mukim
        dsS.Tables.Add("Mukim")
        Using cmd As New MySqlCommand("SELECT DeptCode, Descr FROM Department", dbcon)
            dsS.Tables("Mukim").Load(cmd.ExecuteReader())
            cboColors.DataSource = dsS.Tables("Mukim")
            cboColors.DisplayMember = "Descr"    ' ie "Management"
            cboColors.ValueMember = "DeptCode"   ' eg "MGMT"
        End Using

        ' add table 2 - negeri
        ' this table is fixed - new rows are very rare
        Using cmd As New MySqlCommand("SELECT DeptCode, Descr FROM cDepartment", dbcon)
            Dim dt As New DataTable
            dt.Load(cmd.ExecuteReader)
            cboDept.DataSource = dt
            cboDept.DisplayMember = "Descr"             ' "Sommelier"
            cboDept.ValueMember = "DeptCode"            ' "SOMM"
        End Using

        ' etc

        ' main transaction table - project
        ' do this last
        Dim prjcmd = New MySqlCommand(prjSql, dbcon)
        daSample = New MySqlDataAdapter(prjcmd)

        Dim cb As New MySqlCommandBuilder(daSample)
        daSample.SelectCommand = prjcmd
        daSample.InsertCommand = cb.GetInsertCommand
        ' etc

        dsS.Tables.Add("Project")
        daSample.Fill(dsS.Tables("Project"))
        daSample.FillSchema(dsS.Tables("Project"), SchemaType.Source)
    End Using

The code is stores those domain/code tables in the DataSet for those which might change. Instead of an adapter they are filled using a DataReader. Any that are fixed such as "Department" are posted to the related CBO and forgotten. Depending on how often each are actually updated, you could use this method for all of them and just bind to a new table should they get a new row.

The code also uses the DataTables created as the DataSource for the related CBO. I am not sure what your code is doing since it stores the Combobox.Text. If I do update that DataTable with a new entry, it can be coded to show up automatically.

The result is that the DataAdapter knows how to update etc the main Project table and is not confused by these other tables. Then inserting is pretty much as you have it:

    Dim dr = dsS.Tables("Project").NewRow
    dr("Name") = "My New Row"
    dr("Mukim") = cboMukim.SelectedValue     ' MGMT
    ... etc
    dsS.Tables(1).Rows.Add(dr)

    daSample.Update(dsS.Tables("Project"))
    dsS.Tables("Project").AcceptChanges()

    ' See Notes
    Dim rows = daSample.Fill(dsS.Tables("Project"))       ' refresh
    dsS.Tables("Project").Rows.Remove(dr)

1. Since the Mukim CBO is now bound, use the SelectedValue for storing to the project.
2. The DataTable tracks which rows are new, added, deleted etc. After the update, use AcceptChanges to clear those flags.
3. In cases where the table uses an AI PK, the new row will initially be 0. If you refresh the project table, you will get a new record with the db supplied PK. This means there would be 2 records for "My New Row", one with the actual db supplied Id and one as zero. Removing (not deleting!) the new row we added eliminates that.

It is not clear whether item 3 applies. FloatNo could be the PK, in which case you can skip the removal. Also, looping thru "all" the rows to see if "FloatNo" exists is only going to loop thru the one (1) row loaded since the query includes a LIMTI 1 clause.

Not for nothing but you might be able to simplify the UI by using a DataGridView. Rather than comboboxes on the form, Mukim et al could be ComboBox columns so that adding a new row to the table would be done for you. You'd just need to update after it validates and/or they click save.

Answer 2

Here is a simple example to show data insertion using datarow:

Module *GiveAModuleName*

    Function GetTable() As DataTable

     Dim table As DataTable = New DataTable
     table.Columns.Add("ID", GetType(Integer))
     table.Columns.Add("Name", GetType(String))
     table.Columns.Add("Location", GetType(String))
     table.Columns.Add("D.O.B.", GetType(DateTime))
     table.Rows.Add(1, "Koko", "Sydney", 06/12/1990)
     table.Rows.Add(2, "Fido", "Ottawa", 06/12/1990)
     table.Rows.Add(3, "Alex", "California",06/12/1990)
     table.Rows.Add(4, "Charles", "Malta", 06/12/1990)
     table.Rows.Add(7, "Candy", "Delhi", 06/12/1990)
     Return table
    End Function

    Sub Main()
    Dim table As DataTable = GetTable()
    End Sub

End Module

Refer this and re-write the existing code to insert data.