Updating a JOINed recordset

[zMisc]

Are there any tricks in updaitng a JOINed recordset?

I joned to tables and when I try to change a field on the recordset and
update it, I get this error:

"Unknown column 'CCDE' in 'where clause'.

CCDE is a field from one of the table and is not a field I am updating.

I uses MyODBC with VB6 and MySQL 5.

Any help greatly appreciated.

Tks
John

[Bill Karwin]

"zMisc" <yo********@hotmail.com> wrote in message
news:0I*******************@news-server.bigpond.net.au...

Are there any tricks in updaitng a JOINed recordset?

Apparently so! :-)

Can you post the query that contains the JOIN, a description of the table
schema (CREATE TABLE statements are best), and the VB code that performs the
update, and maybe somebody can offer a suggestion. Debugging by divination
isn't always possible (and isn't offered for free on a newsgroup).

Regards,
Bill K.

[zMisc]

Hi Bill,

Can you post the query that contains the JOIN, a description of the table
schema (CREATE TABLE statements are best), and the VB code that performs
the update, and maybe somebody can offer a suggestion. Debugging by
divination isn't always possible (and isn't offered for free on a
newsgroup).

Here's the select statement I used to create the recordset using MyODBC.

SELECT CL.`DA` AS `CLDA`, CL.`DE` AS `CLDE`, CL.`1` AS `CL1`, CL.`2` AS
`CL2`, CL.`3` AS `CL3`,
CC.`DA` AS `CCDA`, CC.`DE` AS `CCDE`, CC.`1` AS `CC1`,
CC.`2` AS `CC2`, CC.`3` AS `CC3`
FROM `CL` LEFT OUTER JOIN `CC` ON CL.`IDC` = CC.`PIDC` ORDER BY
CL.`1`

All the fields are text field.

I then update it as follows:

rset("CL2") = "test"
rset.update

I then get this error:

"Unknown column 'CCDE' in 'where clause'.

Tks
John

[Bill Karwin]

"zMisc" <yo********@hotmail.com> wrote in message
news:bk******************@news-server.bigpond.net.au...

Here's the select statement I used to create the recordset using MyODBC.

SELECT CL.`DA` AS `CLDA`, CL.`DE` AS `CLDE`, CL.`1` AS `CL1`, CL.`2` AS
`CL2`, CL.`3` AS `CL3`,
CC.`DA` AS `CCDA`, CC.`DE` AS `CCDE`, CC.`1` AS `CC1`,
CC.`2` AS `CC2`, CC.`3` AS `CC3`
FROM `CL` LEFT OUTER JOIN `CC` ON CL.`IDC` = CC.`PIDC` ORDER BY
CL.`1`

All the fields are text field.

I then update it as follows:

rset("CL2") = "test"
rset.update

I then get this error:

"Unknown column 'CCDE' in 'where clause'.

I see 'CCDE' is actually not the name of a column in any of your tables.
It's a column alias you've define in the query. Apparently, positioned
updates to result sets have a problem with column aliases.

In any case, it is not allowed in MySQL (or in the SQL standard) to use a
column alias in a WHERE clause. It seems that the rset.update method is
generating an UPDATE statement, and it is trying to use column aliases.

You might be able to use SQLColAttribute to tell you whether the CL2 column
is updatable.
See
http://msdn.microsoft.com/library/de...lattribute.asp

You might not be able to use this particular query to provide an updatable
result set. Often in a one-to-many join, the "many" side cannot be
updatable.
See
http://msdn.microsoft.com/library/de...t_metadata.asp

Regards,
Bill K.

[Young]

Hi Bill,

As usual thank you for your help.

I have rewritten the code so the update is done on separate recordset.

For exmample:

Create a recordset for the join of the 2 tables - this is displayed on the
grid

When the user edit the record:
Create a recordset for table CL using the keys from the above joined
recordset then update
Create a recordset for table CC using the keys from the above joined
recordset then update

this works as expected but now I have a problem with refresh the original
joined recordset that is displayed on the grid. As expected, the updated
data is not shown on the joined recordset on the grid.

I used the ADO resync method:

- JoinedRecordSet.Resync adAffectCurrent, adResyncAllValues

to refresh the underlying data but it does not work. The same resync works
for an Access and MS SQL database.

If I refresh the joinedrecordset then it will show the updated values. The
problem with this is if I have a large number of recordset, edit update will
be painfully slow due to the refresh.

Any suggestions?

Tks
"Bill Karwin" <bi**@karwin.com> wrote in message
news:du*********@enews2.newsguy.com...

"zMisc" <yo********@hotmail.com> wrote in message
news:bk******************@news-server.bigpond.net.au...

Here's the select statement I used to create the recordset using MyODBC.

SELECT CL.`DA` AS `CLDA`, CL.`DE` AS `CLDE`, CL.`1` AS `CL1`, CL.`2` AS
`CL2`, CL.`3` AS `CL3`,
CC.`DA` AS `CCDA`, CC.`DE` AS `CCDE`, CC.`1` AS `CC1`,
CC.`2` AS `CC2`, CC.`3` AS `CC3`
FROM `CL` LEFT OUTER JOIN `CC` ON CL.`IDC` = CC.`PIDC` ORDER BY
CL.`1`

All the fields are text field.

I then update it as follows:

rset("CL2") = "test"
rset.update

I then get this error:

"Unknown column 'CCDE' in 'where clause'.

I see 'CCDE' is actually not the name of a column in any of your tables.
It's a column alias you've define in the query. Apparently, positioned
updates to result sets have a problem with column aliases.

In any case, it is not allowed in MySQL (or in the SQL standard) to use a
column alias in a WHERE clause. It seems that the rset.update method is
generating an UPDATE statement, and it is trying to use column aliases.

You might be able to use SQLColAttribute to tell you whether the CL2
column is updatable.
See
http://msdn.microsoft.com/library/de...lattribute.asp

You might not be able to use this particular query to provide an updatable
result set. Often in a one-to-many join, the "many" side cannot be
updatable.
See
http://msdn.microsoft.com/library/de...t_metadata.asp

Regards,
Bill K.

[Bill Karwin]

"Young" <yo********@hotmail.com> wrote in message
news:vz***************@news-server.bigpond.net.au...

When the user edit the record:
Create a recordset for table CL using the keys from the above joined
recordset then update
Create a recordset for table CC using the keys from the above joined
recordset then update
Are you aware that you can execute an UPDATE statement, without having to
open another recordset for each table?
If I refresh the joinedrecordset then it will show the updated values. The
problem with this is if I have a large number of recordset, edit update
will be painfully slow due to the refresh.

I understand this is a very common problem with data grids in client/server
applications. Unfortunately, I don't know anything about it since I've
never written an ADO or VB application, and I eschew data-aware controls in
general.

I can only speculate, but is there a way to modify the values of the grid at
the same time as you UPDATE the underlying tables? But in a way that
_doesn't_ create a grid event to cause the resultset to execute the same
update.

Regards,
Bill K.