I've been working on this since yesterday and I just can't update my database. There are 3 tables. Here is a piece of code of one of WinForms. It loads data and display but after changing sth manually in the grid I get either errors by calling Update or anything happens at all.
please help because I'm going crazy.
public partial class Form3 : Form
{
//instance fields
private export2Excel export2XLS;
private DataSet _dataSet;
private BindingSource _bsrc;
private OleDbDataAdapter _dAdapter;
private OleDbCommandBuilder _cBuilder;
private DataTable _dTable;
private void button1_Click(object sender, EventArgs e)
{
//create the connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\\Documents and Settings\\dorota\\Moje dokumenty\\Visual Studio
2010\\Projects\\WindowsFormsApplication1\\WindowsFormsApplication1\\artb.mdb'";
//create the database query
string query = "SELECT * FROM Samochody";
System.Data.DataSet DtSet = new System.Data.DataSet();
_dataSet = DtSet;
//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
dAdapter.FillSchema(_dataSet, SchemaType.Source);
_dAdapter = dAdapter;
//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(_dAdapter);
_cBuilder = cBuilder;
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
_dTable = dTable;
//fill the DataTable
_dAdapter.Fill(_dTable);
//_dAdapter.TableMappings.Add("Samochody", "Table");
_dAdapter.Fill(_dataSet);
// --------------------- to datagridview !
//BindingSource to sync DataTable and DataGridView
BindingSource _bsrc = new BindingSource();
//set the BindingSource DataSource
//bSource.DataSource = _dTable;
_bsrc.DataSource = _dTable;
//_bsrc = bSource;
//set the DataGridView DataSource
dataGridView1.DataSource = _bsrc;
}
}
and here... :
private void sqlsave_Click(object sender, EventArgs e)
{
//int i=_dAdapter.Update(_dTable);
_dAdapter.Update(_dataSet.Tables["Samochody"]);
//_dAdapter.Update(_dataSet,"Samochody");
}
//---------------------------------------------------------------------------------
ok. I have changed sqlsave method for this
private void sqlsave_Click(object sender, EventArgs e)
{
try
{
//_dAdapter.Update(_dataSet.Tables["Samochody"]);
OleDbCommand oldb= _cBuilder.GetUpdateCommand();
int i=oldb.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show(i+" rows affected.");
//_dAdapter.Update(_dataSet,"Samochody");
}catch(OleDbException oldbex){
System.Windows.Forms.MessageBox.Show(oldbex.ToString());
}
and now I get finally sth more informative than "Error in"
An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll Additional information: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
so let me changed sth and I will let you know if this is it!
//--------------------------------
no. no. too fast, can't last. now while trying to save I've got exception again, connectin is opened, but (I can't post the image) when I debug this I see that my object of OleDbCommand type as _commandText has
"UPDATE Samochody SET Item=?, Data dyspozycji autem od=?, ..."
and so on. I think this is the reason. Am I right? What to do?
I've found the answer:
But a better approach would be to use drag-and-drop and learn form the code.
Select Data|View Datasources. Your dataset should be visible in the DataSources Window.
Drag a table to a (new) form. VS2005 will add a load of components and a few lines of code.
The form will now have a instance of the dataset and that is your reference point for Adapter.Fill and .Update methods.
Easy and works great! : D
I've found it here: https://stackoverflow.com/a/548124/1141471