• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

update an inner join statement

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
al4629740
Asked:
al4629740
  • 4
  • 4
1 Solution
 
chaauCommented:
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
 
al4629740Author Commented:
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
 
chaauCommented:
Do you have primary keys in your tables?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
al4629740Author Commented:
ID in both
0
 
chaauCommented:
Are you including the ID of the updatable table into the datagrid?
0
 
al4629740Author Commented:
I have what I stated above.

Doesn't the * include it?
0
 
chaauCommented:
I thought this was an underlying query. I meant: include the column into your datagrid in VB6
0
 
al4629740Author Commented:
So I can't use *
0
 
Martin LissOlder than dirtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now