DataAdapter.update(dataSet) exception Error:
"Changes not saved to database. Expression Too Complex"
Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
Without diving into the details on the one-to-many 99 column Access (Main)
table , can someone tell me the reason for the error? Is it really the
complexity of the SQL statement? Would SQL server help me? Does SQL Server
allow update() of a queries. StevePlease send us the text of the query you are trying. Based on the little
data we have here, UPDATE() is likely not usable in your situation, since
this is only for use in triggers.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve B." <SteveB@.discussions.microsoft.com> wrote in message
news:6284FB15-F8CE-4BDA-9060-B4B09428F4B6@.microsoft.com...
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries. Steve|||hi steve
SQL Server allows updates. there might be an error in the sql string that u
were trying to pass.
please check the query and revert back.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL Serve
r
> allow update() of a queries. Steve|||> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries.
Just off the top of my head, each version of Access has a different maximum
limit on how long an SQL statement is allowed to be. I have actually gotten
that error. That's all I can think of though.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||I hope this is what your looking for (see below). In the mean time, I found
this (please see first few paragraphs):
http://support.microsoft.com/defaul...kb;en-us;192716
I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
thinking of dividing my Main table into 4 or 5 tables indexed on part number
(part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
workaround. What a mess..
The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexed
(dropdown) from their own table in the "Main" table but, every Characteristi
c
field is different (blueprint dimensions, note number, etc) . I really nee
d
30 characteristics not 21. Can't get there from here.
I don't know if a commercial database program like SQL server will help me.
Thats why I'm asking. Comments?
(copied from VS Dataadapter wizard)
SELECT
MainID,
PartNumber,
Nomenclature,
PartStatus,
CoverageBy,
CoverageByDate,
Engine,
Service,
Originator,
RequirementSourceCodes,
PartPointOfContact,
VerificationDate,
CharacteristicCodes1,
ProcessCodes1,
LocationCodes1,
Characteristic1,
CharacteristicCodes2,
LocationCodes2,
ProcessCodes2,
Characteristic2,
CharacteristicCodes3,
LocationCodes3,
ProcessCodes3,
Characteristic3,
CharacteristicCodes4,
LocationCodes4,
ProcessCodes4,
Characteristic4,
CharacteristicCodes5,
LocationCodes5,
ProcessCodes5,
Characteristic5,
CharacteristicCodes6,
LocationCodes6,
ProcessCodes6,
Characteristic6,
CharacteristicCodes7,
LocationCodes7,
ProcessCodes7,
Characteristic7,
CharacteristicCodes8,
LocationCodes8,
ProcessCodes8,
Characteristic8,
CharacteristicCodes9,
LocationCodes9,
ProcessCodes9,
Characteristic9,
CharacteristicCodes10,
LocationCodes10,
ProcessCodes10,
Characteristic10,
CharacteristicCodes11,
LocationCodes11,
ProcessCodes11,
Characteristic11,
CharacteristicCodes12,
LocationCodes12,
ProcessCodes12,
Characteristic12,
CharacteristicCodes13,
LocationCodes13,
ProcessCodes13,
Characteristic13,
CharacteristicCodes14,
LocationCodes14,
ProcessCodes14,
Characteristic14,
CharacteristicCodes15,
LocationCodes15,
ProcessCodes15,
Characteristic15,
CharacteristicCodes16,
LocationCodes16,
ProcessCodes16,
Characteristic16,
CharacteristicCodes17,
LocationCodes17,
ProcessCodes17,
Characteristic17,
CharacteristicCodes18,
LocationCodes18,
ProcessCodes18,
Characteristic18,
CharacteristicCodes19,
LocationCodes19,
ProcessCodes19,
Characteristic19,
CharacteristicCodes20,
LocationCodes20,
ProcessCodes20,
Characteristic20,
CharacteristicCodes21,
LocationCodes21,
ProcessCodes21,
Characteristic21,
Comments,
AnnualCSIConfirmation,
DataRowState
FROM
Main
WHERE
(AnnualCSIConfirmation = 'Yes') AND
(PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None On
Order (Inactive)') ORDER BY MainID
"Chandra" wrote:
> hi steve
> SQL Server allows updates. there might be an error in the sql string that
u
> were trying to pass.
> please check the query and revert back.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Yes!
i agree with you. The message mentions that, the query must be optimized.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mike Labosh" wrote:
> Just off the top of my head, each version of Access has a different maximu
m
> limit on how long an SQL statement is allowed to be. I have actually gott
en
> that error. That's all I can think of though.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||hi
looks like u need to normalize your table:
something like:
MainID,
Charid,
CharacteristicCodes,
ProcessCodes,
LocationCodes,
Characteristic,
but, this is out of scope now:
just try to remove "ORDER BY MainID" and check, it might speed up the
process and u can escape from the error.
or
where part:
AnnualCSIConfirmation = 'Yes' AND
PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
(buy/make part)', 'None On Order (Inactive)' )
please revert back with the results
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> I hope this is what your looking for (see below). In the mean time, I fou
nd
> this (please see first few paragraphs):
> http://support.microsoft.com/defaul...kb;en-us;192716
> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
> thinking of dividing my Main table into 4 or 5 tables indexed on part numb
er
> (part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
> workaround. What a mess..
> The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexe
d
> (dropdown) from their own table in the "Main" table but, every Characteris
tic
> field is different (blueprint dimensions, note number, etc) . I really n
eed
> 30 characteristics not 21. Can't get there from here.
> I don't know if a commercial database program like SQL server will help me
.
> Thats why I'm asking. Comments?
> (copied from VS Dataadapter wizard)
> SELECT
> MainID,
> PartNumber,
> Nomenclature,
> PartStatus,
> CoverageBy,
> CoverageByDate,
> Engine,
> Service,
> Originator,
> RequirementSourceCodes,
> PartPointOfContact,
> VerificationDate,
> CharacteristicCodes1,
> ProcessCodes1,
> LocationCodes1,
> Characteristic1,
> CharacteristicCodes2,
> LocationCodes2,
> ProcessCodes2,
> Characteristic2,
> CharacteristicCodes3,
> LocationCodes3,
> ProcessCodes3,
> Characteristic3,
> CharacteristicCodes4,
> LocationCodes4,
> ProcessCodes4,
> Characteristic4,
> CharacteristicCodes5,
> LocationCodes5,
> ProcessCodes5,
> Characteristic5,
> CharacteristicCodes6,
> LocationCodes6,
> ProcessCodes6,
> Characteristic6,
> CharacteristicCodes7,
> LocationCodes7,
> ProcessCodes7,
> Characteristic7,
> CharacteristicCodes8,
> LocationCodes8,
> ProcessCodes8,
> Characteristic8,
> CharacteristicCodes9,
> LocationCodes9,
> ProcessCodes9,
> Characteristic9,
> CharacteristicCodes10,
> LocationCodes10,
> ProcessCodes10,
> Characteristic10,
> CharacteristicCodes11,
> LocationCodes11,
> ProcessCodes11,
> Characteristic11,
> CharacteristicCodes12,
> LocationCodes12,
> ProcessCodes12,
> Characteristic12,
> CharacteristicCodes13,
> LocationCodes13,
> ProcessCodes13,
> Characteristic13,
> CharacteristicCodes14,
> LocationCodes14,
> ProcessCodes14,
> Characteristic14,
> CharacteristicCodes15,
> LocationCodes15,
> ProcessCodes15,
> Characteristic15,
> CharacteristicCodes16,
> LocationCodes16,
> ProcessCodes16,
> Characteristic16,
> CharacteristicCodes17,
> LocationCodes17,
> ProcessCodes17,
> Characteristic17,
> CharacteristicCodes18,
> LocationCodes18,
> ProcessCodes18,
> Characteristic18,
> CharacteristicCodes19,
> LocationCodes19,
> ProcessCodes19,
> Characteristic19,
> CharacteristicCodes20,
> LocationCodes20,
> ProcessCodes20,
> Characteristic20,
> CharacteristicCodes21,
> LocationCodes21,
> ProcessCodes21,
> Characteristic21,
> Comments,
> AnnualCSIConfirmation,
> DataRowState
> FROM
> Main
> WHERE
> (AnnualCSIConfirmation = 'Yes') AND
> (PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
> part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None
On
> Order (Inactive)') ORDER BY MainID
>
> "Chandra" wrote:
>|||>> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
You have a poorly designed table. Any shuffling you do without a thorough
analysis of your business model and logic rules, might still leave the
schema unmaintainable and make the queries complex.
Instead of splitting up a single set of attribute into groups of {1st set of
cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the table
better. Understand and analyze the entities in question and identify each of
the attributes explicitly. Identify the dependencies among these attributes
and consider decomposing them into multiple tables appropriately, if
required.
Even, without considering any ill-effects of under normalized schemas, with
certain assumptions based on the query you posted, you might benefit from
having your table structured like:
CREATE TABLE Parts (
Part_nbr INT NOT NULL PRIMARY KEY,
.. ) ;
CREATE TABLE Characteristics (
Character_id INT NOT NULL ,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Code ...
Characteristic...
PRIMARY KEY ( Character_id, Part_nbr ) );
CREATE TABLE Processes (
Process_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Process_code ...
PRIMARY KEY ( Process_id, Part_nbr ) );
CREATE TABLE Locations (
Location_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Location_code ...
PRIMARY KEY ( Location_id, Part_nbr );
Now the query is as simple as:
SELECT p1.Part_nbr,...
c1.code, ...
p2.process_code,...
l1.location_code...
FROM Parts p1
INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
...
This could be your comparable resultset which you might consider using for a
variety of purposes. For the specific case you mentioned in your post, you
can simply pivot this data, either using your client programming language or
if done within the server, using the popular pivoting technique detailed in
MSKB ( support.microsoft.com ) : 175574
Anith|||Thank You Chandra. That was an easy fix but I regret it didn't work. It wa
s
definitly worth a try in order to escape. I agree. Thanks Steve
"Chandra" wrote:
> hi
> looks like u need to normalize your table:
> something like:
> MainID,
> Charid,
> CharacteristicCodes,
> ProcessCodes,
> LocationCodes,
> Characteristic,
> but, this is out of scope now:
> just try to remove "ORDER BY MainID" and check, it might speed up the
> process and u can escape from the error.
> or
> where part:
> AnnualCSIConfirmation = 'Yes' AND
> PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
> (buy/make part)', 'None On Order (Inactive)' )
> please revert back with the results
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Anith,
Thank You for your input. I've sent a copy of your post to a coworker who
understands database design more then I - such is the problem with using
wizards. A few questions so I (we) can better understand the design/context
.
Assumptions:
1. You appear to be dividing the fields into 4 tables – Parts,
CharacteristicsCode (identified in post as Characteristics), Process and
Locations. There is an additional field Characteristic that varies with eac
h
of the other three fields. So for each of the [possible] 30 Characteristic’s
each of them have an associated CharacteristicsCode , Process and Location.
I guess the structure of all 4 tables (except Parts) are exactly the same an
d
doesn’t matter. With that said, the CharacteristicsCode, Process, Locatio
ns
and Characteristic tables will have the exact same table structure and kind
of relationship.
2. It’s assumed the Parts table will contain the part numbers and
associated part data fields like t nomenclatures, etc
3. Does PRIMARY KEY (…) mean you somehow combine the both fields? Is thi
s
the actual relationship, for example, the join from Process_id to Part_nbr ?
4. SQL statement: what’s p1, c1, etc. and/or what’s p1.Part_nbr, c1.cod
e, etc
I think I’m going to have to create it this before I fully understand it.
However, I’m not sure the Visual Studio (VS) Query Builder inside the VS
environment will allow me to create such an Access SQL statement as
described. The DataAdapter might just expand the SQL statement again in the
DataSet so, I might be right back to the beginning again. I can’t use
external Access queries for SELECT, UPDATE, DELETE etc. Please keep the
post open. Thank You.
Steve
"Anith Sen" wrote:
> You have a poorly designed table. Any shuffling you do without a thorough
> analysis of your business model and logic rules, might still leave the
> schema unmaintainable and make the queries complex.
> Instead of splitting up a single set of attribute into groups of {1st set
of
> cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the tab
le
> better. Understand and analyze the entities in question and identify each
of
> the attributes explicitly. Identify the dependencies among these attribute
s
> and consider decomposing them into multiple tables appropriately, if
> required.
> Even, without considering any ill-effects of under normalized schemas, wit
h
> certain assumptions based on the query you posted, you might benefit from
> having your table structured like:
> CREATE TABLE Parts (
> Part_nbr INT NOT NULL PRIMARY KEY,
> ... ) ;
> CREATE TABLE Characteristics (
> Character_id INT NOT NULL ,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Code ...
> Characteristic...
> PRIMARY KEY ( Character_id, Part_nbr ) );
> CREATE TABLE Processes (
> Process_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Process_code ...
> PRIMARY KEY ( Process_id, Part_nbr ) );
> CREATE TABLE Locations (
> Location_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Location_code ...
> PRIMARY KEY ( Location_id, Part_nbr );
> Now the query is as simple as:
> SELECT p1.Part_nbr,...
> c1.code, ...
> p2.process_code,...
> l1.location_code...
> FROM Parts p1
> INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
> INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
> INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
> ...
> This could be your comparable resultset which you might consider using for
a
> variety of purposes. For the specific case you mentioned in your post, you
> can simply pivot this data, either using your client programming language
or
> if done within the server, using the popular pivoting technique detailed i
n
> MSKB ( support.microsoft.com ) : 175574
> --
> Anith
>
>
