Solved

Update SQL for Joined Tables

Posted on 2004-08-16
12
364 Views
Last Modified: 2010-05-18
Hi,

First of all thanks for clicking

Have created a DataAdapter that I join two tables together (INNER JOIN). Because it is a joined table it doesn't create the INSERT and UPDATE Sql because apparently the Data Adpater Wizard cannot do this since it's a joined table.

Have to create Update SQL myself.

The SELECT sql created is of many fields from two tables

SELECT Tbl_Alarms.AlarmRefNo, Tbl_LocalTempAlarmsListBox.Description, Tbl_Alarms.BackColor, Tbl_LocalTempAlarmsListBox.Renew, Tbl_LocalTempAlarmsListBox.Status, Tbl_LocalTempAlarmsListBox.SLevel, Tbl_LocalTempAlarmsListBox.Installed, Tbl_LocalTempAlarmsListBox.Name, Tbl_Alarms.ForeColor,....more fields
FROM (Tbl_LocalTempAlarmsListBox INNER JOIN Tbl_Alarms ON Tbl_LocalTempAlarmsListBox.Alarm = Tbl_Alarms.AlarmRefNo)

Have many fields(from the two tables) that need to be displayed in a DataGrid, but only two fields can be changed programmatically (namely ForeColor and BackColor)

Have put this SQL in the INSERT CommandText

INSERT INTO Tbl_Alarms(AlarmRefNo, ForeColor, BackColor) VALUES (?, ?, ?)

And this SQL in the UPDATE CommandText

UPDATE    Tbl_Alarms
SET              AlarmRefNo = ?, ForeColor = ?, BackColor = ?
WHERE     (AlarmRefNo = ?)

When I do the Update command

daAlarm.Update(dsAlarms)

it crashes with this error

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Is it because I'm trying to update a table that was originally created from SQL that joined two tables.

Any ideas??

Thank you for taking time to read this post.

