Problem with database editing.

[acidflash]

I have a small problem with my program. When I launch the program I have a few parameters that I run to load datasets, and fill listboxes, etc.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Activate()
        ClientsConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\SS Solutions.mdb"
        'ClientsConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =\\IP.OF.COMPUTER.WITH.DATABASE\FOLDER WITH DATABASE\DATABASENAME.mdb" & Application.StartupPath & "\SS Solutions.mdb"
        ClientsConnection.Open()
        ClientsString = "SELECT * FROM Clients"
        ClientsDA = New OleDb.OleDbDataAdapter(ClientsString, ClientsConnection)
        ClientsDA.Fill(ClientsDS, "ClientsMain")
        ClientsInc = 0
        Dim Caption As New ToolTip()
        ' Set up the delays for the ToolTip.
        Caption.AutoPopDelay = 5000
        Caption.InitialDelay = 100
        Caption.ReshowDelay = 500
        ' Force the ToolTip text to be displayed whether or not the form is active.
        Caption.ShowAlways = True
        ' Set up the ToolTip text for the Button and Checkbox.
        Caption.SetToolTip(Me.bAddClient, "Add client")
        Caption.SetToolTip(Me.bRemoveClient, "Remove client")
        Caption.SetToolTip(Me.bSearch, "Search")
        Caption.SetToolTip(Me.bReport, "Report")
        Caption.SetToolTip(Me.bEditUser, "Edit passwords")
        DateOfCall.Text = System.DateTime.Today
        dt = ClientsDS.Tables("ClientsMain")
        lstClients.DataSource = dt
        CalculateAccessPoints()
        CalculateInstalled()
        CalculatePending()
        lblNumOfUsers.Text = dt.Rows.Count
        lblTotalInstalled.Text = TotalInstalledUsers
        lblTotalPending.Text = TotalPendingUsers
        lblNumOfAp.Text = TotalAccessPoints
        lstClients.DisplayMember = "FirstName"
        lstClients.DataBindings.Add("Name", ClientsConnection, "FirstName")
    End Sub

After that, I have an add button that when pressed, adds a new row to the dataset, and then immediately after adds the record to the database, and the record successfully shows up in the lstClients list box.

Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAddClient.Click
        Dim ClientsADDCB As New OleDb.OleDbCommandBuilder(ClientsDA)
        Dim ClientsNewRow As DataRow
        ClientsNewRow = ClientsDS.Tables("ClientsMain").NewRow()
        ClientsNewRow.Item("FirstName") = FirstName.Text
        ClientsNewRow.Item("LastName") = LastName.Text
        ClientsNewRow.Item("Address") = Address.Text
        ClientsNewRow.Item("PhoneNumber") = PhoneNumber.Text
        ClientsNewRow.Item("Email") = Email.Text
        ClientsNewRow.Item("InternetUsername") = UserName.Text
        ClientsNewRow.Item("InternetPassword") = Password.Text
        ClientsNewRow.Item("InternetService") = Service.Text
        ClientsNewRow.Item("MonthlyPayment") = MonthlyPayment.Text
        ClientsNewRow.Item("ClientNum") = ClientNum.Text
        ClientsNewRow.Item("DateOfCall") = DateOfCall.Text
        ClientsNewRow.Item("AccessPoint") = AccessPoint.Text
        ClientsNewRow.Item("CardType") = CardType.Text
        ClientsNewRow.Item("Adapter") = Adapter.Text
        ClientsNewRow.Item("AntennaType") = AntennaType.Text
        ClientsNewRow.Item("AntennaSize") = AntennaSize.Text
        ClientsNewRow.Item("DateToBeInstalled") = ToBeInstalled.Text
        ClientsNewRow.Item("DateOfInstallation") = InstalledOn.Text
        ClientsNewRow.Item("Installed") = Installed.Text
        ClientsDS.Tables("ClientsMain").Rows.Add(ClientsNewRow)
        ClientsDA.Update(ClientsDS, "ClientsMain")
        MsgBox("Client Successfully Added")
        FirstName.Clear()
        LastName.Clear()
        Address.Clear()
        PhoneNumber.Clear()
        Email.Clear()
        UserName.Clear()
        Password.Clear()
        Service.Clear()
        MonthlyPayment.Clear()
        ClientNum.Clear()
        AccessPoint.Clear()
        CardType.Clear()
        Adapter.Clear()
        AntennaType.Clear()
        AntennaSize.Clear()
        ToBeInstalled.Clear()
        InstalledOn.Clear()
        Installed.Clear()
        Me.Refresh()
    End Sub

After that, when I double click on the new added Entry in the listbox (that is now present in the database aswell) and I try to update it, lets say Edit the name or something, and press the save button

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
        Dim RecordUpdate As New OleDb.OleDbCommandBuilder(ClientsDA)
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("FirstName") = FirstName.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("LastName") = LastName.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("Address") = Address.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("PhoneNumber") = PhoneNumber.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("Email") = Email.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("InternetUsername") = UserName.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("InternetPassword") = Password.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("InternetService") = Service.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("MonthlyPayment") = MonthlyPayment.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("DateOfCall") = DateOfCall.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("AccessPoint") = AccessPoint.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("CardType") = CardType.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("Adapter") = Adapter.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("AntennaType") = AntennaType.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("AntennaSize") = AntennaSize.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("ClientNum") = ClientNum.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("DateToBeInstalled") = ToBeInstalled.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("DateOfInstallation") = InstalledOn.Text
        ClientsDS.Tables("ClientsMain").Rows(lstClients.SelectedIndex).Item("Installed") = Installed.Text
        ClientsDA.Update(ClientsDS, "ClientsMain")
        MessageBox.Show("Saved")
        Me.Refresh()
    End Sub

I get the following error: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

I dont know where the problem is, the record is now in the database, and since the listbox (which shows the new entry) is bound to the datatable which is bound to the dataset, it should also be there, why is it doing this? Any help is much appreciated as I am lost.

acidflash

 

[cjard]

Aiiiee.. Look at that code! You could save yourself so much time and effort by using the built in databindings facility. You have a DataSet called ClientsDS in your project, yes? Just do this temporarily, t demo the point:

Add a new Form, call it tempform or something
Open the form
On the Data Menu choose Show Data Sources
A tool panel appears, in it is ClientsDS as a combobox node
Change the combobox node from a datagrid, to a details type
You can also expand the node and change the types of the children so that different types of controls are created, but leave them all as textboxes for now
Drag the node onto the form

You get:
a load of textboxes and labels
a bindingsource which is what your textboxes are connected to the dataset table with
a dataset
a binding navigator (i've always found them useless but keep it for now)

In your main form load() put a line of code to show a New TempForm
Run the project

Now notice that you can click the add button on the navigator, write stuff, press save, it saves, update stuff, press save, it updates..

Let me know if you still get the concurrency violation upon saving using this method. Note by looking at the contents of TempForm's code that it's all nic and clean, very few lines of code - all that stuff you typed, has been done by Visual Studio and is in the TempForm.Designer.vb code file
Later I'll show you some tricks to make things easier/more in line with what you want to do, like asking the bindingsource for the current row of data

 

[acidflash]

cjard,

The concurrency violation doesnt occur when I use the above mentioned method. I assume its because VS is doing the real life editing and appending to the database for me and then calling all the necessary routines and updating all the necessary 'stuff'. I think I will probably rewrite this project over again using this method, but after I submit so that Im not late for my due date. For now though, when I remove the TempForm, yes the concurrency violation comes back. I have also not found it very easy to edit the parameters afterwards using the Data Designer. Anyhow I will write another project in using the Designer later, for now though, has anyone spotted an error ?

cjard,

Note: When I press Show Data Sources, it is empty. There is no ClientsDS in it. I had to manually add it and manually add the database.

acidflash

 

[cjard]

Oh, yes.. if there are no DataSets in your project, nothing will show in DataSources. If you saw the message "Your project has no dataset, click here to add one" that's what happened; it added the dataset, and during the wizard you told the dataset to be based on some database

I assume its because VS is doing the real life editing and appending to the database for me and then calling all the necessary routines and updating all the necessary 'stuff'

You can see what it is "doing" if you click the Show All Files button in the Solution Explorer, and then open the CLientsDS.Designer.vb file

In there is just a bunch of code that reads and writes the database

ConcurrencyExceptions occur when a concurrent aware query doesnt update anything. Consider this:

You create a local record in a datatable that has just 2 columns:
ID
Name

-1,
John

-
You send it to the DB. Maybe the DB calculates a new ID for you, and maybe you get it back in the dataset:

2341,
John

You edit the name. The following query is run:
UPDATE people SET name = 'Bill' WHERE id = 2341 AND name = 'John'

This is an optimistically concurrent query: It hopes that noone else has edited the name John before you. If someone else edited it, then this query updates 0 records and a ConcurrencyViolation exception is thrown

Problems can also come if you have a DB that calculates an ID and it isnt fed back into the dataset. Remeber you inserted -1, John.. The db calculated 2341 for the ID, but if the dataset was never told about it, it is still thinking the id is -1 thus when it tries to update:

UPDATE people SET name = 'Bill' WHERE id = -1 AND name = 'John'

THis also affects 0 records, and the dataset assumes it is because someone else edited the record before you did.. In fact that's not true.


-

In either case, it will likely be a lot faster to write an app based on proper dataset reading and writing but if you want to do it all yourself, take a look at the following methods on your ClientDSTableAdapter:
Insert(...)
Update(...)
Delete(...)


Given that you got a TempForm up and running, reading and writing a DB in probably a few minutes is it really going to impact on your due date?