tableadapter.update

[MickDoev]

Hello Guru's

Environment = VB.NET 2005 & Pervasive SQL 9.5 ADO.NET

Can someone please try to explain this behavour . . . .

In a simplified example, I have single database table (customers) with the columns "customer_code_PK" and "customer_name"

This table is presented in VB.NET 2005 as a single form datagrid (bindingsource/dataset).

In this example, the database table is populated with the following data

customer_code_PK customer_name
SHELL Shell Oil
BP British Petrolium
MOB Mobile


In very simplified terms, code does the following;

sub on form load
fill dataset with database records (select * from customers)
call generate_custom_updateinsertdeletecommands (have also tried commandbuilder here)
end load


sub save_button
bindingsource endedit
tableadapter.update
end save

The datagridview loads the records correctly.



Here is the sequence of operations that produces the bizzar behaviour.

In the editable datagrid;
1. remove the row with PK = MOB -> save_button -> Database correctly updates
2. add a new row with PK = KFC -> save_button -> Database correctly updates
3. remove row with PK = SHELL -> save_button -> Database exception occurs

On this last command, the row SHELL is INSERTED!!, not removed.


However, If I change ths save_button routine to this

save_button
call generate_custom_updateinsertdeletecommands
bindingsource endedit
tableadapter.update
end save


It works correctly !!!!!!!


Is it correct that you would need to "refresh" the sql update commands after every tableadapter.update ?

Many Thanks.

[jmcilhinney]

Unless there's something broken in your project or your system, in which case we probably can't help much anyway, I think that it's safe to say that you're doing something wrong. As we haven't seen what you're actually doing, it's hard to say what might be wrong with it. You've given us an approximation of what you think you're doing, but if you were doing what you think you are then this issue probably wouldn't have arisen. Show us the actual code you're actually executing and tell us the steps you're actually performing.

Apart from that, you talk about a table adapter and also a command builder, which do not go together. I'm guessing that what you actually have is a data adapter, not a table adapter.

On a related note, I'd be inclined use an auto-generated ID as the PK and just make that code column a unique index or the like.

[MickDoev]

Thanks for your reply. Indeed I am refering to a dataadapter as you suggested.

The cust_code is set as a PK because it's being used in a foreign key constraint in another table.

anyway . .

Here is some code that produces the fault I described

As a test, I tried dropping the unique PK contraint and can indeed see that a new record is being inserted instead of being removed when performing "step 3" as per my initial post.

