"Unique Table" and "Resync Command" problems with self-ref query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: shadow123 (shadow123_at_discussions.microsoft.com)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 13:05:04 -0800

Problem Abstract: I am unable to use "Unique Table" and "Resync Command"
properties with self-referencing queries in ADO 2.8.
 
System setup: SQL server 2000, MDAC 2.8 SP1 on Windows XP SP2
 
Database structures: (There are others but this one is sufficient to
reproduce the problem)
CREATE TABLE [dbo].[Employee] (
 [Employee] [varchar] (36) CONSTRAINT PK_emp PRIMARY KEY,
 [Company] [varchar] (36) NOT NULL ,
 [Car] [varchar] (36) NOT NULL ,
 [EmployeeName] [varchar] (50),
 [Superviser] [varchar] (36)
)
 
Problematic Query:
"select EMPLOYEE.employee, EMPLOYEE.employeeName, EMPLOYEE.superviser,
E2.employee name
FROM EMPLOYEE LEFT OUTER JOIN EMPLOYEE AS E2 ON E2.employee =
EMPLOYEE.superviser "
 
Sample VB code which demonstrates the problem: (not calling resync in the
example)
 
Dim rs As Recordset
Dim conn As Connection
 

Private Sub Command1_Click()
    Set conn = New Connection
    Set rs = New Recordset
    conn.ConnectionString = "Provider=sqloledb;Data Source=name;Initial
Catalog=ADOTest;User Id=sa;Password=;"
    conn.CursorLocation = adUseClient
    conn.Open conn.ConnectionString, "sa", "", 0
    rs.Open "SELECT employee.employee, employee.employeename,
employee.superviser, e2.employeename as supname FROM EMPLOYEE LEFT OUTER JOIN
employee as e2 on e2.employee = employee.superviser ", _
        conn, adOpenKeyset, adLockOptimistic
    If Not rs.EOF Then
        rs.Properties("Unique Table").Value = "employee"
        rs.Properties("Resync Command").Value = _
            "SELECT employee.employee, employee.employeename,
employee.superviser, e2.employeename as supname FROM EMPLOYEE LEFT OUTER JOIN
employee as e2 on e2.employee = employee.superviser where EMPLOYEE.employee =
? "
        rs.Fields("employeename").Value = "Name"
        rs.Update
    End If
End Sub
 
Problem 1
Originally I tried to write the code with the query spelled exactly the way
it is in the Problematic Query example. That resulted in an error "Row
cannot be located for updating. Some values may have been changed since it
was last read." I believe this happened because the sql issued to the SQL
server by ADO looked like this
 
"exec sp_executesql N'UPDATE "ADOTest".."EMPLOYEE" SET "employeename"=@P1
WHERE "employee"=@P2 AND "employeename"=@P3 AND "Employee" IS NULL', N'@P1
varchar(4),@P2 varchar(36),@P3 varchar(4)', 'Name',
'C0A67307-585E-4617-8124-2E91F116586D', 'Name'"
 
Since employee cannot be NULL and the guid simultaneously 0 rows were
updated, which resulted in the error. This problem led me to believe that
ADO was assuming the main employee table and the self-reference were the same
table and generated where elements for both. I removed "Unique Table" and
resync command properties and the problem persisted.
 
Later on and purely by accident I discovered that changing the
capitalization of the table name in the FROM clause and the table name being
joined to EMPLOYEE and employee respectively (as shown in the example ) fixed
this problem, and the Update worked correctly.
 
Problem 2.
I got very excited, but as soon as I added the "Unique Table" and "Resync
Command" I got a new error which is "Unique Table is nonexistent or not
completely specified." I suspect that now that ADO realizes that there are
two unique tables it is confused as to which one should be the unique table
since this comparison as opposed to the table name one, is not case
sensitive. This is of course just a guess on my part. The sample VB code in
its current rendition results in this error.
 
I need to be able to use "Unique Table" and "Resync Command" properties in
order to assure that all joined data (there are more joins in the actual
query) gets resynced after a foreign key update.
 
At this point I am finding myself out of options and am looking for any and
all posible solutions. If there is any way I can get this to work that would
be preferable, but if not I will take work arounds as well. Someone has
suggested using views, but I could not get that to work as it had the same
problem, and I would rather not join in the actual view.
 
Thank you very much for your help.

Relevant Pages

  • Re: "Row cannot be located for updating. Some values may have been changed since it was last re
    ... It appears that ADO's SQL parser is case sensitive ... > the same it will break, but if they are spelled differently (e.g. EMPLOYEE and employee) the ... You should be writing the Resync Command for a joined query not relying on what ... ADO generates. ...
    (borland.public.delphi.database.ado)
  • Re: "Row cannot be located for updating. Some values may have been changed since it was last re
    ... >pretty sure my 'Resync Command' works although it never gets to resyncing. ... >Obviously it will think that a row is deleted since there is no way employee is the guid and is null at the same time. ... >This example does not use adCriteriaKey but if I did the effect would be the same it just ... If I open the ADO recordset and then assign it to the TADOQuery it works as well. ...
    (borland.public.delphi.database.ado)
  • Re: Help on resolving record conflicts with ADO
    ... I misunderstood your note about adding a resync command. ... include any fields from another table through a left outer join (the other ... table's field is for display purposes only) then the above code crashes ... My select statement contains the primary key for ...
    (borland.public.delphi.database.ado)
  • Re: Refreshing a current record in a DataSet
    ... Can you please show me the Resync Command property in standard Delphi ADO ... Brian Bushay (TeamB) ...
    (borland.public.delphi.database.ado)