Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB.NET
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB.NET General VB.NET discussions for issues that don't fall into other VB.NET forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 12th, 2003, 10:56 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update or add multiple table records

Can the above be done on the server at the same time? Effectively, I want to update a descripion in one table and then update the address, city, state and zip in another table. I am using vs.net with an access databas, oledbcommands and executereader. The tables have a one-to-many relationship.

Here is my code:

UPDATE stlocation
SET storeaddrl1 = ?, storecity = ?, storestate = ?, storezip = ?
WHERE (storeid = ?)
__________________________________________________ ______________

Notice above that I have no Description field. That's because it is in the sthdr record.

Can I do something like:

UPDATE sthdr, stlocation
SET storedesc = ? storeaddrl1 = ?, storecity = ?, storestate = ?, storezip = ?
WHERE (storeid = ?)

Or can I do something like below in the oledbcommand:

Update storehdr
Set storedesc = ?
where (storeid = ?)

UPDATE stlocation
SET storeaddrl1 = ?, storecity = ?, storestate = ?, storezip = ?
WHERE (storeid = ?)

Thanks for answers.
Reply With Quote
  #2 (permalink)  
Old June 12th, 2003, 12:27 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 33
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can use transactions. Assign a transaction to your command objects, begin the transaction, execute all of the commands, then commit the transaction. If you dont want to persist updates that have occurred since the transaction began simply rollback the transaction.

Quote:
quote:Originally posted by rosenzl
 Can the above be done on the server at the same time? Effectively, I want to update a descripion in one table and then update the address, city, state and zip in another table. I am using vs.net with an access databas, oledbcommands and executereader. The tables have a one-to-many relationship.

Here is my code:

UPDATE stlocation
SET storeaddrl1 = ?, storecity = ?, storestate = ?, storezip = ?
WHERE (storeid = ?)
__________________________________________________ ______________

Notice above that I have no Description field. That's because it is in the sthdr record.

Can I do something like:

UPDATE sthdr, stlocation
SET storedesc = ? storeaddrl1 = ?, storecity = ?, storestate = ?, storezip = ?
WHERE (storeid = ?)

Or can I do something like below in the oledbcommand:

Update storehdr
Set storedesc = ?
where (storeid = ?)

UPDATE stlocation
SET storeaddrl1 = ?, storecity = ?, storestate = ?, storezip = ?
WHERE (storeid = ?)

Thanks for answers.
Reply With Quote
  #3 (permalink)  
Old June 12th, 2003, 01:46 PM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

All samples of .net stuff use single table updates. In the real world, many tables are related for processing updates. This update I am referring to is the most basic that exists. Is what you said the only way to update 2 tables when the user clicks submit? Apparently a datagrid only allows for edit/delete and canel. A adapter only deals with one table at a time. I don't see anything that works with a dataset either. The pubs database has a titles and authors relationship. Are you saying that the only way to add a new title and author is to use transaction processing?
Reply With Quote
  #4 (permalink)  
Old June 12th, 2003, 01:59 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 33
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It may not be the only way, after all, you could write a command that contains an SQL Update query that updates more than one table (i think your initial post had such a statement so you are obviously aware of this).

It is the 'Correct' (Microsoft tm)s way of doing it though. Transactions have been around for a long time and will probably be used more with the introduction of .NET.

DataAdapters are capable of handling data from multiple tables in a database, but Microsofts preferred way of doing it (at least from what I have read) is to use a different DataAdapter for each table. The real bitch about transactions used with DataAdapters is that each command (that modifies data) in a dataAdapter needs a property setting to the transaction object you are using.

Doesnt sound like what you want to hear but I hope it helps.





Quote:
quote:Originally posted by rosenzl
 All samples of .net stuff use single table updates. In the real world, many tables are related for processing updates. This update I am referring to is the most basic that exists. Is what you said the only way to update 2 tables when the user clicks submit? Apparently a datagrid only allows for edit/delete and canel. A adapter only deals with one table at a time. I don't see anything that works with a dataset either. The pubs database has a titles and authors relationship. Are you saying that the only way to add a new title and author is to use transaction processing?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add/Update/Delete Database records MANUALLY jn148 BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 2 December 14th, 2008 10:18 AM
Update Multiple Records cancer10 Classic ASP Databases 0 October 25th, 2006 01:01 AM
update multiple records mateenmohd Classic ASP Basics 4 June 28th, 2004 03:38 AM
How Can I Update Multiple Records Lucy SQL Server ASP 3 March 18th, 2004 03:19 PM
How to Add/Delete/Update multiple records in DB? mmwaikar ADO.NET 1 September 20th, 2003 11:41 AM



All times are GMT -4. The time now is 04:46 AM.


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