data adapter update fails

[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!

[One Handed Man [ OHM ]]

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!

[rriness]

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!

.

[Bernie Yaeger]

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!

[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

[One Handed Man [ OHM ]]

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

[One Handed Man [ OHM ]]

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

[rriness]

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

.

[One Handed Man [ OHM ]]

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