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

Update SQL for Joined Tables

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
StuartK
Asked:
StuartK
  • 5
  • 5
  • 2
2 Solutions
 
natlozCommented:
Isn't AlarmRefNo the primary key?

Maybe change to:

UPDATE    Tbl_Alarms
SET              ForeColor = ?, BackColor = ?
WHERE     (AlarmRefNo = ?)
0
 
Arthur_WoodCommented:
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
 
Arthur_WoodCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
StuartKAuthor Commented:
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
 
natlozCommented:
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
 
Arthur_WoodCommented:
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
 
StuartKAuthor Commented:
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
 
Arthur_WoodCommented:
That final SQL should be sufficient to accomplish ther task at hand.

AW
0
 
StuartKAuthor Commented:
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
 
Arthur_WoodCommented:
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
 
StuartKAuthor Commented:
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
 
StuartKAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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