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 5 2503
"zMisc" <yo********@hot mail.com> wrote in message
news:0I******** ***********@new s-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.
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
"zMisc" <yo********@hot mail.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.
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 , adResyncAllValu es
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.co m> wrote in message
news:du******** *@enews2.newsgu y.com... "zMisc" <yo********@hot mail.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" <yo********@hot mail.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Roy Adams |
last post by:
Hi everyone
I'm trying to build a shopping cart app using a db the part I'm stuck
on is the fact that, if someone adds a product that they have
previously added to the cart.
I've got it set up to check whether the size and colour fields match
what's in the db, if they do then you've already added this item with
the same colours and size so,...
|
by: RC |
last post by:
My code below will loop through all the records in the table, and when
the if statement is true it goes to the
***Me.ContainerNumberProductsTable = GETContainerNumber.Value*** bit
like should but it does not make the change/update in the table. Any
ideas?
Dim rst As Object
Set rst = Me.Recordset.Clone
If Not rst.EOF Then Me.Bookmark =...
|
by: Carl |
last post by:
Hi,
I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with one-to-one relationship but on this
occasion
I must keep username/password details within a seperate table.
Here's the basic specs and database schema:...
|
by: barret bonden |
last post by:
(closest newsgroup I could find)
Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype.
/asp_data3_add.asp, line 30
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
|
by: Jeff |
last post by:
For years I have been using VBA extensively for updating data to tables
after processing. By this I mean if I had to do some intensive processing
that resulted in data in temp tables, I would have VBA code that wrote the
results of that away to the db, either creating new records or updating
existing records, whichever was relevant. This may...
| |
by: Alexey.Murin |
last post by:
The application we are developing uses MS Access 2003 database (with
help of ADO).
We have noticed that during massive records updating the size of the
mdb file increases dramatically (from 3-4 to 200-300 Mb).
After compacting the file shrinks back to 3-4 Mb.
I have performed the following experiment. I created a test database
containing...
|
by: Hexman |
last post by:
I've come up with an error which the solution eludes me. I get the
error:
>An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in HRTest.exe
>
>Additional information: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
It occurs when I...
|
by: John T Ingato |
last post by:
I have a contacts table with name address and such but are missing all phone
numbers in the phone number fields. I have just received an updated
customer list in Excel and have imported into a new table.
Can I run a query that will take the phone number only from the new table
and update the old tables phone number fields from empty to the...
|
by: Fresco |
last post by:
On large invoices, it's better cash flow at times to pay only part now, part later. I tried using a totals query to build a recordset that show a listing of invoices that have balances owing with the original balance and the sum of any and all payments that have been made.
I am using this query to populate a form which would allow me to select...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |