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)
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")
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)
Please Suggest me what to do now
Thanks in Advance
Amit Saraf