DataTable on MSAccess Table with 100+ fields

[Icemokka]

Hi,

I've got a table in MsAccess with 100+ fields.
If I fill a tabletable with this table , change some values , get the
update-command via commandbuilder , the update fails.
This because the commandbuilder adds a where clause that contains all
the fields in the datatable ( to check whether the record has been
changed since the fill ).
Because MSAccess does not allow more then 99 fields in the where
clause, the update fails offcourse.

So I was trying to adapt the sqlcommend for update itself by replacing
the where clause with only a where of it's primary key.
But even then when you do the update-commend, the tableadapter add's
the whole where-clause again.

Is there a way to avoid that he uses this where-clause itself and only
add the primary key ( or maybe a where we can alter ourselfs )?

Regards,
Sven Peeters
Belgium

[Jonathan Boivin]

I would two things.
Either, change for SQL Express 2005 (which is totally free), if this
possible for sure.
Otherwise, you could create a your own update string and pass it to the
DataAdapter.

"Icemokka" <ic******@gmail.coma écrit dans le message de news:
11**********************@i38g2000prf.googlegroups. com...

Hi,

I've got a table in MsAccess with 100+ fields.
If I fill a tabletable with this table , change some values , get the
update-command via commandbuilder , the update fails.
This because the commandbuilder adds a where clause that contains all
the fields in the datatable ( to check whether the record has been
changed since the fill ).
Because MSAccess does not allow more then 99 fields in the where
clause, the update fails offcourse.

So I was trying to adapt the sqlcommend for update itself by replacing
the where clause with only a where of it's primary key.
But even then when you do the update-commend, the tableadapter add's
the whole where-clause again.

Is there a way to avoid that he uses this where-clause itself and only
add the primary key ( or maybe a where we can alter ourselfs )?

Regards,
Sven Peeters
Belgium

[Icemokka]

On 8 nov, 19:40, "Jonathan Boivin" <[e-mail]:

I would two things.
Either, change for SQL Express 2005 (which is totally free), if this
possible for sure.
Otherwise, you could create a your own update string and pass it to the
DataAdapter.

"Icemokka" <[e-mail] écrit dans le message de news:
[e-mail]. com...

Hi,

I've got a table in MsAccess with 100+ fields.
If I fill a tabletable with this table , change some values , get the
update-command via commandbuilder , the update fails.
This because the commandbuilder adds a where clause that contains all
the fields in the datatable ( to check whether the record has been
changed since the fill ).
Because MSAccess does not allow more then 99 fields in the where
clause, the update fails offcourse.

Unfortunately that is not possibel ( SQL Express ), that would be much
easier.

I was also thinking about generating the SQL myself, but when I set
the UpdateCommand of the DataAdapter, he replaces it with the
UpdateCommand of the CommandBuilder ( with the full where-clause ).
Another thing bother's my is that some of my tables have 'image'
fields that contain files. Another restriction of MsAccess is that a
query cannot contain more than 64000 characters.
So even if I can force the UpdateCommand, ik my image field is too big
( like 64K for example ), the query fails to and updating is
impossible.

Is there no connected way in ADO.Net to update a recordset that
doesn't use a Query tu opdate?

Regards,
Sven Peeters

So I was trying to adapt the sqlcommend for update itself by replacing
the where clause with only a where of it's primary key.
But even then when you do the update-commend, the tableadapter add's
the whole where-clause again.

Is there a way to avoid that he uses this where-clause itself and only
add the primary key ( or maybe a where we can alter ourselfs )?

Regards,
Sven Peeters
Belgium- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

[=?Utf-8?B?VGVycnk=?=]

I have not used the TableAdapter much, but I believe that you can override
the commands that it generates. In the Ide, open the dataset containing the
table, near the bottom, click on the tableadapter, and then in the
properties, click on the + next to "UpdateCommand" and then modify the
command text and parameters collection to meet your needs. I believe that
you could also right click the tableadapter (from above) and then select
configure. Select "Advanced Options" and uncheck the "Generate Insert,
Update...". Maybe that will keep it from overriding the update command you
had generated in code. And I think that there is also a third option.
Again, right click the TableAdapter, and select "Add Query". Select the
"Update" radio button and build a new/different update command that (I think)
you would just invoke something like MyTableAdapter.MyUpdateCommand.
Do me a favor and let us know how this works for you. As I said, I havent
used TableAdapters much and am curious.
--
Terry
"Icemokka" wrote:

On 8 nov, 19:40, "Jonathan Boivin" <[e-mail]:

I would two things.
Either, change for SQL Express 2005 (which is totally free), if this
possible for sure.
Otherwise, you could create a your own update string and pass it to the
DataAdapter.

"Icemokka" <[e-mail] icrit dans le message de news:
[e-mail]. com...

Hi,

I've got a table in MsAccess with 100+ fields.
If I fill a tabletable with this table , change some values , get the
update-command via commandbuilder , the update fails.
This because the commandbuilder adds a where clause that contains all
the fields in the datatable ( to check whether the record has been
changed since the fill ).
Because MSAccess does not allow more then 99 fields in the where
clause, the update fails offcourse.

Unfortunately that is not possibel ( SQL Express ), that would be much
easier.

I was also thinking about generating the SQL myself, but when I set
the UpdateCommand of the DataAdapter, he replaces it with the
UpdateCommand of the CommandBuilder ( with the full where-clause ).
Another thing bother's my is that some of my tables have 'image'
fields that contain files. Another restriction of MsAccess is that a
query cannot contain more than 64000 characters.
So even if I can force the UpdateCommand, ik my image field is too big
( like 64K for example ), the query fails to and updating is
impossible.

Is there no connected way in ADO.Net to update a recordset that
doesn't use a Query tu opdate?

Regards,
Sven Peeters

So I was trying to adapt the sqlcommend for update itself by replacing
the where clause with only a where of it's primary key.
But even then when you do the update-commend, the tableadapter add's
the whole where-clause again.

Is there a way to avoid that he uses this where-clause itself and only
add the primary key ( or maybe a where we can alter ourselfs )?

Regards,
Sven Peeters
Belgium- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -