 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / February 2008update after insert does not find records
SQL Server 2005, VB.NET 2005... In a cross database query, when I insert a set of records, then in a subsequent query attempt to update those records, the update fails to see the newly added records. I am using multiple tables in a set of joins in which the target table is always in a second database. I know that my joins are correct as running Select queries at breakpoints in SSMS shows correct results. It seems as if the ADO engine is not "seeing" the newly added records. Placing a break on the line and running a Select in SQL Server Management Studio shows that the data is there. Thanks for any insight! Joe > SQL Server 2005, VB.NET 2005... > [quoted text clipped - 9 lines] > Placing a break on the line and running a Select in SQL Server Management > Studio shows that the data is there. I'm a little fuzzy on where ADO factors into this, are you opening a recordset on the joined query, and then trying to assign fields and update the recordset? Or are you using ADO to execute an UPDATE query? In either case, this is the question: does the product of *all* joins to the target table definitively and explicitly resolve as 1:1, so that it is impossible (as in, enforced by DRI) for a single physical row in the target to be logically included more than once in the product? If the answer is no, then the query cannot be updated. You say you've set breakpoints and verified correctness using SELECT queries, but have you attempted to execute any update queries against the same joined target, from SSMS? I suspect that the target rowset is inherently non-updateable, perhaps you could restructure the update target using EXISTS clauses or IN clauses? Alternatively, you might be able to dump the PK of the target table from the product of the join to a temp table, and use that temp table in a definitive 1:1 join to the target table. To summarize, if my assumptions are correct, the task becomes either a.) filtering the target to the same set of rows using constructs other than joins, b.) resolving a set of values that uniquely identifies the filtered rows in the target (along with derivations of new values for target table fields, if applicable) using multiple joins as you're currently doing, then dumping whatever you need out of it to a temp table, and using that temp table in a 1:1 join with the target, or, c.) adjusting DRI to guarantee/enforce target row one-to-oneness across all joins to the target table. -Mark > Thanks for any insight! > Joe Mark, Thanks for the considered response. After a long restful sleep, and a return to the problem I realized I must have been querying the wrong database when I was testing the results of the insert (while code execution was suspended at a breakpoint). In fact my issue lay in another problem in logic, not in the JOIN, not in the ADO object model or operation of the SQL Server database. Thanks, Joe >> SQL Server 2005, VB.NET 2005... >> [quoted text clipped - 44 lines] >> Thanks for any insight! >> Joe "Comcast Newsgroups" <joe@bravenewsoftware.com>'s wild thoughts were released on Mon, 11 Feb 2008 10:13:09 -0800 bearing the following fruit: >SQL Server 2005, VB.NET 2005... This is a VB6 group. >In a cross database query, when I insert a set of records, then in a >subsequent query attempt to update those records, the update fails to see >the newly added records. Did the insert occur as part of a transaction which has been committed? If not do so. J >I am using multiple tables in a set of joins in which the target table is >always in a second database. I know that my joins are correct as running [quoted text clipped - 6 lines] >Thanks for any insight! >Joe -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde Thanks for the headsup re: vb6 not .net, I based my newsgroup choice on "vb" and "ado". The query was not part of a transaction. But my problem turned out to be a different coding issue. Thank you, Joe > "Comcast Newsgroups" <joe@bravenewsoftware.com>'s wild > thoughts were released on Mon, 11 Feb 2008 10:13:09 -0800 [quoted text clipped - 27 lines] > > https://mvp.support.microsoft.com/profile/Jan.Hyde "Comcast Newsgroups" <joe@bravenewsoftware.com>'s wild thoughts were released on Tue, 12 Feb 2008 17:50:49 -0800 bearing the following fruit: >Thanks for the headsup re: vb6 not .net, I based my newsgroup choice on "vb" >and "ado". Yes, Microsoft and are entirely to blame for this confusion. J >The query was not part of a transaction. But my problem turned out to be a >different coding issue. [quoted text clipped - 33 lines] >> >> https://mvp.support.microsoft.com/profile/Jan.Hyde -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.