?
Solved

update an inner join statement

Posted on 2014-02-05
10
Medium Priority
?
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 1500 total points
ID: 39837813
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
ID: 39837823
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 25

Expert Comment

by:chaau
ID: 39837827
Do you have primary keys in your tables?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:al4629740
ID: 39837834
ID in both
0
 
LVL 25

Expert Comment

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

Author Comment

by:al4629740
ID: 39837849
I have what I stated above.

Doesn't the * include it?
0
 
LVL 25

Expert Comment

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

Author Comment

by:al4629740
ID: 39884538
So I can't use *
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39940431
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Suggested Courses
Course of the Month8 days, 16 hours left to enroll

764 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