Best regards
Stuart
0
Comment
Question by:StuartK
[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
  • 5
  • 5
  • 2
12 Comments
 
LVL 7

Assisted Solution

by:natloz
natloz earned 100 total points
ID: 11813441
Isn't AlarmRefNo the primary key?

Maybe change to:

UPDATE    Tbl_Alarms
SET              ForeColor = ?, BackColor = ?
WHERE     (AlarmRefNo = ?)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11813473
it is not so much that the DataAdapter cannot generate the UPDATE and INSERT statements when using JOINed tables, but SQL (the Language) is not designed to UPDATE or INSERT into a set of JOINED tables.  That is outside the intended design of SQL.

AW
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 75 total points
ID: 11813541
you make this statement:

Have many fields(from the two tables) that need to be displayed in a DataGrid, but ---only two fields can be changed programmatically (namely ForeColor and BackColor)---(my 'emphasis')

<my comment> You then proceed to insert 3 values:
</my comment>


INSERT INTO Tbl_Alarms(AlarmRefNo, ForeColor, BackColor) VALUES (?, ?, ?)
<my comment> and also UPDate the same three values
</my comment>


UPDATE    Tbl_Alarms
SET              AlarmRefNo = ?, ForeColor = ?, BackColor = ?
WHERE     (AlarmRefNo = ?)


whenere it would seem, from your original condition (only two fields can be changed programmatically), that the two blocks should be:


INSERT INTO Tbl_Alarms(ForeColor, BackColor) VALUES (?, ?)

and

UPDATE    Tbl_Alarms
SET           ForeColor = ?, BackColor = ?
WHERE     AlarmRefNo = ?

As natloz asked, is the field AlarmRefNo the primary Key on the table (as it appears to be from the error message that you are getting) - By the way, what database package are you using?

AW

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:StuartK
ID: 11815069
Thanks Guys,

Tried removing AlarmRefNo form SET and INSERT INTO

Gave a quick try. Original message gone, but now have "Indeox or primary key cannot contain a Null value"

The AlarmRefNo is the primary key

and the DB is Access2000 (Jet 4.0)

Sorry need to rush. Got issues today!!

If you can direct me, just drop a quick note. otherwise the next time I get time to look into it is 12 hours from now.

Your help is appreciated guys

Stuart
0
 
LVL 7

Expert Comment

by:natloz
ID: 11815689
In your access Table, is the AlarmRefNo just a generated number (no logic to it?) If so is it not set to AUTONUMBER? Do you have to manage the Primary Key yourself?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11819073
If AlarmRefNo is NOT an Autonumber field, then you will need to determine the current largest value in the table, increment it by 1 before you INSERTa NEW record into the table.  You should NOT need to UPDATE the AlarmRefNo field, in any case, as it already has a value, and it is VERY VERY unusual to ever change the current value of a Primary Key in a table.

are you using the SQL in code (VBA) that you (or a wizard) has generated, or how are you making use of the SQL?

If it is in code, then you can generate a Select query that will return the CURRENT maximum value of AlarmRefNo, to be executed before you exdcute the INSERT statement.  You might post some more of the code that you are using (or trying to use).

AW
0
 

Author Comment

by:StuartK
ID: 11819348
Hi natloz and Arthur,

To be perfectly honest, I don't really think there's any need for INSERT SQL in my application. I've put it in there because I once read that you need INSERT SQL and UPDATE SQL to do an update. It was a case of "stick it in anyway"

- I'm joining two tables to display in a Janus GridEx (consider as a DataGrid). Namely, Tbl_Alarms and Tbl_LocalTempAlarmsListBox. The GridEx needs to show several fields from both tables, so the user has the maximum info available.

- There is no facility to add a new record. The number of records is SET!!

- There is no facility to change any of the Columns EXCEPT ForeColor and BackColor.

- AlarmRefNo is the primary key of Tbl_Alarms which also has the fields ForeColor and BackColor. It is not an Autonumber, but this is irrelevent now since we know we don't want to add any new records.

- AlarmRefNo (the primary key) will never change. It's simply in the SQL as a reference.

- This SQL is part of a DataAdapter in VB.NET that is connected to a Access 2000 DB.

- The DataAdapter wizard did not create UPDATE CommandText (or INSERT or DELETE) because we are joining two tables. So I am trying to create it myself.

This is what I put into the Update CommandText of the DataAdapter

UPDATE    Tbl_Alarms
SET              ForeColor = ?, BackColor = ?
WHERE     (AlarmRefNo = ?)

Was this what you were looking for guys???
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11820270
That final SQL should be sufficient to accomplish ther task at hand.

AW
0
 

Author Comment

by:StuartK
ID: 11820494
Hi Arthur,

Have my main application, and have put this UPDATE and INSERT SQL in the DataAdapter. When I run, and Simply open and close the form with the GridEx(dataGrid) the Update method is run in the "Protected Overrides Sub OnClosing" event. This error occurs

"Index or primary key cannot contain a Null value."

I have not changed any aspect of the Table before running this Update method in the OnClosing event, so how can it say the primary key is Null?

Any possible ideas would be gratefully accepted, Arthur.

Stuart
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 11821915
it would appear the the INSERT sql is being executed.  No idea why that would happen.  alternatively, what is the current value of the AlarmRefNo parameter that you are using in the DataAdapter?

Show the code that you are using for the OnClosing sub.

AW
0
 

Author Comment

by:StuartK
ID: 11828869
Hi Arthur,

Your request to post the OnClosing sub allowed me to question a couple of issues, and got it sorted.

This was my original code

GridEx.UpdateData (This is Janus GridEx object. This method updates from GridEx to Dataset)
daAlarms.Update(dsAlarms)

Was causing error at .Update

Put dsAlarms.AcceptChanges before .UpdateData and it allows closing without errors now.

Thanks for help Arthur.

0
 

Author Comment

by:StuartK
ID: 11828902
Natloz,

Thanks for your help too!! It is much appreciated guys!!

Have split the points in recogition and gratitude of both your support.

Stuart
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 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