Trying to learn some database techniques - deleting records

[rasinc]

I'd like some help understanding why some of this works. Using the MS Access version of Northwind.mdb and VB2008 in VS2008.

I am pulling the customer records and displaying them on a form in text boxes and one combobox for the country.

I am populating the form with the following code

Public Class frmMain
 
   Private strFilename As String
   Private AccConn As New OleDbConnection
   Private mybindingsource As New BindingSource
   Private AccCmd As OleDbCommand = New OleDbCommand
   Private AccAdapter As OleDbDataAdapter = New OleDbDataAdapter
   Private AccDS As DataSet = New DataSet
 
   Private Sub mnuCustomerView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuCustomerView.Click
      pnlCustRecords.Visible = True
      pnlCustRecords.Enabled = True
 
      'setup toolbar
      tsNew.ToolTipText = "Add a New Customer"
      tsFirst.ToolTipText = "First Customer"
      tsNext.ToolTipText = "Next Customer"
      tsPrevious.ToolTipText = "Previous Customer"
      tsLast.ToolTipText = "Last Customer"
      tsNew.Enabled = True
      tsFirst.Enabled = True
      tsNext.Enabled = True
      tsPrevious.Enabled = True
      tsLast.Enabled = True
 
      'setup buttons
      btnSave.Enabled = False
 
      'get data from database
      AccCmd.CommandText = "SELECT CustomerID, CompanyName, ContactName, " _
                              & "ContactTitle, Address, City, Region, " _
                              & "PostalCode, Country, Phone, Fax " _
                              & "FROM Customers " _
                              & "ORDER BY CompanyName"
 
      AccCmd.Connection = AccConn
 
      AccAdapter.SelectCommand = AccCmd
      AccDS.Clear()
      AccAdapter.Fill(AccDS, "Customers")
 
      mybindingsource.DataSource = AccDS.Tables(0)
 
      'assign values to textboxes
      txtCustID.DataBindings.Add("Text", mybindingsource, "CustomerID")
      txtCompany.DataBindings.Add("Text", mybindingsource, "CompanyName")
      txtContact.DataBindings.Add("Text", mybindingsource, "ContactName")
      txtContTitle.DataBindings.Add("Text", mybindingsource, "ContactTitle")
      txtAddress.DataBindings.Add("Text", mybindingsource, "Address")
      txtCity.DataBindings.Add("Text", mybindingsource, "City")
      txtProv.DataBindings.Add("Text", mybindingsource, "Region")
      txtPostalCode.DataBindings.Add("Text", mybindingsource, "PostalCode")
      cboCountry.DataBindings.Add("Text", mybindingsource, "Country")
      txtTel.DataBindings.Add("Text", mybindingsource, "Phone")
      txtFax.DataBindings.Add("Text", mybindingsource, "Fax")
 
      tsPosition.Text = (1 + mybindingsource.Position).ToString
 
 
   End Sub

So the above code is working fine and pulls the data from the database and allows me to use toolbar buttons to move between records. This was part of what I wanted.

I want to be able to show a record and then delete it. So I put a record into the customer table that shows in my form. I can then delete it with the code below (I needed to add a record that didn't have orders as there is referential integrity set up in this file at the database level).

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click, mnuCustomerDelete.Click
      'deletes current record
      'TODO: manage referential integrity and handle orders and order items
 
      Dim strSQLText As String
      Dim intTotalRecords As Long
 
      strSQLText = "DELETE FROM Customers " _
                              & "WHERE CustomerID = '" & txtCustID.Text & "'"
 
      Dim strSQL As New OleDbCommand(strSQLText, AccConn)
 
      intTotalRecords = strSQL.ExecuteNonQuery
 
      mybindingsource.RemoveCurrent()
      mybindingsource.ResetCurrentItem()
 
 
      mybindingsource.ResetBindings(False)
 
   End Sub

What I found was that I had to remove it from both the database and the bindingsource separately using the code above. This does seem to work but I am missing the concepts of why I need to do both if a bindingsource is used. I thought a binding source binds the data from the database to the fields.

So my main questions are:

1) Is the above delete code correct?
2) If not, what would you suggest as a better alternative?
3) Is there anything else I am missing in this attempt to look at some data and then delete a record?

