Solved

Update SQL for Joined Tables

Posted on 2004-08-16
12
361 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
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…

821 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