data adapter update fails

  • Thread starter Thread starter rriness
  • Start date Start date
R

rriness

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!
 
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 (e-mail address removed)
 
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 (e-mail address removed)
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!
Click to expand...


.
Click to expand...
 
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
 
Hi rriness,

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

Yes

But you can load both on one computer

I hope this helps a little bit?

Cor
 
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 said:
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
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)
 
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

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 (e-mail address removed)
rriness said:
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!
Click to expand...


.
Click to expand...
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)
 
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

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 (e-mail address removed)
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!


.
Click to expand...
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)


.
Click to expand...
 
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

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

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 (e-mail address removed)
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!


.
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)


.
Click to expand...
Click to expand...

Best Regards - OHMBest Regards - OHM (e-mail address removed)
 
Back
Top