Environment = VB.NET 2005 & Pervasive SQL 9.5 ADO.NET
Can someone please try to explain this behavour . . . .
I have made a simple example to demonstrate the problem I am experiencing.
I have single database table (customers) with the columns "cust_code" and "description"
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
cust_code description
SHELL Shell Oil
BP British Petrolium
MOB Mobile
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 into the Database !!, not removed.
Here is the code
[CODE]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[/CODE]
If I modify the code to include a call to the "getsqlcommands" before each save (as below), it works correcly!
Is it right that you need to "refresh" the sqlupdate commands before each update?
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