OleDbCommandBuilder, ImportRow, DataTable.Copy() etc. etc. etc.

[D13]

I'm sure everyone's sick of DataSet, OleDbDataAdapter.Update(), INSERT
INTO etc. etc. questions in this group, but I've been scanning the
usually insta-problem solving Google Groups for hours now and I just
can't seem to find a similar situation to mine.

I'm trying to accomplish something that I thought would be easy
enough:

1. Run a query on a table producing a DataSet of the data I want to
replace
2. Delete all said data
3. Insert another DataSet full of records back into the table
4. Actually have the changes to the database friggin _UPDATE_.

The trick is, and for reasons I won't bother getting into here, I need
to use OleDbCommandBuilder to do the work instead of what otherwise
would be a trivial SQL statement. The step I'm having trouble with is
#3... and well, I suppose #4.

What I'm discovering is that it's extremely difficult to get the
OleDbCommandBuilder to notice when changes have been (are being?) made
to the DataSet. I can manually DataRow.BeginEdit() make changes,
EndEdit() and everything updates fine, so there's no problem with my
peripheral code. I've tried the following ways of getting data from
one DataSet (newDataSet) to the other (dsDBData), all to no avail:

// Row-by-row copy attempts:

foreach (DataRow curRow in newDataSet.Tables["Items"].Rows)
{
dsDBData.Tables["Items"].NewRow();
// #1 dsDBData.Tables["Items"].Rows.Add(curRow);
// ERROR: row is already in another table
// #2 dsDBData.Tables["Items"].Rows.Add(curRow.IndexArray);
// ERROR: OleDbException (?)
// #3 dsDBData.Tables["Items"].ImportRow(curRow);
// RESULT: Data not updated (DataRowState is imported and thus
"Unchanged")
}

// Whole table attempts:

// #4 DataTable tmpTable = dsDBData.Tables["Items"];
// tmpTable = newDataSet.Tables["Items"].Copy();
// RESULT: Data not updated (?)

The closest I seem to get is with #2 wherein an actual UpdateCommand
is built by the OleDbCommandBuilder, but alas a pesky and vague
OleDbException gets thrown.

Apologies for the verbose post on what I originally thought would be
an extremely simply problem to solve. Hopefully there is a quick
answer out there somewhere about something I'm just missing.

Thanks in advance.

 

[Cor Ligthert]

D13,

What I read from you gives me the idea, that you have an idea how the
commandbuilder and the update is working in a way it is not doing that.

The dataadapter is the active element.
For that you can (using the select command) build update, insert and delete
command with the commandbuilder. Than the action from the commandbuilder is
done.

The dataadapter checks everytime during an update what is the rowstate of a
datarow in a datatable and does his job accoording to that.

That is all

I hope this gives some ideas?

Cor

..

 

[D13]

Cor, I'm not sure I follow - maybe posting some of my peripheral code
will help to further describe my problem and eliminate some red
herrings.

I'm certainly originally populating the DataSet with a SELECT
statement:

<--->
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ SweetDictionary.CURRENT + ";";
string strSQL = "SELECT * FROM Items WHERE CheckID = " + checkID;

DataSet dsDBData = new DataSet();
OleDbConnection dbConnection = new OleDbConnection(strConnection);
OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
dbAdapter.SelectCommand = new OleDbCommand(strSQL, dbConnection);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(dbAdapter);
dbConnection.Open();

dbAdapter.Fill(dsDBData, "Items");
<--->

And after each attempt listed in my first post, I'm calling Update()
on the DataAdapter:

<--->
// Update the info in the database
dbAdapter.Update(dsDBData, "Items");

dbConnection.Close();
<--->

As I mentioned, investigating the OleDbCommandBuilder after (during?)
the DataAdapter.Update() call, I can clearly see an UpdateCommand has
been built, a vague exception is also thrown however.

I should also mention that earlier in the block I had no problem
calling Delete() on certain DataRows then calling DataAdapter.Update()
to propogate those changes from the DataSet to the actual Database. In
other words, a DeleteCommand was successfully built and executed
earlier in the block. My problem remains that I cannot get an
UpdateCommand both successfully built AND executed when it comes to
basically replacing certain DataRows with others.

