Solved

Update SQL for Joined Tables

Posted on 2004-08-16
12
360 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

19 Experts available now in Live!

Get 1:1 Help Now