Updating Multiple Tables

Question

4.50/5 (2 votes)

See more:

, +

I'm writing my Invoicing Application from Scratch and now want to make it more stable.
I have a Form for Sale Invoicing, Here are the Tables related to i

1. Table Name SP contains master register and invoice information having fields SPId, No, Date, Cust, TotalAmt
2. Table Name Items contains details of Product Billed having fields PId, SPId, Product, Qty, Rate, Amt
3. Table Name Tax contains details of Taxation having fields TId, SPId, TaxRate, TaxAmt, Tax
4. Table Name Other Contains details of Other Charges having fields OId, SPId, Charge, Amt

Every Table is linked to SP with SPId and data entered can be one single row or many rows or not a single row in any table depending on the requirement of the user.

Currently in my old app i was updating SP using SQL Command and retrieving SPId from DB and then updating each table using its table adapter after applying SPId to it. This would need to open the connection more than 5 times and thus was more prone to err of anything goes wrong which is beyond control.

I just studied / learned about dataset and relationships and was thinking wheather it is possible to update all the four tables in single stroke without having to update each table seperately and how

here is my current code (VB 2010 & SQL Server Express)

VB

Dim DS as DataSet
Dim AdapSp as New SqlDataAdapter("SELECT * FROM SP WHERE SPId=0",Conn)
Dim AdapItem as New SqlDataAdapter("SELECT * FROM Items WHERE SPId=0",Conn)
Dim AdapTax as New SqlDataAdapter("SELECT * FROM Tax WHERE SPId=0",Conn)
Dim AdapOther as New SQLDataAdapter("SELECT * FROM Other WHERE SPId=0",Conn)

AdapSp.Fill(DS,"SP")
AdapItem.Fill(Ds,"Item")
AdapTax.Fill(DS,"Tax")
AdapOther.Fill(DS,"Other")


'Now I Have Added Rows to respective Data Tables in DataSet DS
'Now When User Clicks Save Button a Single Row is Added to Table SP

dim R as DataRow=DS.Tables("SP").NewRow
R("SPId")=0
R("No")= xxxx
R("Date")= xxxx
R("Cust")= xxxx
R("TotalAmt")= xxxx
ds.Tables("SP").Rows.Add(R)

'Add & Update Commands can be created using SqlCommandBuilder and data can be updated to DB File but that would be the same what i was doing previously
'Now I want to update the complete information to my Database

Please Suggest me what to do now

Thanks in Advance
Amit Saraf

Posted 2-Nov-12 2:21am

A N Saraf

Updated 5-Nov-12 1:17am

v2

Add a Solution

Comments

Bala Selvanayagam 4-Nov-12 5:49am

Hope its a SQL serer database and what version of SQL servr ?

Have you thought about updatable database views ?

A N Saraf 5-Nov-12 2:02am

it is SQL Server 2012 Localdb

I'm using SQL Server for the first time so I dont know about updatable views? please if possible give me any link to know more about it

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2012-11-05T01:22:00

Solution 2

A little Google goes a long way. Read me[^]

Posted 5-Nov-12 1:22am

Dave Kreskowiak

Comments

A N Saraf 7-Nov-12 0:21am

Your Article was great. After studying that I understood the tachnique how to update tables using a view
Can you paste the link to the second part
Thanks again

njammy · Answer 2 · 2012-11-05T11:58:00

There really isn't a risk-free way of doing what your thinking/wanting, with just one command.

To help you learn a little more about Datasets and how they can be used more efficiently, I present this possible solution.

I would create a DataSet object in the project e.g. 'CustomDataSet'. This will define the data set class as type 'CustomDataSet'.

Then add the TableAdapters to the designer using your existing SELECT commands. You can add all the tables as in your question to the designer.

Allow the designer to auto generate the update, delete, insert commands (NOTE that the sql must be for one table i.e no joins, otherwise auto generation of insert,delete,update will not work).

Then in code:

VB

Dim ds as New CustomDataSet
Dim spTa as New CustomDataSetTableAdapters.SpTableAdapter 'or whatever you call the table
Dim itemTa as New CustomDataSetTableAdapters.ItemTableAdapter

spTa.Fill(ds.sp)
itemTa.Fill(ds.Item)
'repeat for other tables

'Note the ds.sp tables rows will have RowState of 'Unchanged'. You do not need to use this but it helps to know what it is. The .Fill method sets rowstate = 'unchanged'. New rows added to the DS will be 'Added'. Updated rows will be 'Modified'. Have a play around or read up 'DataRow.RowState'

' Add row
Dim spRow as CustomDataSet.spRow = ds.sp.NewSpRow()
spRow.spId = 0
'Set other spRow fields this way. (Fields will appear in intellisense because it is defined in typed dataset CustomDataSet.

ds.sp.AddSpRow(spRow) ' <-- Here the spRow.RowState will be 'Added'

'
'Repeat the table row adds for the other tables
'

' Now call built in update method
spTa.Update(ds.Sp)
itemTa.Update(ds.Item)

Note that calling the .Update methods will reset the rowstates to Unchanged once committed. If you need to manipulate modified or added rows before doing database commit/update, then you can use the RowState property then finalise using DataSet.AcceptChanges().