Perhaps there's an entirely different approach to replacing DataRows
within an updating DataSet of which I'm unaware - that's really what
I'm looking for.

Thanks again.

 

[Cor Ligthert]

D13

I see nothing wrong with your code (except that you should when you have
resolved this use a parameter to fill that checkId however lets keep us that
for the next question).

I cannot see the part where you add the rows to your dataset, however read
this completly first before you show that to me and try it yourself first.

In this command beneath should all your rows be updated, which have a
rowstate added, changed or deleted. (watch that you do not use remove, that
removes complete so there is no rowstate).

dbAdapter.Update(dsDBData, "Items");

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadatarowstateclasstopic.asp

What goes mostly wrong is that people are using binded datasets, in those
will the row only change when there is really a change in the control, let
say to another textbox or a new row in a datagrid. To force that is the
endcurrentedit

BindingContext(ds.Tables(0)).EndCurrentEdit()

However what I can see is that not the situation from you

1. Run a query on a table producing a DataSet of the data I want to
replace

Here you can use as far as I can see the fillschema
http://msdn.microsoft.com/library/d...ystemdataidataadapterclassfillschematopic.asp

2. Delete all said data
Than this is of course not needed

3. Insert another DataSet full of records back into the table
Possible is this the reason of your error. You insert all date however check
that you insert them as new rows, not with a reference to an existing row,
than it is only a reference and the rowstate does not change. (although
normally this should be impossible because setting a row in two datatables
is impossible)

However try to check when you are doing that insert if your rowstate is set
to one of the to updated states as you see in the link above.

4. Actually have the changes to the database friggin _UPDATE_.
Just as you do.

I hope this gives some ideas

And this is the link to the commandparameters
http://msdn.microsoft.com/library/d...rfsystemdataoledboledbparameterclasstopic.asp

It is not the nicest description page, this is not the reason of your error
however it is possible that somebody says that, because that is one of the
first things what is said by some regulars when they see that here (not
all).


I hope this helps something?

Cor

 

[D13]

Thanks a lot for your help, Cor.

I've narrowed the exception down to a syntax error in the INSERT INTO
command built by the OleDbCommandBuilder, but I'm still having
troubling resolving it. Here is the code in question:

