469,317 Members | 1,861 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,317 developers. It's quick & easy.

data adapter update fails

I'm getting an inconsistent failure when trying to save
data in ADO.Net.

I'm using an Access database with a simple query - SELECT
StudentID, FirstName, LastName FROM Students - and have
no other users in the database. I keep getting the
message 'Operation must use an updateable query.'

Process seems to work on some PC's, not others. Is there
a difference in .Net framework versions? Or some other
place to look for the problem? I'm using a datatable, and
dataadapter with command builder, using disconnected
data. Help!
Nov 20 '05 #1
8 1714
Here is an example of an update query I did with an access DB. Compare with
Yours.

Regards - OHM

'setup People Update Command

peopleUpdateCmd.CommandText = "UPDATE People SET Address1 = ?, Address2 = ?,
Address3 = ?, Address4 = ?, Country" & _

" = ?, County = ?, DOB = ?, Email = ?, FirstName = ?, [Home Phone] = ?,
LastName " & _

"= ?, MiddleInnitial = ?, [Mobile Fone] = ?, [Post Code] = ?, Town = ? WHERE
(ID " & _

"= ?) AND (Address1 = ?) AND (Address2 = ?) AND (Address3 = ?) AND (Address4
= ?)" & _

" AND (Country = ?) AND (County = ?) AND (DOB = ?) AND (Email = ? OR ? IS
NULL AN" & _

"D Email IS NULL) AND (FirstName = ?) AND ([Home Phone] = ?) AND (LastName =
?) A" & _

