Question DataAdapter.Update - Query is too complex workaround?

[Oddism]

The database which I'm updating exceeds 120 fields so now, when I call my DataAdapters update method, it throws the excception 'Query is too complex'.

I was just curious as to whether there was a way to workaround this error or if the only option is to make my Database smaller?

Thanks

 

[Oddism]

After reading around for quite some time, I now know I'm getting the error due to the generated SQL statement from the DataAdapter.Update method contains more than 99 'AND' statements - apparently Access can't handle this. So now, I'm looking for some help in manually writing a SQL Update statements which updates every field in my Database.

 

[cjard]

Read the DW4 link in my sig, section: creating a simple data app, so that this part makes sense:

When you go to add a tableadapter to your dataset, or if you already added one by dragging, go through the wizard (again, by right click the TA and choose configure) and in the first or second page of the wizard youll find an advanced options section/button, press it.. Turn OFF th option "use optimistic concurrency"

Right now your query is too complex because your update query has the main part:

UPDATE table1 SET col1 ... col 120 WHERE id = ?

But it also has an optimistically concurrent part:

(WHERE id = ?)
AND (col1 = ? or col1 is null and ? is null)
...
AND (col120 = ? or col120 is null and ? is null)


These AND claues essentially check that the data hasnt been changed by anyone else since you downloaded the data, because the relevant parameters are populated by the old data values the dataset remembers before the edits.. If you turn off optimistic concurrency you will gain a very simple update query, with a short where clause.. BUT you will also have a query that just overwrites the data in the db with what your user is saving, regardless of if someone else edited it while you were editing it

This latter notion might be of little consequence to you or it might be great.. If it matters I'd suggest adding a column (heh) that stored revision number of the record.. Increment it on update, by:

UPDATE table1 SET col1...col120, revision = revision + 1 WHERE id = ? and revision = ?

Pass the old value for revision that you downloaded. If this query fails, someone else ran an update. While not as sophisticated as the other query (in that the other query will let 2 users work on a record so long as they each edit different fields) but provides rudimentary row level notification that something on the ro has been changed by someone else

 

[cjard]

note if youre not using dataset designer/tableadapter you can possibly get a similar solution out of the oledbcommandbuilder.. i just have no idea how.. not sure if it always generates optimistically concurrent queries or if theres a parameter to control that behaviour.. (but i'd really recommend using dataset designer in all cases.. )
good luck

 

[JohnH]

note if youre not using dataset designer/tableadapter you can possibly get a similar solution out of the oledbcommandbuilder.. i just have no idea how.. not sure if it always generates optimistically concurrent queries or if theres a parameter to control that behaviour.. (but i'd really recommend using dataset designer in all cases.. )
good luck

DbCommandBuilder.ConflictOption Property

All update and delete statements include only PrimaryKey columns in the WHERE clause. If no PrimaryKey is defined, all searchable columns are included in the WHERE clause.

 

[Oddism]

Thanks so much both of you for your help. I'm not using the designer since I've only recently began coding and wanted to write as much code as possible.

JohnH, the conflict option property was exactly what I was looking for, however, this may become a problem. Because of this, I may decide to add the revision column as you suggested cjard and write the SQL update statement myself. Yet this raises another problem - I have little to no knowledge on how they work...

From what I've looked at so far you would:
"UPDATE tablename SET columnname1 columnname2 columnname3 columnname4 ... columnnamex WHERE id(primary key field) = ?"

Would a statement like that update everycell in the database. Sorry if I sound like an idiot but I really can't seem to figure out how these SQL update statements work

For now though, it's working through the OleDbCommandBuilder.ConflictOption property

Thanks again!

 

[cjard]

Thanks so much both of you for your help. I'm not using the designer since I've only recently began coding and wanted to write as much code as possible.

So youre laying out your forms by hand too.. or are you using the forms designer to drag and drop your buttons, change your label texts, etc...

Just pointing out the inconsistency/hypocrisy in using the forms designer but refusing to use the dataset designer

JohnH, the conflict option property was exactly what I was looking for, however, this may become a problem. Because of this, I may decide to add the revision column as you suggested cjard and write the SQL update statement myself. Yet this raises another problem - I have little to no knowledge on how they work...

From what I've looked at so far you would:
"UPDATE tablename SET columnname1 columnname2 columnname3 columnname4 ... columnnamex WHERE id(primary key field) = ?"

Would a statement like that update everycell in the database. Sorry if I sound like an idiot but I really can't seem to figure out how these SQL update statements work

Yes, but lets say the user only changed the text in one column.. the other 119 columns would have their values updated to the same value as is already there, which is effectively no change.
Note that you'd need to add a little concurrency check:
UPDATE tablename SET columnname1 columnname2 columnname3 columnname4 ... columnnamex, rowVersionNumber = rowVersionNumber +1 WHERE id(primary key field) = ? AND rowVersionNumber = ?

In your setup, the rowversionnumber passed in the place of the ? parameter is the same number you downloaded, you DONT edit that client side... Row Version is 10, you download the row, edit it, update it sending 10 in as the version, the database willonly overwrite the row that is there if it is version 10.. (and make it version 11)
If someone else snuck an edit in before you, row version will be 11 (or more) so you cant save your version 10.. stops you overwriting their changes