Solved

update an inner join statement

Posted on 2014-02-05
10
337 Views
Last Modified: 2014-03-19
have a datagrid in vb6.  I performed an inner join statement in sql to retreive a recordset from our SQL DB.  When I try to edit the record in the datagrid, I get the following error:

Insufficient key column information for updating or refreshing.

Shouldn't I be able to update an inner join statement?
0
Comment
Question by:al4629740
  • 4
  • 4
10 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
You can update inner joined queries. All you need to do is to make sure that columns that make the primary key of the table to be updated are included into the select statement. Imagine this situation:
Table1: Primary key Column1, Column2, Columns3
Table2: Primary key Column1
SELECT Table1.Column1, Table1.Column2, Table2.Column1
FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column1

Open in new window

The above statement will not be converted to the update statement for Table1, as Table1.Column3 is not included into the SELECT clause. Modify the statement like this:
SELECT Table1.Column1, Table1.Column2, Table2.Column1, Table1.Column3
FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column1

Open in new window

0
 

Author Comment

by:al4629740
Comment Utility
This is what I had.  Wouldn't that include it?
select * from tblYAT1 Y1 Inner Join tblYAT2 Y2 on Y1.ID = Y2.ID where Y1.Committee = 'Group1' And Y1.Fiscal = 2014 order by Month, [Last Name]

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Do you have primary keys in your tables?
0
 

Author Comment

by:al4629740
Comment Utility
ID in both
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Expert Comment

by:chaau
Comment Utility
Are you including the ID of the updatable table into the datagrid?
0
 

Author Comment

by:al4629740
Comment Utility
I have what I stated above.

Doesn't the * include it?
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
I thought this was an underlying query. I meant: include the column into your datagrid in VB6
0
 

Author Comment

by:al4629740
Comment Utility
So I can't use *
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now