Update SQL for Joined Tables
Posted on 2004-08-16
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
SET AlarmRefNo = ?, ForeColor = ?, BackColor = ?
WHERE (AlarmRefNo = ?)
When I do the Update command
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.
Thank you for taking time to read this post.