"ND (MiddleInnitial = ? OR ? IS NULL AND MiddleInnitial IS NULL) AND
([Mobile Fon" & _

"e] = ? OR ? IS NULL AND [Mobile Fone] IS NULL) AND ([Post Code] = ?) AND
(Town =" & _

" ?)"





Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com
"rriness" <an*******@discussions.microsoft.com> wrote in message
news:03****************************@phx.gbl...
I'm getting an inconsistent failure when trying to save
data in ADO.Net.

I'm using an Access database with a simple query - SELECT
StudentID, FirstName, LastName FROM Students - and have
no other users in the database. I keep getting the
message 'Operation must use an updateable query.'

Process seems to work on some PC's, not others. Is there
a difference in .Net framework versions? Or some other
place to look for the problem? I'm using a datatable, and
dataadapter with command builder, using disconnected
data. Help!

Nov 20 '05 #2
I was hoping not to have to build my own command - the
select statement meets the requirements of a single
table, with a one field primary key....

In your query how do you map the ? (parameters) to
fields/alternate values? (Each field has an original
value, a current value if changed since the data was
read). Thanks for your help.
-----Original Message-----
Here is an example of an update query I did with an access DB. Compare withYours.

Regards - OHM

'setup People Update Command

peopleUpdateCmd.CommandText = "UPDATE People SET Address1 = ?, Address2 = ?,Address3 = ?, Address4 = ?, Country" & _

" = ?, County = ?, DOB = ?, Email = ?, FirstName = ?, [Home Phone] = ?,LastName " & _

"= ?, MiddleInnitial = ?, [Mobile Fone] = ?, [Post Code] = ?, Town = ? WHERE(ID " & _

"= ?) AND (Address1 = ?) AND (Address2 = ?) AND (Address3 = ?) AND (Address4= ?)" & _

" AND (Country = ?) AND (County = ?) AND (DOB = ?) AND (Email = ? OR ? ISNULL AN" & _

"D Email IS NULL) AND (FirstName = ?) AND ([Home Phone] = ?) AND (LastName =?) A" & _

"ND (MiddleInnitial = ? OR ? IS NULL AND MiddleInnitial IS NULL) AND([Mobile Fon" & _

"e] = ? OR ? IS NULL AND [Mobile Fone] IS NULL) AND ([Post Code] = ?) AND(Town =" & _

" ?)"





Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com"rriness" <an*******@discussions.microsoft.com> wrote in messagenews:03****************************@phx.gbl...
I'm getting an inconsistent failure when trying to save
data in ADO.Net.

I'm using an Access database with a simple query - SELECT StudentID, FirstName, LastName FROM Students - and have
no other users in the database. I keep getting the
message 'Operation must use an updateable query.'

Process seems to work on some PC's, not others. Is there a difference in .Net framework versions? Or some other
place to look for the problem? I'm using a datatable, and dataadapter with command builder, using disconnected
data. Help!

.

Nov 20 '05 #3
Hi,

If the query includes all of the columns in the table you might try using a
commandbuilder. You simply have to dim it to have it develop the necessary
update commands dynamically.

HTH,

Bernie Yaeger

"rriness" <an*******@discussions.microsoft.com> wrote in message
news:03****************************@phx.gbl...
I'm getting an inconsistent failure when trying to save
data in ADO.Net.

I'm using an Access database with a simple query - SELECT
StudentID, FirstName, LastName FROM Students - and have
no other users in the database. I keep getting the
message 'Operation must use an updateable query.'

Process seems to work on some PC's, not others. Is there
a difference in .Net framework versions? Or some other
place to look for the problem? I'm using a datatable, and
dataadapter with command builder, using disconnected
data. Help!

Nov 20 '05 #4
Cor
Hi rriness,

Process seems to work on some PC's, not others. Is there
a difference in .Net framework versions?


Yes

But you can load both on one computer

I hope this helps a little bit?

Cor
Nov 20 '05 #5
Its an alternative, the only problem is that Command Builder cannot handle
complex SQL statements and when joins are used it cant handle those either.

Regards - OHM

Bernie Yaeger wrote:
Hi,

If the query includes all of the columns in the table you might try
using a commandbuilder. You simply have to dim it to have it develop
the necessary update commands dynamically.

HTH,

Bernie Yaeger

"rriness" <an*******@discussions.microsoft.com> wrote in message
news:03****************************@phx.gbl...
I'm getting an inconsistent failure when trying to save
data in ADO.Net.

I'm using an Access database with a simple query - SELECT
StudentID, FirstName, LastName FROM Students - and have
no other users in the database. I keep getting the
message 'Operation must use an updateable query.'

Process seems to work on some PC's, not others. Is there
a difference in .Net framework versions? Or some other
place to look for the problem? I'm using a datatable, and
dataadapter with command builder, using disconnected
data. Help!


Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com
Nov 20 '05 #6
Hello,

As bernie points out , you can use the CommandBuilder class. However, it has
limitations, if you SQL queries are simple, then it may suit you to do this.

As far as the parameters are concerned, the ? characters are placeholders as
connecting with OLEDB you cannot have named parameters as you do in SQL, so
the Parameters have to be read in the order of the SQL statement and the ?
means an unknown value being returned.( Thats why its a question mark ).

Try the builder, you can find references in the help.

Regards - OHM
rriness wrote:
I was hoping not to have to build my own command - the
select statement meets the requirements of a single
table, with a one field primary key....

In your query how do you map the ? (parameters) to
fields/alternate values? (Each field has an original
value, a current value if changed since the data was
read). Thanks for your help.
-----Original Message-----
Here is an example of an update query I did with an access DB.
Compare with Yours.

Regards - OHM

'setup People Update Command

peopleUpdateCmd.CommandText = "UPDATE People SET Address1 = ?,
Address2 = ?, Address3 = ?, Address4 = ?, Country" & _

" = ?, County = ?, DOB = ?, Email = ?, FirstName = ?, [Home Phone] =
?, LastName " & _

"= ?, MiddleInnitial = ?, [Mobile Fone] = ?, [Post Code] = ?, Town =
? WHERE (ID " & _

"= ?) AND (Address1 = ?) AND (Address2 = ?) AND (Address3 = ?) AND
(Address4 = ?)" & _

" AND (Country = ?) AND (County = ?) AND (DOB = ?) AND (Email = ? OR
? IS NULL AN" & _

"D Email IS NULL) AND (FirstName = ?) AND ([Home Phone] = ?) AND
(LastName = ?) A" & _

"ND (MiddleInnitial = ? OR ? IS NULL AND MiddleInnitial IS NULL) AND
([Mobile Fon" & _

"e] = ? OR ? IS NULL AND [Mobile Fone] IS NULL) AND ([Post Code] =
?) AND (Town =" & _

" ?)"





Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com
"rriness" <an*******@discussions.microsoft.com> wrote in message
news:03****************************@phx.gbl...
I'm getting an inconsistent failure when trying to save
data in ADO.Net.

I'm using an Access database with a simple query - SELECT
StudentID, FirstName, LastName FROM Students - and have
no other users in the database. I keep getting the
message 'Operation must use an updateable query.'

Process seems to work on some PC's, not others. Is there
a difference in .Net framework versions? Or some other
place to look for the problem? I'm using a datatable, and
dataadapter with command builder, using disconnected
data. Help!

.


Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com
Nov 20 '05 #7
I'm already using a command builder (see original post).
I have a valid SELECT statement, valid connection, and
send a datatable into the update method of the data
adapter.

Part of my confusion with your suggested query using
parameters is correlating parameters to original and
current values of a datarow, and how to ignore a field
which cannot change (autonumber). It's starting to look
easier to just build a command and execute a non-query...

Thanks for your help.
-----Original Message-----
Hello,

As bernie points out , you can use the CommandBuilder class. However, it haslimitations, if you SQL queries are simple, then it may suit you to do this.
As far as the parameters are concerned, the ? characters are placeholders asconnecting with OLEDB you cannot have named parameters as you do in SQL, sothe Parameters have to be read in the order of the SQL statement and the ?means an unknown value being returned.( Thats why its a question mark ).
Try the builder, you can find references in the help.

Regards - OHM
rriness wrote:
I was hoping not to have to build my own command - the
select statement meets the requirements of a single
table, with a one field primary key....

In your query how do you map the ? (parameters) to
fields/alternate values? (Each field has an original
value, a current value if changed since the data was
read). Thanks for your help.
-----Original Message-----
Here is an example of an update query I did with an access DB. Compare with Yours.

Regards - OHM

'setup People Update Command

peopleUpdateCmd.CommandText = "UPDATE People SET Address1 = ?, Address2 = ?, Address3 = ?, Address4 = ?, Country" & _

" = ?, County = ?, DOB = ?, Email = ?, FirstName = ?, [Home Phone] = ?, LastName " & _

"= ?, MiddleInnitial = ?, [Mobile Fone] = ?, [Post Code] = ?, Town = ? WHERE (ID " & _

"= ?) AND (Address1 = ?) AND (Address2 = ?) AND (Address3 = ?) AND (Address4 = ?)" & _

" AND (Country = ?) AND (County = ?) AND (DOB = ?) AND (Email = ? OR ? IS NULL AN" & _

"D Email IS NULL) AND (FirstName = ?) AND ([Home Phone] = ?) AND (LastName = ?) A" & _

"ND (MiddleInnitial = ? OR ? IS NULL AND MiddleInnitial IS NULL) AND ([Mobile Fon" & _

"e] = ? OR ? IS NULL AND [Mobile Fone] IS NULL) AND ([Post Code] = ?) AND (Town =" & _

" ?)"





Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com "rriness" <an*******@discussions.microsoft.com> wrote in message news:03****************************@phx.gbl...
I'm getting an inconsistent failure when trying to save data in ADO.Net.

I'm using an Access database with a simple query - SELECT StudentID, FirstName, LastName FROM Students - and have no other users in the database. I keep getting the
message 'Operation must use an updateable query.'

Process seems to work on some PC's, not others. Is there a difference in .Net framework versions? Or some other place to look for the problem? I'm using a datatable, and dataadapter with command builder, using disconnected
data. Help!
.

Best Regards - OHMBest Regards - OHM

On**********@BTInternet.Com

.

Nov 20 '05 #8
Look,
If you really get stuck, let me have your project and I will try
and resolve it for you. Update commands can get really messy and
complicated. If your new to it, it can be a real pain.

Regards - OHM
rriness wrote:
I'm already using a command builder (see original post).
I have a valid SELECT statement, valid connection, and
send a datatable into the update method of the data
adapter.

Part of my confusion with your suggested query using
parameters is correlating parameters to original and
current values of a datarow, and how to ignore a field
which cannot change (autonumber). It's starting to look
easier to just build a command and execute a non-query...

Thanks for your help.
-----Original Message-----
Hello,

As bernie points out , you can use the CommandBuilder class.
However, it has limitations, if you SQL queries are simple, then it
may suit you to do this.

As far as the parameters are concerned, the ? characters are
placeholders as connecting with OLEDB you cannot have named
parameters as you do in SQL, so the Parameters have to be read in
the order of the SQL statement and the ? means an unknown value
being returned.( Thats why its a question mark ).

Try the builder, you can find references in the help.

Regards - OHM
rriness wrote:
I was hoping not to have to build my own command - the
select statement meets the requirements of a single
table, with a one field primary key....

In your query how do you map the ? (parameters) to
fields/alternate values? (Each field has an original
value, a current value if changed since the data was
read). Thanks for your help.

-----Original Message-----
Here is an example of an update query I did with an access DB.
Compare with Yours.

Regards - OHM

'setup People Update Command

peopleUpdateCmd.CommandText = "UPDATE People SET Address1 = ?,
Address2 = ?, Address3 = ?, Address4 = ?, Country" & _

" = ?, County = ?, DOB = ?, Email = ?, FirstName = ?, [Home Phone]
= ?, LastName " & _

"= ?, MiddleInnitial = ?, [Mobile Fone] = ?, [Post Code] = ?, Town
= ? WHERE (ID " & _

"= ?) AND (Address1 = ?) AND (Address2 = ?) AND (Address3 = ?) AND
(Address4 = ?)" & _

" AND (Country = ?) AND (County = ?) AND (DOB = ?) AND (Email = ?
OR ? IS NULL AN" & _

"D Email IS NULL) AND (FirstName = ?) AND ([Home Phone] = ?) AND
(LastName = ?) A" & _

"ND (MiddleInnitial = ? OR ? IS NULL AND MiddleInnitial IS NULL)
AND ([Mobile Fon" & _

"e] = ? OR ? IS NULL AND [Mobile Fone] IS NULL) AND ([Post Code] =
?) AND (Town =" & _

" ?)"





Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com
"rriness" <an*******@discussions.microsoft.com> wrote in message
news:03****************************@phx.gbl...
> I'm getting an inconsistent failure when trying to save
> data in ADO.Net.
>
> I'm using an Access database with a simple query - SELECT
> StudentID, FirstName, LastName FROM Students - and have
> no other users in the database. I keep getting the
> message 'Operation must use an updateable query.'
>
> Process seems to work on some PC's, not others. Is there
> a difference in .Net framework versions? Or some other
> place to look for the problem? I'm using a datatable, and
> dataadapter with command builder, using disconnected
> data. Help!
.


Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com
.


Best Regards - OHMBest Regards - OHM On**********@BTInternet.Com
Nov 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by William | last post: by
9 posts views Thread by Brad | last post: by
6 posts views Thread by rcoco | last post: by
6 posts views Thread by Suresh | last post: by
9 posts views Thread by SAL | last post: by
6 posts views Thread by insirawali | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.