Updating an unbound BindingSource from an Access Database

[EDN Admin]

I have an application that uses data in an Access database. One of the tables in the Access database needs to be manipulated by the user to 1) Change its order 2) Change a boolean value.
In Order to manipulate the Order, I have chosen to use a TreeView control. I liked the TreeViews ability to drag the rows around the control, so this works really well in chaning the order of the items in the Table (the Table contains a field which
is basically the sorting order of the table...according to the user). Also, instead of just having a checkbox for the boolean field, I instead use two Images to represent the Yes/No options of the field.
The application previously was an old VB5.0 program running under Windows XP, so the syntax and variables were pretty much just like the VBA in Access (Recordsets in the VB5.0). I am in the process of converting the whole thing to VB2010 and have been pointed
in the direction of using BindingSource(s) to link to the Tables or SQL statements from the previous app (Im already using BindingSource(s) for some ComboBoxes and they work fine.
Im able to populate the TreeView, its just the update with the new data thats not working. Here is a sample of my code (the name of the table is tblCDOrder):
Declarations outside of Form_Load and TableUpdating Sub(s)
<span style="font-size:small <span style="font-size:small




<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Dim<span style="font-size:small <span style="font-size:small myConnection
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As<span style="font-size:small <span style="font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small New<span style="font-size:small <span style="font-size:small OleDb.<span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small OleDbConnection
<span style="font-size:small <span style="font-size:small (<span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="""<span style="font-size:small <span style="font-size:small &
<span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small Strings<span style="font-size:small <span style="font-size:small .Left(<span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small Application<span style="font-size:small <span style="font-size:small .StartupPath,
Len(<span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small Application<span style="font-size:small <span style="font-size:small .StartupPath)
- 9) & <span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small "App_DataThe Hazzans Desktop.mdb"""<span style="font-size:small <span style="font-size:small )<br/>
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small WithEvents<span style="font-size:small <span style="font-size:small bsCDOrder
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As<span style="font-size:small <span style="font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small New<span style="font-size:small <span style="font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small BindingSource<span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <br/>
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Dim<span style="font-size:small <span style="font-size:small dtCDOrder
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As<span style="font-size:small <span style="font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small New<span style="font-size:small <span style="font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small DataTable<span style="font-size:small <span style="font-size:small ,
daCDOrder <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As<span style="font-size:small <span style="font-size:small OleDb.<span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small OleDbDataAdapter<span style="font-size:small <span style="font-size:small
Sub Form_Load<br/>
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Dim<span style="font-size:small <span style="font-size:small cn
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As
<span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small OleDbConnection<br/>
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Dim<span style="font-size:small <span style="font-size:small IX
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Integer<br/>
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Dim<span style="font-size:small <span style="font-size:small strSQL
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small String<br/>
<span style="font-size:small <span style="font-size:small strSQL =
<span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small "SELECT tblRecordingTypes.* "<br/>
<span style="font-size:small <span style="font-size:small strSQL = strSQL &
<span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small "From tblRecordingTypes "<br/>
<span style="font-size:small <span style="font-size:small strSQL = strSQL &
<span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small "ORDER BY tblRecordingTypes.SortOrder "<br/>
<span style="font-size:small <span style="font-size:small strSQL = strSQL &
<span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small <span style="color:#a31515; font-size:small "WITH OWNERACCESS OPTION;"<br/>
<span style="font-size:small <span style="font-size:small
<span style="font-size:small <span style="font-size:small cn =
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small New<span style="font-size:small <span style="font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small OleDbConnection<span style="font-size:small <span style="font-size:small (myConnection.ConnectionString)<br/>
<span style="font-size:small <span style="font-size:small daCDOrder =
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small New<span style="font-size:small <span style="font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small <span style="color:#2b91af; font-size:small OleDbDataAdapter<span style="font-size:small <span style="font-size:small (strSQL,
cn)<br/>
<span style="font-size:small <span style="font-size:small daCDOrder.Fill(dtCDOrder)<br/>
bsCDOrder.DataSource = dtCDOrder
<span style="font-size:small <span style="font-size:small
<span style="font-size:small <span style="font-size:small <a> Populate the TreeView is here and works fine
<span style="font-size:small <span style="font-size:small
<span style="font-size:small <span style="font-size:small <a> End Sub
<span style="font-size:small <span style="font-size:small Sub UpdateTheTable
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Dim<span style="font-size:small <span style="font-size:small <span style="font-size:small <span style="font-size:small ixCDOrder
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small As<span style="font-size:small <span style="font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small Integer
<span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="color:#0000ff; font-size:small <span style="font-size:small <span style="font-size:small
ixCDOrder = bsCDOrder.Find(<RecordKeyField>,<TreeViewNode>) Locate record to update<br/>
bsCDOrder.Position = ixCDOrder Move to the proper record in the BindingSource<br/>
Debug.Print(bsCDOrder.Current(<BooleanField in Table>).ToString To trace the prior value<br/>
bsCDOrder.Current(<BooleanField in Table>) = <Boolean Value in TreeView)
<br/>
Debug.Print(bsCDOrder.Current(<BooleanField in Table>).ToString To trace the updated value<br/>
Me.Validate Put this in because I found it in another thread<br/>
bsCDOrder.EndEdit<br/>
dtCDOrder.AcceptChanges Put this in because I found it in another thread<br/>
daCDOrder.
End Sub
Just to clarify, the populating of the TreeView is working fine, updating of the fields in the TreeView is also working, its when I try and save them back to the Table that theyre not updating. Ive read other threads that talk about Copy options, but
all of the references to the Database are set to Do Not Copy.
Any ideas would be greatly appreciated.
Thanks,
Paul Goldstein
<
Paul D. Goldstein Forceware Systems, Inc.

View the full article