Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tad
Starting Member
31 Posts |
 Posted - 2002-06-11 : 09:59:43
|
| I have a form based on a join that allows the user to edit fields only on one table from the join (see below). If the user edits the same column on a specific row twice, the user gets a Write Conflict message box. However, if the user nevers edits the same column on a specific row, meaning, if the edit every field on the screen once, then the user move off any field and/or close the form without an error.I've reviewed Article Q280730 which talks about bit columns being set to Null. All the columns have values on the records being edited.Unique Table:KYLAB_SPEC_DETAILRecord Source:SELECT KYLAB_LAB_TEST.TEST_NAME, KYLAB_PROPERTY.PROPERTY, KYLAB_SPEC_DETAIL.* FROM KYLAB_SPEC_DETAIL LEFT JOIN KYLAB_PROPERTY ON KYLAB_PROPERTY.PROPERTY_NO = KYLAB_SPEC_DETAIL.PROPERTY_NO LEFT JOIN KYLAB_LAB_TEST ON KYLAB_LAB_TEST.TEST_NO = KYLAB_SPEC_DETAIL.TEST_NO WHERE KYLAB_SPEC_DETAIL.SPEC_NO = '114' ORDER BY KYLAB_SPEC_DETAIL.SEQ_NOAny suggestions. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 10:18:04
|
| The problem comes from the LEFT JOINs. The left join always returns all of the rows of the left hand table, whether or not there are any matching rows in the right hand table. If someone tries to edit a column in the right hand table that doesn't match a row in the left hand table, the database cannot update anything because there is no such row.Secondly, whenever editing two or more joined tables, technically you cannot update more than one base table unless each row in each table can be uniquely identified.You may want to consider changing this so that edits only occur on base tables, not views or joined query statements. |
 |
|
|
tad
Starting Member
31 Posts |
Posted - 2002-06-11 : 10:29:22
|
The Unique Table specifies the BASE table to be edited. And in this case the inner join does produce the same results as the left join.quote:
The problem comes from the LEFT JOINs. The left join always returns all of the rows of the left hand table, whether or not there are any matching rows in the right hand table. If someone tries to edit a column in the right hand table that doesn't match a row in the left hand table, the database cannot update anything because there is no such row.Secondly, whenever editing two or more joined tables, technically you cannot update more than one base table unless each row in each table can be uniquely identified.You may want to consider changing this so that edits only occur on base tables, not views or joined query statements.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 10:45:24
|
| 1. If LEFT JOIN and INNER JOIN produce the same results, then you should stick with INNER JOIN; it will reduce the chances of something like this occurring.2. It might be a locking problem. How do the users edit each row? What's the program being used? If it's a bound data grid, like an Access form or query window, or a VB datagrid control, it might not release the lock after the row is updated the first time.Just a WAG.EDIT: Hmmmm, I NEED TO READ THE FORUM TITLE FIRST, ya think?????If this is an Access database, OH MY GOD YEAH you are gonna have locking problems, especially if the database is used by more than one person at a time. I'm not talking about 2 people hitting the same row at the same time either; any kind of locking in Access sucks the big moose bazooka whenever more than one person is using the database. I'm not too sure about Access 2000/Jet 4.0, but Access 97/Jet 3.x is ATROCIOUS with locks.Edited by - robvolk on 06/11/2002 10:53:12 |
|
|
|
tad
Starting Member
31 Posts |
Posted - 2002-06-11 : 11:31:58
|
The database is SQL2K the UI is Access2K, via a Form.quote:
1. If LEFT JOIN and INNER JOIN produce the same results, then you should stick with INNER JOIN; it will reduce the chances of something like this occurring.2. It might be a locking problem. How do the users edit each row? What's the program being used? If it's a bound data grid, like an Access form or query window, or a VB datagrid control, it might not release the lock after the row is updated the first time.Just a WAG.EDIT: Hmmmm, I NEED TO READ THE FORUM TITLE FIRST, ya think?????If this is an Access database, OH MY GOD YEAH you are gonna have locking problems, especially if the database is used by more than one person at a time. I'm not talking about 2 people hitting the same row at the same time either; any kind of locking in Access sucks the big moose bazooka whenever more than one person is using the database. I'm not too sure about Access 2000/Jet 4.0, but Access 97/Jet 3.x is ATROCIOUS with locks.Edited by - robvolk on 06/11/2002 10:53:12
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 14:23:04
|
| Is it linked to SQL Server using ODBC, or does it use ADO/OLE DB? And what's the exact error message and number that you're getting? |
|
|
|
tad
Starting Member
31 Posts |
Posted - 2002-06-11 : 16:15:36
|
I'm using an ADP to access SQL2K, thus using an Data Link(Ole Db). The dialog box is:Write ConflictThis record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes. quote:
Is it linked to SQL Server using ODBC, or does it use ADO/OLE DB? And what's the exact error message and number that you're getting?
|
|
|
|
|
|
|
|
|
Write Conflict