Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 24th, 2004, 06:28 PM
Registered User
 
Join Date: Sep 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Huge problem updating FoxPro with ADO recordset

I am trying to update a Visual FoxPro table with an ADO recordset object. When I change the value of a field in one of the rows, however, the fields is changed in ALL of the rows after the update is complete. Here is sample code:

Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset

con.Open "Driver={Microsoft Visual FoxPro Driver}; SourceType=DBF; SourceDB=c:\db; Exclusive=No;"

rs.Open "select * from items where ponumber = 111", con, adOpenForwardOnly, adLockOptimistic

rs!quantity = 42
rs.Update
rs.Close
set rs = Nothing

If the select returned two rows, both rows will have a quantity of 42. When I do the same thing with MS SQL Server, it only updates the first row.

I'd really appreciate any help!

  - Eric

 
Old September 24th, 2004, 06:52 PM
Registered User
 
Join Date: Sep 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just noticed something peculiar. This only seems to happen if both records in the record set are completely identical. If there is any difference at all, only the first record is updated.

Again, any help would be great.

  - Eric

 
Old September 24th, 2004, 06:58 PM
Authorized User
 
Join Date: Jul 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to AFei
Default

try this way:
SQL="Update items Set quantity=42 Where ponumber=111"
con.Exuecute(SQL)

 
Old September 24th, 2004, 07:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

That is because you dont loop through the recordset and update each. Afei's solution should help you achieve that in one step.

Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old October 18th, 2004, 03:00 PM
Registered User
 
Join Date: Sep 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply. The problem is that with the statement "Update items Set quantity=42 Where ponumber=111" it will update everything with a ponumber of 111. I only want to update the first record with a ponumber of 111 even if multiple are qualified. The update that I'm performing is actually much more complex, I just simplified it to make the illustration easier.

Essentially I do a select on a table and multiple rows qualify because every field in both rows is identical. When I update only the first row in the recordset, it updates both rows. The odd part is that even if both rows qualify my selection if any field is different, I don't have the problem--its only when every field is exactly the same.

  - Eric

 
Old October 18th, 2004, 03:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Looks like you need to have a look at the table design. I don't understand why 2 rows should be identical in any case. If that was allowed, then obviously, it would update both the rows that are identical.

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
Updating FoxPro index from ASP namhatre Classic ASP Databases 0 January 5th, 2007 03:06 AM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM
Ado Recordset Filtering problem abumishal Access VBA 0 March 30th, 2004 01:11 PM
ADO connect string for FoxPro db file lcsgeek Classic ASP Databases 1 September 12th, 2003 04:53 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.