[VS2003] Updating table - how to do that ???

[Pascalus]

Hi there!

I've parsed the posts in this thread but haven't found answers to my problem. It drives me nuts.
I'm newbie at Database app under VS2003 and for now I only do simple things.

I fill a table with hundreds of records. I can see the result in a DataGrid.
Now I want to save the records on the physical file - the table - (through a SQL server).
How can I do that?

For the adapter, I dropped an oleDbAdapter on the form and followed the setup (a SQL connection).
I generated the dataset and the wizard generated all the INSERT/UPDATE/... commands without problem.

Below is a code snippet (C++ style). Can somebody tell me what's wrong/missing in it?
Many thanks,
/Pascalus.


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

private: System::Void btnOK_Click(System::Object * sender, System::EventArgs * e)
{
DataTable *table = myDataSet->Tables->Item[S"myTable"];
table->Clear();
for(int i=0; i<num; i++) {
DataRow *newRow = table->NewRow();
newRow->BeginEdit(); // is this mandatory?
newRow->Item[S"Column1"] = <something>;
newRow->Item[S"Column2"] = <something>;
newRow->EndEdit();
table->Rows->Add(newRow);
}
table->AcceptChanges(); // if not called, I get a oleDbDataAdapter Exception afterwards
}

// then I call UpdateTable(myDataSet, S"myTable");

void UpdateTable(DataSet* dataSet, String* tableName)
{
this->BindingContext->get_Item(dataSet, tableName)->EndCurrentEdit();
oleDbDataAdapter1->Update(dataSet, tableName); // if DataGrid has been edited, then I get DBConcurrencyException
if(dataSet->HasChanges(DataRowState::Added)) { // apparently, returns always FALSE
dataSet->AcceptChanges();
}
}

 

[Jerry H.]

Looks like your problem woud be the table->AcceptChanges line - you
should remove it.

When you call that line, all the rows in your table have their Changed
flag set to false (along with updating data inside the Dataset), and
then the UPDATE method of your adapter has no rows to work with.

 

[Pascalus]

Thanks Jerry for your reply!

I remove that line and now I get a "System.Data.OleDb.OleDbException" with no explicit message (btw, is there some window in VS2003 where I can get more details
about the exception?).

Well, I removed all the DataAdapter/DataSet components and restarted with an oleDbDataAdapter for a local MS Access database using the Jet OLEDB 4.0 driver.
When I ran the app then, it *updated* the table! BUT now the problem is that I get the aforementioned exception every time I run the app again!!! Is there some
specific action to do on the DataAdapter/DataSet when closing the app?

Thanks,
/Pascalus

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

private: System::Void btnOK_Click(System::Object * sender, System::EventArgs * e)
{
DataTable *table = myDataSet->Tables->Item[S"myTable"];
table->Clear();
for(int i=0; i<num; i++) {
DataRow *newRow = table->NewRow();
newRow->Item[S"Column1"] = <something>;
newRow->Item[S"Column2"] = <something>;
table->Rows->Add(newRow);
}
}

// then I call UpdateTable(myDataSet, S"myTable");

void UpdateTable(DataSet* dataSet, String* tableName)
{
oleDbDataAdapter1->Update(dataSet, tableName); // then I get a "System.Data.OleDb.OleDbException"
}

 

[Jerry H.]

put all your code inside a try..catch block. you can catch the
exception that is raised and look at the message.

 

[jmbledsoe]

I've been using a tool called the DataSet Toolkit to manage fills and
updates of multiple tables in my DataSet. It takes care of a lot of
the details of organizing the operations so that you don't have to.
Check it out and let me know if it works for you.

http://www.hydrussoftware.com

John B.
http://johnsbraindump.blogspot.com

 

[Pascalus]

Thx Jerry!
Indeed it helped: the exception raises because I have twice the same record on the primary key.

 

[Jerry H.]

AAAAHH!

Can't do that Pascalus! You can also set the "EnforceConstraints"
property of your dataset to FALSE, so that constraint exceptions (like
Nulls where they don't belong or Key violations) are ignored. But I
can't imagine to many instances of where you'd want to do that, other
than testing.