K
k
-----------------------------------------------------------------
The PK in the oledbdeletecommand.parameters collection *appears* to have the
appropriate Primary Key to look for to delete, so I'm not sure why it's not
able to find the record to delete... After reloading the master form, the
datarow deletes and the dataadapter.update(me.ds) goes just fine.
-----------------------------------------------------------------
I have two forms in a master/detail relationship, with the master form's
dataset shared with the detail form. I use the form's currencymanagers to
synch the detail record, add a new row, etc. The database is Access, using
the Jet driver.
After creating a new record with "cm.AddNew()" in the detail form, the
following gives me the "affected 0 records" error:
1. update the database with the dataadapter via
"masterForm.da.update(masterForm.ds)"
2. use "@@Identity" to get the database's primary key just generated on
this database connection for my new row, and alter our dataset row's PK
field to conform with database by using "drCm.Item("PK") = newPK"
3. call cm.EndCurrentEdit() & drCm.acceptChanges() to set the row's state
from "Modified" to "Unchanged".
4. back in the master form, delete the new record via "dr.Delete()"
////////////////// This throws the Exception //////////////////
5. attempt to synch the datarow delete w/ the database by using the
dataadapter via:
"me.da.update(me.ds)"
Here are some code snippets:
mngUsers.vb
-------------------------------------
Private cmEmployees As CurrencyManager
Private Sub mngUsers_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.cmEmployees = CType(Me.BindingContext(Me.DsUsers1.Employees),
CurrencyManager)
'// some code snipped from here
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNew.Click
Dim myUser As New mngUsersNewEdit(mngUsersNewEdit.formAction.newUser)
Me.AddOwnedForm(myUser)
myUser.Show()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDelete.Click
Dim drCm As DataRow
drCm = CType(Me.cmEmployees.Current, DataRowView).Row
Try
drCm.Delete()
Me.OleDbDataAdapter1_Users.Update(Me.DsUsers1.Employees)
Catch ex As Exception
MessageBox.Show(ex.Message + vbCrLf + vbCrLf + ex.StackTrace)
End Try
End Sub
mngUsersNewEdit.vb
-----------------------------------
Private m_actionToTake As formAction
Private m_parent As mngUsers
Private cmEmployees As CurrencyManager
Private cmOwner As CurrencyManager
Private Sub mngUsersNewEdit_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.m_parent = Me.Owner
Me.cmEmployees =
CType(Me.BindingContext(Me.m_parent.DsUsers1.Employees), CurrencyManager)
'// If appropriate, Synchronize our Form to the record currently
selected in parent form
If m_actionToTake = formAction.editUser Then
'// set a currency manager for the owner form to find Form1's
current record position
cmOwner = CType(CType(Me.Owner,
masterdetailDeleteTest.mngUsers).BindingContext(Me.m_parent.DsUsers1.Employe
es), CurrencyManager)
'// synchronize the table record positions between form1 and form2
Me.cmEmployees.Position = Me.cmOwner.Position
Else
'// create a new itemOption
cmEmployees.AddNew()
'// set up default data so avoid DBnull conflicts
'// casting the currency manager and accessing it's 'row' object
allows us to access an item by column name.
Dim drNew As DataRow = CType(cmEmployees.Current, DataRowView).Row
drNew.Item("IsActive") = True
drNew.Item("Birthdate") = "1/1/1970"
End If
'// Set our databindings
Me.tbEmployeeID.DataBindings.Add("text", Me.m_parent.DsUsers1.Employees,
"EmployeeID")
Me.tbFirstName.DataBindings.Add("text", Me.m_parent.DsUsers1.Employees,
"FirstName")
'// etc. w/ the databinding
End Sub
Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnOK.Click
Me.cmEmployees.EndCurrentEdit()
Try
Me.m_parent.OleDbDataAdapter1_Users.Update(Me.m_parent.DsUsers1)
If Me.m_actionToTake = formAction.newUser Then
'// To avoid having the autogenerated primary key in dataset out
of synch with database,
'// retrieve the newly created database PK and update our
dataset's PK with it.
'// Include a variable and a command to retrieve the
autogenerated primary key value
'// (identity value) from the Access database.
'// cf.
ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconretrievingidentityorautonumber
values.htm
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("SELECT
@@IDENTITY", Me.m_parent.OleDbConnection1)
'// Retrieve the identity value.
newID = CInt(idCMD.ExecuteScalar())
'// Assign retrieved autonumber to our new row's primary key.
Dim drCm As DataRow = CType(Me.cmEmployees.Current,
DataRowView).Row '// datarow to inspect our current row
drCm.Item("EmployeeID") = newID '// push DB's new PK to
row's "Current", "Original", and "Default" values
'// Roll our PK value change into the row's "original version."
'// The deleteCommand uses the original row PK values to do the
deletion,
'// which may be out of synch with the database. If so, the
dataset would throw an
'// exception when the database (looking for the wrong PK) could
still not find the row to delete.
cmEmployees.EndCurrentEdit() '// allow this row's changes
to remain
drCm.AcceptChanges() '// set row's state from "Modified" to
"Unchanged" to prevent unneccessary labor
End If
Catch ex As Exception
MessageBox.Show(ex.Message + vbcrlf + vbcrlf + ex.StackTrace)
End Try
Me.Owner.Focus()
Me.Close()
End Sub
The PK in the oledbdeletecommand.parameters collection *appears* to have the
appropriate Primary Key to look for to delete, so I'm not sure why it's not
able to find the record to delete... After reloading the master form, the
datarow deletes and the dataadapter.update(me.ds) goes just fine.
-----------------------------------------------------------------
I have two forms in a master/detail relationship, with the master form's
dataset shared with the detail form. I use the form's currencymanagers to
synch the detail record, add a new row, etc. The database is Access, using
the Jet driver.
After creating a new record with "cm.AddNew()" in the detail form, the
following gives me the "affected 0 records" error:
1. update the database with the dataadapter via
"masterForm.da.update(masterForm.ds)"
2. use "@@Identity" to get the database's primary key just generated on
this database connection for my new row, and alter our dataset row's PK
field to conform with database by using "drCm.Item("PK") = newPK"
3. call cm.EndCurrentEdit() & drCm.acceptChanges() to set the row's state
from "Modified" to "Unchanged".
4. back in the master form, delete the new record via "dr.Delete()"
////////////////// This throws the Exception //////////////////
5. attempt to synch the datarow delete w/ the database by using the
dataadapter via:
"me.da.update(me.ds)"
Here are some code snippets:
mngUsers.vb
-------------------------------------
Private cmEmployees As CurrencyManager
Private Sub mngUsers_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.cmEmployees = CType(Me.BindingContext(Me.DsUsers1.Employees),
CurrencyManager)
'// some code snipped from here
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNew.Click
Dim myUser As New mngUsersNewEdit(mngUsersNewEdit.formAction.newUser)
Me.AddOwnedForm(myUser)
myUser.Show()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDelete.Click
Dim drCm As DataRow
drCm = CType(Me.cmEmployees.Current, DataRowView).Row
Try
drCm.Delete()
Me.OleDbDataAdapter1_Users.Update(Me.DsUsers1.Employees)
Catch ex As Exception
MessageBox.Show(ex.Message + vbCrLf + vbCrLf + ex.StackTrace)
End Try
End Sub
mngUsersNewEdit.vb
-----------------------------------
Private m_actionToTake As formAction
Private m_parent As mngUsers
Private cmEmployees As CurrencyManager
Private cmOwner As CurrencyManager
Private Sub mngUsersNewEdit_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.m_parent = Me.Owner
Me.cmEmployees =
CType(Me.BindingContext(Me.m_parent.DsUsers1.Employees), CurrencyManager)
'// If appropriate, Synchronize our Form to the record currently
selected in parent form
If m_actionToTake = formAction.editUser Then
'// set a currency manager for the owner form to find Form1's
current record position
cmOwner = CType(CType(Me.Owner,
masterdetailDeleteTest.mngUsers).BindingContext(Me.m_parent.DsUsers1.Employe
es), CurrencyManager)
'// synchronize the table record positions between form1 and form2
Me.cmEmployees.Position = Me.cmOwner.Position
Else
'// create a new itemOption
cmEmployees.AddNew()
'// set up default data so avoid DBnull conflicts
'// casting the currency manager and accessing it's 'row' object
allows us to access an item by column name.
Dim drNew As DataRow = CType(cmEmployees.Current, DataRowView).Row
drNew.Item("IsActive") = True
drNew.Item("Birthdate") = "1/1/1970"
End If
'// Set our databindings
Me.tbEmployeeID.DataBindings.Add("text", Me.m_parent.DsUsers1.Employees,
"EmployeeID")
Me.tbFirstName.DataBindings.Add("text", Me.m_parent.DsUsers1.Employees,
"FirstName")
'// etc. w/ the databinding
End Sub
Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnOK.Click
Me.cmEmployees.EndCurrentEdit()
Try
Me.m_parent.OleDbDataAdapter1_Users.Update(Me.m_parent.DsUsers1)
If Me.m_actionToTake = formAction.newUser Then
'// To avoid having the autogenerated primary key in dataset out
of synch with database,
'// retrieve the newly created database PK and update our
dataset's PK with it.
'// Include a variable and a command to retrieve the
autogenerated primary key value
'// (identity value) from the Access database.
'// cf.
ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconretrievingidentityorautonumber
values.htm
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("SELECT
@@IDENTITY", Me.m_parent.OleDbConnection1)
'// Retrieve the identity value.
newID = CInt(idCMD.ExecuteScalar())
'// Assign retrieved autonumber to our new row's primary key.
Dim drCm As DataRow = CType(Me.cmEmployees.Current,
DataRowView).Row '// datarow to inspect our current row
drCm.Item("EmployeeID") = newID '// push DB's new PK to
row's "Current", "Original", and "Default" values
'// Roll our PK value change into the row's "original version."
'// The deleteCommand uses the original row PK values to do the
deletion,
'// which may be out of synch with the database. If so, the
dataset would throw an
'// exception when the database (looking for the wrong PK) could
still not find the row to delete.
cmEmployees.EndCurrentEdit() '// allow this row's changes
to remain
drCm.AcceptChanges() '// set row's state from "Modified" to
"Unchanged" to prevent unneccessary labor
End If
Catch ex As Exception
MessageBox.Show(ex.Message + vbcrlf + vbcrlf + ex.StackTrace)
End Try
Me.Owner.Focus()
Me.Close()
End Sub