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?
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)
- Since the Mukim CBO is now bound, use the
SelectedValuefor storing to the project.
DataTabletracks which rows are new, added, deleted etc. After the update, use
AcceptChangesto clear those flags.
- 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.