// Step through the passed DataSet, comparing with and updating data
in the DB
foreach (DataRow curRow in newDataSet.Tables["Items"].Rows)
{
// If the item is a new item, add a new row to represent it in the
database,
// getting a new unique ID
if (Convert.ToInt32(curRow["UniqueID"]) == -1)
{
DataRow addRow = dsDBData.Tables["Items"].NewRow();
addRow.ItemArray = curRow.ItemArray;
dsDBData.Tables["Items"].Rows.Add(addRow);
}

....

What's happening here is if the row in the source DataSet (newDataSet)
contains a UniqueID value of -1, I want to add that row as a new row
to the target DataSet (dsDBData). What I thought those three little
lines would accomplish is create a new row, copy the values from one
DataSet to the other, and Add the new row (setting the DataRowState to
Added). All of that seems to be working, but when it comes time to
call DataAdapter.Update(), the InsertCommand built by the
OleDbCommandBuilder seems to be something along the lines of "INSERT
INTO Items( [all my columns] ) VALUES ( ? , ? , ? ...)".

Frankly, my SQL knowledge is weak, but that looks okay to me - I
assume those ?'s are filled out by the DataAdapter when it gets to
each new DataRow that needs inserting.

I should mention that the UniqueID field is an Autonumber field as
well as the table's Primary Key. The InsertCommand makes no reference
to this field at all, which is the way I assumed it should be, but I
am copying that field's info from the source DataSet to the target
DataSet when I simply assign the one's ItemArray to the other's. That
all may just be a red herring, but I thought I'd mention it anyway.

Again, the error thrown on the Update() call is a syntax error in the
INSERT INTO statement, and again, thanks for your help.

 

[Cor Ligthert]

D13

Glad you are talking that you use the autokey, because there is most
probably your problem, have a look at these pages (you have to scroll a
little bit through them using the 3 properties which are important with
this).

http://msdn.microsoft.com/library/d...stemdatadatacolumnclassautoincrementtopic.asp

What is in my eyes important, the autoincrement is adviced to set to -1.
When the update is done the actual numbers are set, so to go on, you have to
clean your dataset and fill it again and you get the actual numbers which
are allocated to that.

I hope this helps?

Cor

 

[D13]

I had my suspicions about the AutoNumber field as well, Cor, but it
turns out that's not the problem. I tried explicitly setting the
DataColumn.AutoIncrement to true and assigning the new
DataRow["UniqueID"] to DBNull.Value, but now I get an error on
DataRow.Add() saying that this field cannot contain a null value.

I tried physically changingthe table property in Access to Int32
instead of AutoNumber, but then I get the same original SQL error.

It's possible I have both problems - I've been reading posts about
having to include an inline function to report back the assigned
AutoNumber, and that looks like something I'll need to do - but the
original SQL error still remains even when the entire AutoNumber field
is taken out of the equation. I'll post my function in its entirety in
case I'm missing something:

(Sorry for the terrible indenting - i need a better NNTP client)

{
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ SweetDictionary.CURRENT + ";";
string strSelect = "SELECT * FROM Items WHERE CheckID = " + checkID;

DataSet dsDBData = new DataSet();
OleDbConnection dbConnection = new OleDbConnection(strConnection);
OleDbDataAdapter dbAdapter = new OleDbDataAdapter(strSelect,
dbConnection);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(dbAdapter);
dbConnection.Open();

// Retrieve items belonging to this check currently in the database
dbAdapter.Fill(dsDBData, "Items");

// Primary key isn't defined during Fill(); define it here.
DataColumn [] dbColArray = new DataColumn[1];
dbColArray[0] = dsDBData.Tables["Items"].Columns["UniqueID"];
dsDBData.Tables["Items"].PrimaryKey = dbColArray;

// AutoIncrement column isn't defined during Fill(); define it here.
dsDBData.Tables["Items"].Columns["UniqueID"].AutoIncrement = true;

// Step through the passed DataSet, comparing with and updating data
in the DB
foreach (DataRow curRow in newDataSet.Tables["Items"].Rows)
{
// If the item is a new item, add a new row to represent it in the
database, getting a new unique ID
if (curRow["UniqueID"] == DBNull.Value)
{
DataRow addRow = dsDBData.Tables["Items"].NewRow();
addRow.ItemArray = curRow.ItemArray;
dsDBData.Tables["Items"].Rows.Add(addRow); // Cannot contain null
value error!
}
else
{
// Find the item in the database to change by matching its unique
idDataRow changeRow =
dsDBData.Tables["Items"].Rows.Find(curRow["UniqueID"]);


if (changeRow == null)
{
// D13: Raise exception - panic!
}
else
{
// Change the row in the database to reflect the new row
changeRow.ItemArray = curRow.ItemArray;
}
}
}

try
{
// Update the info in the database
dbAdapter.Update(dsDBData, "Items");
}
catch (Exception e)
{
string blah = e.ToString();
}
dbConnection.Close();
}

I'm not sure if you'll be able to make much out of that mess, but I've
almost given up on doing this function the originally intended way.

Thanks again for the time spent.

 

[Cor Ligthert]

D13,

You should not set you autoincrement field, that is done automaticly for you
by the methods I showed you in the previous message.

Cor

 

[D13]

As I mentioned in an earlier posting, in an attempt to narrow the
scope of the problem, I removed all references to the AutoIncrement
stuff in code and changed the field to an Int32 right in the database.
The whole set of problems associated with AutoIncrement fields seems
to have nothing to do with the syntax error in the INSERT INTO command
generated by the OleDbCommandBuilder.

Why is the SQL statement generated with a syntax error? Can anyone
help me?

 

[D13]

What an incredibly misleading error message! Turns out one of my
fields was on the reserved wordlist.

Thanks again for your help, Cor - sorry it wasn't something that you
could've possibly known without seeing my database.