[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Update or insert in adoquery with a join query

Posted on 2006-03-23
8
Medium Priority
?
1,617 Views
Last Modified: 2010-04-05
Hello Guys,

Sometimes things seem to be easy but they have another face and this is my new dilemma here.

I have a main table in my MS SQL 2000 that has two other joined to it

In my form I have a adoconnection + AdoQuery

This is my query:

SELECT      CADUSO.RH_FUNC_NOME, CADUSO.RH_DEPTO_NOME,
                 CADUSO.RH_EMPRESA_NOME, AGENDA_USO_CONEX.AG_ID,    AGENDA_USO_CONEX.AG_CODUSO
FROM         AGENDA_USO_CONEX
INNER JOIN CADUSO ON AGENDA_USO_CONEX.AG_CODUSO = CADUSO.RH_FUNC_ID

How can I do when I need to change the information? I have an error when I post. Can't I update, insert in Adoquery with
a join query?

Thanks
0
Comment
Question by:hidrau
  • 3
  • 3
  • 2
8 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 16272410
prepare two independent queries for updating separately each table
0
 
LVL 1

Author Comment

by:hidrau
ID: 16272664
Sorry I didn't undertand.

This query I use in my dbgrid, and I make all the changes (insert, edit) in it. How two queries?

Thanks
0
 
LVL 17

Expert Comment

by:mokule
ID: 16273045
If You are trying to change value in AGENDA_USO_CONEX  table You must execute command something like
UPDATE AGENDA_USO_CONEX SET
and another command for updating
UPDATE CADUSO SET ...

You can't update two tables in one command
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
LVL 13

Expert Comment

by:BlackTigerX
ID: 16274682
that's easy, just create a view out of the query that joins the tables, you can then select/insert/update/delete from that view like it was a table, and the changes willl be reflected into the underlying tables
0
 
LVL 13

Accepted Solution

by:
BlackTigerX earned 2000 total points
ID: 16276869
or what you might need, is just to include the primary key fields, so the engine can resolve which records you are trying to change

SELECT      CADUSO.RH_FUNC_ID, AGENDA_USO_CONEX.AG_CODUSO
                 CADUSO.RH_FUNC_NOME, CADUSO.RH_DEPTO_NOME,
                 CADUSO.RH_EMPRESA_NOME, AGENDA_USO_CONEX.AG_ID,
FROM         AGENDA_USO_CONEX (nolock)
INNER JOIN CADUSO (nolock) ON AGENDA_USO_CONEX.AG_CODUSO = CADUSO.RH_FUNC_ID

I guess you were just missing CADUSO.RH_FUNC_ID
0
 
LVL 1

Author Comment

by:hidrau
ID: 16278885
Hello blackTigerX, so I can create a view and update , insert and everything. But a simple query with join is not possible, right????

0
 
LVL 1

Author Comment

by:hidrau
ID: 16279052
BlackTigerx, Can I do the same in access table?
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 16282213
if you want to be able to update that using a update statement, you can't do it in access, however, if by update you mean like you use the data (that results from running this query) in a DBGrid and you want to be able to make changes there and update the data that way, then you can use this query in any other database (that allows joins)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question