By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

.net table adapter code behind missing UPDATE statement

P: n/a
in a new project using .net 2005, c#.

getting err message "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."

source RDBMS is oracle 8i. I add a new dataset to the project and drag
a datatable from server explorer onto the dataset design surface. In
the configuration wizard, under advanced options the "refresh data
table" is disabled ie I am not able to check it (note if the source db
is sql server this option is enabled).

I click next and accept defaults. The Insert and Select commands are
created, but not the Update command (note if the source db is sql
server the update command is created). The Insert, Select, and Update
methods are all created, just not the update command.

I am trying to use typed datasets to pass into the DAL for updates, so
I'm not sure that I need to use command builder as a workaround as it
appears cb will take a generic dataset only (unless an overload for
each typed dataset is written).

I also suppose a workaround could be to type the update command
manually but we have over 100 tables ...

I tried copying the SQL Server update command into the code behind of
the oracle equivalent but it didn't take (I receive the same error msg
as when there is no update statement).

the connection types I've tried are ODBC, OLEDB, Oracle Database etc
all seem to give the same result.

please let me know if there is a way to have the adapter automatically
create the update statement from an oracle 8i db.

Sep 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

Hi - I had the same issue with Oracle in VS 2003.

Check to make sure that the TABLE you are working with has a PRIMARY KEY
CONSTRAINT. It can't be a INDEX that enforces uniqueness on the table. It has
to be a PRIMARY KEY CONSTRAINT.

This issue with VS 2003 was that the UpdateCommand builder wizard (or
whatever you call it) would bail on creating the UpdateCommand if it could
not find a primary key on the table. In order words it could not build a SQL
statement that could update just ONE RECORD (i.e. so the dataset -
dataadapter could always find the right record to update).

In SQL Server the UpdateCommand builder is a little more forgiving - with
Oracle it's not.

Now I'm not sure if this is the case with VS 2005 (which is what you are
using) but I got a feeling it might be. It's worth a shot anyway.

Once you get beyond this you are going to like working with .NET and Oracle
8i. It works pretty darn well... and I have several production systems out
there working within this context.

ANOTHER CRITICAL TIP! Make sure your Oracle field names are less than 20
characters or the command builder is going to give you troubles. If you want
more information on this - feel free to ping me directly...

Good luck!

--
Franklin M. Gauer III
Applications Development Manager
Integrated Companies, Inc.
"tr******@gmail.com" wrote:
in a new project using .net 2005, c#.

getting err message "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."

source RDBMS is oracle 8i. I add a new dataset to the project and drag
a datatable from server explorer onto the dataset design surface. In
the configuration wizard, under advanced options the "refresh data
table" is disabled ie I am not able to check it (note if the source db
is sql server this option is enabled).

I click next and accept defaults. The Insert and Select commands are
created, but not the Update command (note if the source db is sql
server the update command is created). The Insert, Select, and Update
methods are all created, just not the update command.

I am trying to use typed datasets to pass into the DAL for updates, so
I'm not sure that I need to use command builder as a workaround as it
appears cb will take a generic dataset only (unless an overload for
each typed dataset is written).

I also suppose a workaround could be to type the update command
manually but we have over 100 tables ...

I tried copying the SQL Server update command into the code behind of
the oracle equivalent but it didn't take (I receive the same error msg
as when there is no update statement).

the connection types I've tried are ODBC, OLEDB, Oracle Database etc
all seem to give the same result.

please let me know if there is a way to have the adapter automatically
create the update statement from an oracle 8i db.

Sep 20 '06 #2

P: n/a
Thanks, I think that might do the trick...

we are converting a legacy system and I know for a fact that the
original db didn't use primary keys. I thought the unique indexes might
have worked but I will try adding an explicit PK.

thanks again
Franklin M. Gauer III wrote:
Hi - I had the same issue with Oracle in VS 2003.

Check to make sure that the TABLE you are working with has a PRIMARY KEY
CONSTRAINT. It can't be a INDEX that enforces uniqueness on the table. It has
to be a PRIMARY KEY CONSTRAINT.

This issue with VS 2003 was that the UpdateCommand builder wizard (or
whatever you call it) would bail on creating the UpdateCommand if it could
not find a primary key on the table. In order words it could not build a SQL
statement that could update just ONE RECORD (i.e. so the dataset -
dataadapter could always find the right record to update).

In SQL Server the UpdateCommand builder is a little more forgiving - with
Oracle it's not.

Now I'm not sure if this is the case with VS 2005 (which is what you are
using) but I got a feeling it might be. It's worth a shot anyway.

Once you get beyond this you are going to like working with .NET and Oracle
8i. It works pretty darn well... and I have several production systems out
there working within this context.

ANOTHER CRITICAL TIP! Make sure your Oracle field names are less than 20
characters or the command builder is going to give you troubles. If you want
more information on this - feel free to ping me directly...

Good luck!

--
Franklin M. Gauer III
Applications Development Manager
Integrated Companies, Inc.
"tr******@gmail.com" wrote:
in a new project using .net 2005, c#.

getting err message "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."

source RDBMS is oracle 8i. I add a new dataset to the project and drag
a datatable from server explorer onto the dataset design surface. In
the configuration wizard, under advanced options the "refresh data
table" is disabled ie I am not able to check it (note if the source db
is sql server this option is enabled).

I click next and accept defaults. The Insert and Select commands are
created, but not the Update command (note if the source db is sql
server the update command is created). The Insert, Select, and Update
methods are all created, just not the update command.

I am trying to use typed datasets to pass into the DAL for updates, so
I'm not sure that I need to use command builder as a workaround as it
appears cb will take a generic dataset only (unless an overload for
each typed dataset is written).

I also suppose a workaround could be to type the update command
manually but we have over 100 tables ...

I tried copying the SQL Server update command into the code behind of
the oracle equivalent but it didn't take (I receive the same error msg
as when there is no update statement).

the connection types I've tried are ODBC, OLEDB, Oracle Database etc
all seem to give the same result.

please let me know if there is a way to have the adapter automatically
create the update statement from an oracle 8i db.
Sep 20 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.