SubmitChanges to DB

  • Thread starter Thread starter Patrick A
  • Start date Start date
P

Patrick A

All,

I'm reading different posts, MSDNs, and Help topics, and can't find a
method that works. Maybe I'm trying to do something I can't w/VB 2008
Express.

I have a form that reads data from a DB.
Me.QRY_ButtonsTableAdapter.Fill(Me.SRTimerDataSet.QRY_Buttons)

The form displays the data fine, scrolls through, all of that.

I have enabled the save changes button on the Navigation Toolbar.

I can not figure out what code I need in my ...SaveItem_Click
subroutine to save the record(s) the user changes. I want to save all
of the fields on the form (values for each column).

None of these work:
SRTimerDataSet.QRY_Buttons.submitchanges()
SRTimerDataSet.QRY_ButtonsTableAdapter.submitchanges()
Me.SRTimerDataSet.QRY_Buttons.submitchanges()
Me.SRTimerDataSet.QRY_ButtonsTableAdapter.submitchanges()

Anyone have some good syntax to spare? Is there more to it than that?

Thanks,

Patrick
 
Patrick A said:
All,

I'm reading different posts, MSDNs, and Help topics, and can't find a
method that works. Maybe I'm trying to do something I can't w/VB 2008
Express.

I have a form that reads data from a DB.
Me.QRY_ButtonsTableAdapter.Fill(Me.SRTimerDataSet.QRY_Buttons)

The form displays the data fine, scrolls through, all of that.

I have enabled the save changes button on the Navigation Toolbar.

I can not figure out what code I need in my ...SaveItem_Click
subroutine to save the record(s) the user changes. I want to save all
of the fields on the form (values for each column).

None of these work:
SRTimerDataSet.QRY_Buttons.submitchanges()
SRTimerDataSet.QRY_ButtonsTableAdapter.submitchanges()
Me.SRTimerDataSet.QRY_Buttons.submitchanges()
Me.SRTimerDataSet.QRY_ButtonsTableAdapter.submitchanges()

Anyone have some good syntax to spare? Is there more to it than that?

Thanks,

Patrick
Click to expand...

Hello Patrick

I'll do this.
(Only works if the navigatorcontrol's datasource is a Dataset.

DataSet ds = (Dataset from Nav.Control.Datasource)
if ( ds != null)
{
DataSet dsChanges = ds.GetChanges();
if ( dsChanges != null)
{
using ( OleDbConnection con = new OleDbConnection(""))
{
con.Open();
using ( OleDbTransaction trn = con.BeginTransaction())
{
foreach ( DataTable dtChanges in dsChanges.Tables)
{
using ( OleDbCommand cmd = new OleDbCommand("SELECT *
FROM [" + dtChanges.TableName + "]", con);
{
cmd.Transaction = trn;
using ( OleDbDataAdapter da = new
OleDbDataAdapter(cmd))
{
using ( OleDbCommandBuilder cb = new
OleDbCommandBuilder(ds))
{
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.InsertCommand.Transaction = trn;
da.UpdateCommand.Transaction = trn;
da.DeleteCommand.Transaction = trn;

da.Update(dtChanges);

}
}
}
}
ds.AcceptChanges();
trn.Commit();
}
}
}
}
 
Thanks JJ

I can see there is a lot there - I think it may be over my head - l
tried to work my way through this replacing your hints with my
environmental stuff, but it's still a sea of errors. See my attempts
below.

That seems a lot to go through to update one record, though - I
thought this LINQ thingy was supposed to make my life easier!

--------------------------------------------------------

DataSet(ds = (Me.SRTimerDataSet.QRY_Buttons))
if ( ds != null)
{
DataSet dsChanges = ds.GetChanges();
if ( dsChanges != null)
{
using ( OleDbConnection con = new OleDbConnection(""))
{
con.Open();
using ( OleDbTransaction trn = con.BeginTransaction())
{
foreach ( DataTable dtChanges in dsChanges.Tables)
{
using ( OleDbCommand cmd = new OleDbCommand
("SELECT *
FROM [" + dtChanges.TableName + "]", con);
{
cmd.Transaction = trn;
using ( OleDbDataAdapter da = new
OleDbDataAdapter(cmd))
{
using ( OleDbCommandBuilder cb = new
OleDbCommandBuilder(ds))
{
da.InsertCommand = cb.GetInsertCommand
();
da.UpdateCommand = cb.GetUpdateCommand
();
da.DeleteCommand = cb.GetDeleteCommand
();
da.InsertCommand.Transaction = trn;
da.UpdateCommand.Transaction = trn;
da.DeleteCommand.Transaction = trn;


da.Update(dtChanges);


}
}
}
}
ds.AcceptChanges();
trn.Commit();
}
}
}
End Using
End Using
End Using
End Using
End Using
End If
End If
 
The issue seems to be trying to UPDATE an MDB via a Query.

If I replace my nice, clean, orderly query with just the table, all is
well, and VB lets me write:

Me.Validate()
Me.TBL_TimersBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.SRTimerDataSet)

Does that seem correct? So if I can't use a query, how do I sort the
records?
 
Back
Top