Thanks for taking the time to read through this rather long post. I appreciate it. rasinc

[leinad31]

Because it is not the job of the database to throw DML "events/notifications" to the front end so they can update their copies of the data.

Following your logic that would be a performance and synchronization nightmare; for each DML a user does all other users (possibly hundreds) will have to be notified by the database despite the fact that changes have not yet been COMMITed and all other users are in turn making their own changes. In your case, rollback notification will also have to be sent and processed by your app (revert item removed earlier); how are you supposed to do that unless DB resends record that wasn't deleted anymore (why not just requery or don't remove uncommited items in the first place)?

It would be best to make up your mind whether you will delete via abstration layer (e.g. object performs its job by sending out relevant DML msg to db), or via direct DML to DB (in which case its your responsibility to refresh/maintain front end list). In both cases, sync is only in one direction.

[rasinc]

Thanks for the response. I am not sure I really understand everything you said but if I rephrase a little, the code I have written does appear to be disconnected from the database. I have pulled data and am able to move through records on a form.

I have been able to delete by sending DML instructions to the database (which I assume is your reference "via direct DML to DB"). So I just need to find an adequate way to requery the database after the delete. And if I am going to use this in a multi-user environment I will have to put in some validation of data in some way to confirm that a user is still working with the correct data and that it hasn't been changed since the original query. I assume if two users query the data and try to delete the same record at the same time, I don't really need to let the user know it's not there anymore anyway but should I?

Have I got this right so far?

[jmcilhinney]

Check it out:

http://msdn.microsoft.com/en-us/library/aa0416cz.aspx

[rasinc]

Thanks again jmcilhinney. This article looks familiar but I believe when I looked at it before I didn't really understand what a DataAdapter and DataSet were. I am starting to understand and use of them more so this information is now more pertinent and timely. It helps to remember the names of these things as well.

So basically in any updating of a database in a multi-user environment, the idea is to check in some way whether the original data is still the same and if so, go ahead and update. If not, then someone else got there first and there has to be a decision made to update/delete or not. I assume this can only be answered by the end users/programmers dependent on the data itself, not based on generally accepted programming guidelines.

I can see a case where users enter two customers but they are the same customer. One person may be recording a sale for that second customer when the manager decides to merge the orders from the second into the first and then remove the second customer. Here is a case where pessimistic concurrency is probably warranted but if all the updates were checking first, then it could be handled if users expected this type of thing to happen. I am really starting to have a better appreciation for the programmers of database applications I've used over the years. And I believe I still have so much more to learn than I originally anticipated considering my "simple" little example above is only handling one table and I haven't even broached the concepts of transactions hitting multiple tables. I now feel like I am starting to eat an elephant! Life was so much less complicated when I focussed on pulling data from databases instead of putting it in.

Thanks for the responses leinad31 and jmcilhinney.

[leinad31]

Pessimistic/optimistic locking is not the only way to control concurrency. You have to do some research and widen your options.

You can control concurrency by table design. Minimize UPDATE, DELETE queries, e.g. table designed to accept multiple info regarding transaction, or a history, then obsolete ones are purged at a set schedule. A good sample is running balance, rather than repeatedly updating inventory of single product record you maintain inventory history (ins/outs) so DML beceomes predominantly SELECT+INSERT rather than SELECT+UPDATE, then at month end starting balances for succeeding month are created to minimize records retrieved by SELECT running balance.

Constraint design (PK/FK) as well as how the steps in transaction are order also make a difference http://www.vbforums.com/showpost.php...51&postcount=4

[rasinc]

For some of the work I am doing right now, I don't have control over the database itself. So I cannot use Fields to mark records as deleted. I had thought of that for work I was going to start myself, where I would have control over the database. Your other post regarding multiple keys is interesting as well and I will think about this when working on my own db structure.

Thanks for the info.