DataTable, not writing to database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having problems getting records to save to a DataTable and was hoping
maybe someone out there could help. I am using a DataTable that is bound to
a data entry form. Basically, here is how it flows....

1.) DataTable has several records in it.
2.) User pushes a button to signal that they are done with the current
record (and therefore dont want to see it anymore) and a flag is inserted in
the record to indicate this.
3.) I want to commit all the current records in the DataTable to the
database and requery the database so that it brings up a fresh datatable
(minus the record the user just said they were done with).

This is the code I am using

'Trying to commit the changes to the database
DataAdapter.Update(DataTable)
DataTable.AcceptChanges()

'Clear the current DataTable
DataTable.Clear

'Requery Database
cmd.CommandText = MySqlStatement
DataAdapter= New AdoceDataAdapter(cmd)
DataAdapter.Fill(DataTable)

But when I do this, I dont get any records to return because the never got
wrote to the database? Any suggestions?

Thanks!

Mike
 
Though you would get an InvalidOperationException... Post your Update
command and also make sure that you have modified these records. See
what records have been returned by:

Dim dv as DataView = new DataView(DataTable, Nothing, Nothing,
DataViewRowState.ModifiedCurrent)


Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com
 
Thanks Sergey-

Well, your dataview suggestion seemed to identify the problem! Using the
dataview to only show 'ModifiedCurrent' records returns a count of 0. Any
idea why? Before I clear the DataTable I can see that there are records in
the DataTable, I can even see all the values in each row. But I still get no
'ModifiedCurrent' records via the DataView.

What triggers the DataViewRowState in a Datatable?

Any suggestions would be extremely helpful!

Thanks
 
No problem, your little snippet is pretty much how I do it.

'I first create a new record
Me.BindingContext(DataTable).AddNew()

'I then insert some data, like the primary key, into the row.
DataTable.Rows(Me.BindingContext(DataTable).Count - 1)("SampleID") =
m_SampleID

'The rest of the data is inserted via the bound controls (i.e. TextBoxes,
Combobox's, etc.) on the form.

'Then, to reiterate, I update the Data Table (unsuccesfully)
DataAdapter.Update("DataTable")

Does that help?
 
You did not say that you were adding new rows. Then you must modify
DataView in this manner:

Dim dv As DataView = new DataView(table, Nothing, Nothing,
DataViewRowState.ModifiedCurrent or DataViewRowState.Added)

Instead of direct access to DataTable use DefaultView and for commiting
editing call EndCurrentEdit:


BindingContext(DataTable).AddNew()
DataTable.DefaultView(BindingContext(table).Count - 1)("SampleID") =
m_SampleID
....
BindingContext(DataTable).EndCurrentEdit()



Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com
 
Well, that seems to be an improvement. The dataview now recognizes Modified
rows. The data I manually insert into the table is going in fine via....

DataTable.DefaultView(Me.BindingContext(DataTable).Count - 1)("SampleID") =
g_SampleID

But...the data entered via my bound controls are not.

After I "EndCurrentEdit" I clear the DataTable and Requery the DataTable. I
have records (because of the data I entered manually) but none of the records
from my bound fields got saved. In other words I have blank records on my
form. They are all null. Surgey, can you think of any reason this may be
happening? I dont know what else to try?

I have done this a million times and have always had it work fine. The only
difference here is that I am clearing the dataset and requerying the database
for the bound form. It obviously must have something to do with that.
 
Wow! That was kind of you. Unfortunately I cant access the file. I cant get
to your website either? Could you try email?

(e-mail address removed)

Thanks for all your help!
 
Back
Top