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
FROM (Tbl_LocalTempAlarmsListBo
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
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
FROM (Tbl_LocalTempAlarmsListBo
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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?
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
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
ASKER
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
- 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???
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
- 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???
That final SQL should be sufficient to accomplish ther task at hand.
AW
AW
ASKER
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
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
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
Show the code that you are using for the OnClosing sub.
AW
ASKER
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.
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.
ASKER
Natloz,
Thanks for your help too!! It is much appreciated guys!!
Have split the points in recogition and gratitude of both your support.
Stuart
Thanks for your help too!! It is much appreciated guys!!
Have split the points in recogition and gratitude of both your support.
Stuart
AW