Imports Pervasive.Data.SqlClient
Public Class _test
    Dim connect_string As String = "Database Name=_testdb;Host=_testhost;Password=abc;Persist Security Info=True;User ID=basic"
    Dim psqlconnect As New PsqlConnection(connect_string)
    Dim customertableadapter As New PsqlDataAdapter("Select * from customers", psqlconnect)

    Private Sub customer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Get psql update insert and delete commands
        Call getpsqlcommands()

        'populate datagrid
        Try
            psqlconnect.Open()
            Me.customertableadapter.Fill(Me.ds_customers.customers)
            psqlconnect.Close()
        Catch ex As Exception
            psqlconnect.Close()
            MessageBox.Show("Error loading records :" & ex.ToString, "Problem", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub save_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save_btn.Click
        'save datagrid updates
        Dim rows_updated As Integer = 0
        Try
            Me.Validate()
            Me.CustomerBindingSource.EndEdit()
            psqlconnect.Open()
            rows_updated = Me.customertableadapter.Update(Me.ds_customers.customers)
            psqlconnect.Close()
            If rows_updated = 0 Then
                Beep()
                MessageBox.Show("No changes detected")
            Else
                Beep()
                MessageBox.Show("Updated " & rows_updated & " row(s)")
            End If
        Catch ex As Exception
            psqlconnect.Close()
            MessageBox.Show("Error updating records :" & ex.ToString, "Problem", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub getpsqlcommands()
        'Define update commands
        Dim updatecmd As PsqlCommand
        Dim insertcmd As PsqlCommand
        Dim deletecmd As PsqlCommand
        Dim upparm As PsqlParameter
        Dim delparm As PsqlParameter

        updatecmd = New PsqlCommand("UPDATE customers SET description = ? WHERE cust_code = ?", psqlconnect)
        updatecmd.Parameters.Add("@description", PsqlDbType.VarChar, 50, "description")
        upparm = updatecmd.Parameters.Add("@cust_code", PsqlDbType.VarChar, 5, "cust_code")
        upparm.SourceVersion = DataRowVersion.Original
        customertableadapter.UpdateCommand = updatecmd

        insertcmd = New PsqlCommand("INSERT into customers (cust_code, description) VALUES(?,?)", psqlconnect)
        insertcmd.Parameters.Add("@cust_code", PsqlDbType.VarChar, 5, "cust_code")
        insertcmd.Parameters.Add("@description", PsqlDbType.VarChar, 50, "description")
        customertableadapter.InsertCommand = insertcmd

        deletecmd = New PsqlCommand("DELETE from customers WHERE cust_code = ?", psqlconnect)
        delparm = deletecmd.Parameters.Add("@cust_code", PsqlDbType.VarChar, 5, "cust_code")
        delparm.SourceVersion = DataRowVersion.Original
        customertableadapter.DeleteCommand = deletecmd
    End Sub

[jmcilhinney]

The fact that you want to use a foreign key is not a reason to use the code as a primary key. It's not essential but I use an auto-generated number as an ID in every table, specifically an int or bigint set as an identity in SQL Server. That ID is then used as a foreign key in related tables. I've never used Pervasive so I'm not sure what the equivalent would be, but I'm sure that there would be one.

As for your code, I can't see anything just looking through it. I would suggest that you step through the code line by line and check the contents of your DataTable at each point. I just can't really see how you could be inserting a row that already exists, so you'll need to determine at exactly what stage things go wrong. In theory, the adapter is just going to save changes from the DataTable so that would suggest that the contents of your DataTable are not what you think they are before you call Update.

[MickDoev]

Once again thanks for your input.

I can can confirm that the datatable is correctly being updated at each step.

It's like the customertableadapter.update call is polluted with the previous instructions it has performed.

As per my initial post (and shown in the code below), If I modify my code to include a call to refresh the "getpsqlcommands()" routine before each save, everything works correctly.

I would like to know if it is normal that you would need to refresh these commands before each update?

Imports Pervasive.Data.SqlClient
Public Class _test
    Dim connect_string As String = "Database Name=_testdb;Host=_testhost;Password=abc;Persist Security Info=True;User ID=basic"
    Dim psqlconnect As New PsqlConnection(connect_string)
    Dim customertableadapter As New PsqlDataAdapter("Select * from customers", psqlconnect)

    Private Sub customer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Get psql update insert and delete commands
        Call getpsqlcommands()

        'populate datagrid
        Try
            psqlconnect.Open()
            Me.customertableadapter.Fill(Me.ds_customers.customers)
            psqlconnect.Close()
        Catch ex As Exception
            psqlconnect.Close()
            MessageBox.Show("Error loading records :" & ex.ToString, "Problem", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub save_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save_btn.Click
        'save datagrid updates
        Dim rows_updated As Integer = 0
        Try
            Me.Validate()
            Me.CustomerBindingSource.EndEdit()
            Call getpsqlcommands()                           ' adding this line fixes the problem ************
            psqlconnect.Open()
            rows_updated = Me.customertableadapter.Update(Me.ds_customers.customers)
            psqlconnect.Close()
            If rows_updated = 0 Then
                Beep()
                MessageBox.Show("No changes detected")
            Else
                Beep()
                MessageBox.Show("Updated " & rows_updated & " row(s)")
            End If
        Catch ex As Exception
            psqlconnect.Close()
            MessageBox.Show("Error updating records :" & ex.ToString, "Problem", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub getpsqlcommands()
        'Define update commands
        Dim updatecmd As PsqlCommand
        Dim insertcmd As PsqlCommand
        Dim deletecmd As PsqlCommand
        Dim upparm As PsqlParameter
        Dim delparm As PsqlParameter

        updatecmd = New PsqlCommand("UPDATE customers SET description = ? WHERE cust_code = ?", psqlconnect)
        updatecmd.Parameters.Add("@description", PsqlDbType.VarChar, 50, "description")
        upparm = updatecmd.Parameters.Add("@cust_code", PsqlDbType.VarChar, 5, "cust_code")
        upparm.SourceVersion = DataRowVersion.Original
        customertableadapter.UpdateCommand = updatecmd

        insertcmd = New PsqlCommand("INSERT into customers (cust_code, description) VALUES(?,?)", psqlconnect)
        insertcmd.Parameters.Add("@cust_code", PsqlDbType.VarChar, 5, "cust_code")
        insertcmd.Parameters.Add("@description", PsqlDbType.VarChar, 50, "description")
        customertableadapter.InsertCommand = insertcmd

        deletecmd = New PsqlCommand("DELETE from customers WHERE cust_code = ?", psqlconnect)
        delparm = deletecmd.Parameters.Add("@cust_code", PsqlDbType.VarChar, 5, "cust_code")
        delparm.SourceVersion = DataRowVersion.Original
        customertableadapter.DeleteCommand = deletecmd
    End Sub

[jmcilhinney]

No, it's certainly not normal. I don't know if there's some bug in that ADO.NET provider. I suggest, as I have suggested, that you step through the code and see where things go wrong. Have a look at the adapter and its constituent commands before and after calling getpsqlcommands. Maybe somehow the commands are getting mixed up and the INSERT statement is ending up in the DeleteCommand. If you know what's happening then you can deal with that specifically. That's what debugging is for. If you know what's happening then you can determine where it's happening. If it's an issue with your code then you can fix it. If it's a bug in third-party code then you can notify them. I'd try additional test projects and reinstalling that software first though.

[MickDoev]

This problem was occurring due to a defect with the Pervasive ADO.NET provider (version 3.0.x) which was released with PSQL 9.5

Unfortunately Pervasive no longer provide support and never release a later ADO.NET provider for 9.5

As test, I tried using the ADO.NET provider (version 3.2) designed for PSQL 10 and this fixes the problem.

In relaying this to pervasive, I have been advised that I use this provider at my own risk as it has not been designed or officially tested for use with PSQL 9.5

Not sure where I'll go from here, do I use the tested and recommended version that has a major flaw or the unsupported, untested version that works . . .

Pervasives recommendation is to simply upgrade the database to the latest version - not really an option.

[jmcilhinney]

Is there the option of not using that database-specific connector and rather using OleDb or Odbc with an OLE DB provider or ODBC driver?