Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    3

    Unanswered: Using Resync with ADO and complex SQL Query statement using left outer joins

    I'm actually working with visual basic 6.0 and ADO 2.5. When I try to resync a recordset generated from a complex sql query, I get this error:

    Run-time error '-2147217885 (80040e23)'
    Key value for this row was changed or deleted at the data store. The local row is now deleted.

    This issue appends only when I try to resync a row which has a joined on a null row from the other table.

    For example: I got two table ( vfac and zcardex). I use this custom query: select vfac.kvfac, zcardex.kzcardex from vfac left outer join zcardex on zcardex.kzcardex=vfac.kzcardex. When I use the resync function and the zcardex.kzcardex value is NULL. I get the error. Otherwise, everything works fine.

    I then found that it was possible to use two properties of the recordset to resolve this problem. Unfortunatly it doesn't seem to work. I get a new error.

    Here is my source code:
    '*************************************

    Dim m_Rs As ADODB.Recordset
    Set m_Rs = New ADODB.Recordset

    m_strConn = "driver={SQL Server};server=" & m_Conn.Name & ";uid=" & m_Conn.UserName & ";pwd=" & m_Conn.Password & ";database=" & m_Conn.Database & IIf(InStr(m_Conn.Name, ".") <> 0, ";Network=DBMSSOCN", "")

    Query = "select vfac.kvfac, vfac.dttrans, vfac.kzcardex, vfac.notrans, zcardex.nocardex, zcardex.nomprenom from vfac left outer join zcardex on vfac.kzcardex=zcardex.kzcardex order by vfac.kvfac"

    m_Rs.Open Query, m_strConn, adOpenKeyset, adLockOptimistic

    m_Rs.Properties("Resync Command") = "select vfac.kvfac, vfac.dttrans, vfac.kzcardex, vfac.notrans, zcardex.nocardex, zcardex.nomprenom from vfac left outer join zcardex on vfac.kzcardex=zcardex.kzcardex where vfac.kvfac = ?"

    m_Rs.Properties("Unique Table") = "vfac"

    m_Rs.Resync adAffectCurrent

    m_Rs.Close
    set m_Rs = nothing

    '*************************************



    Thank you,
    Mathieu Tremblay

  2. #2
    Join Date
    Nov 2002
    Posts
    5

    Re: Using Resync with ADO and complex SQL Query statement using left outer joins

    Not sure if this helps but two things came to mind, use requery instead of resync (I've never used resynch) and account for the null in you SQL statement

    select vfac.kvfac, zcardex.kzcardex from vfac left outer join zcardex on zcardex.kzcardex=vfac.kzcardex where zcardex.kzcardex <> null


    Originally posted by dabelzee
    I'm actually working with visual basic 6.0 and ADO 2.5. When I try to resync a recordset generated from a complex sql query, I get this error:

    Run-time error '-2147217885 (80040e23)'
    Key value for this row was changed or deleted at the data store. The local row is now deleted.

    This issue appends only when I try to resync a row which has a joined on a null row from the other table.

    For example: I got two table ( vfac and zcardex). I use this custom query: select vfac.kvfac, zcardex.kzcardex from vfac left outer join zcardex on zcardex.kzcardex=vfac.kzcardex. When I use the resync function and the zcardex.kzcardex value is NULL. I get the error. Otherwise, everything works fine.

    I then found that it was possible to use two properties of the recordset to resolve this problem. Unfortunatly it doesn't seem to work. I get a new error.

    Here is my source code:
    '*************************************

    Dim m_Rs As ADODB.Recordset
    Set m_Rs = New ADODB.Recordset

    m_strConn = "driver={SQL Server};server=" & m_Conn.Name & ";uid=" & m_Conn.UserName & ";pwd=" & m_Conn.Password & ";database=" & m_Conn.Database & IIf(InStr(m_Conn.Name, ".") <> 0, ";Network=DBMSSOCN", "")

    Query = "select vfac.kvfac, vfac.dttrans, vfac.kzcardex, vfac.notrans, zcardex.nocardex, zcardex.nomprenom from vfac left outer join zcardex on vfac.kzcardex=zcardex.kzcardex order by vfac.kvfac"

    m_Rs.Open Query, m_strConn, adOpenKeyset, adLockOptimistic

    m_Rs.Properties("Resync Command") = "select vfac.kvfac, vfac.dttrans, vfac.kzcardex, vfac.notrans, zcardex.nocardex, zcardex.nomprenom from vfac left outer join zcardex on vfac.kzcardex=zcardex.kzcardex where vfac.kvfac = ?"

    m_Rs.Properties("Unique Table") = "vfac"

    m_Rs.Resync adAffectCurrent

    m_Rs.Close
    set m_Rs = nothing

    '*************************************



    Thank you,
    Mathieu Tremblay

  3. #3
    Join Date
    Nov 2002
    Posts
    3

    Re: Using Resync with ADO and complex SQL Query statement using left outer joins

    In fact, resync can be use to refresh only the data for a row wich have changed since you open your recordset. I don't think requery can refresh the data of 1 unique row, it should refresh the entire recordset which may take much time than a row.

    For the null values, I can't use this way because I would loose all my vfac rows whith no zcardex.kzcardex. I would be better tu use a join instead of a left outer join if I'd like to use your solution.

    Maybe I just went wrong and I don't understood the requery function. If there is a way to refresh the changed data for a unique row other than resync please let me know.

    Mathieu Tremblay

    Originally posted by pudgmo
    Not sure if this helps but two things came to mind, use requery instead of resync (I've never used resynch) and account for the null in you SQL statement

    select vfac.kvfac, zcardex.kzcardex from vfac left outer join zcardex on zcardex.kzcardex=vfac.kzcardex where zcardex.kzcardex <> null

  4. #4
    Join Date
    Nov 2002
    Posts
    5
    Requery does the whole rs over, sorry but I don't know how to get around this other than trapping and handeling the error.
    rob

  5. #5
    Join Date
    Nov 2002
    Posts
    3
    Unfortunatly I do believe your right.
    I'll try finding a solution, if I success, I'll post it here.

    Thank you for your help.

    Mathieu Tremblay
    Originally posted by pudgmo
    Requery does the whole rs over, sorry but I don't know how to get around this other than trapping and handeling the error.
    rob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •