locked
how to insert and update master/details records using vb.net and sql server RRS feed

  • Question

  • hi i have following code to insert a master/details records in sql server database

    actually i have not knowledge to update child records using relation so my inserting method is as under

    when i need to update a record i just delete the desired record from master table and run insert command again to save data master/details data into database

    but i am not satisfied with it because using this trick if there is an error generate during inserting details record then master table current record only saved and details record against this master record will be lost due to the use of Update and Delete rules CASCADE in relational tables.

    My coding under save button listed below.

    please review it

     If isUpdate = True Then
                        com.CommandText = "delete from CashSales WHERE InvoiceNo = '" & txtInvNo.Text & "'"
                        com.ExecuteNonQuery()
    
                    End If
                    com.CommandText = "INSERT INTO CashSales ( InvNo, RefNo, AccCode, AccName, dDate, Total, [User], Type, GroupType1, DiscountPcnt, DiscountRs, Received, [Time], Changed, DetailDiscRs, SalesmanID, SalesmanName, Margine, StoreID,Address,ProgramID,TotalCost,InvoiceType ) values ('" & txtInvNo.Text & "','" & txtRef.Text & "','" & txtCashCode.EditValue & "','" & txtCashCode.Text & "','" & txtDate.EditValue & "','" & txtGrandTotal.EditValue & "','" & MainForm.txtUserName.Caption & "','CS','CA','" & txtDiscPcnt.EditValue & "','" & txtDiscRs.EditValue & "','" & txtamountenter.EditValue & "',GETDATE(),'" & txtChanged.EditValue & "'," & totalDisc & ",'" & SalesmanID & "','" & txtSalesman.Text & "','" & totalMar & "','" & txtStore.EditValue & "','" & Address & "','" & MaxProgramID & "','" & totalCost & "','" & IIf(CheckEdit2.Checked = True, 1, 0) & "')"
                    com.ExecuteNonQuery()
                    Dim Narration As String = String.Empty
    
                    For i = 0 To LedgerDBDataSet.InvoiceDetail.Rows.Count - 1
                        Dim PID As Integer = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("ProductID")
                        Dim PName As String = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("ProductName")
                        Dim Packing As String = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("UOM").ToString
                        Dim Qty As Double = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("Qty")
                        Dim TPrice As Double = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("TPrice")
                        Dim Price As Double = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("Price")
                        Dim Discount As Double = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("Discount")
                        Dim InvTotal As Double = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("Invtotal")
                        Dim Barcode As String = LedgerDBDataSet.InvoiceDetail.Rows(i).Item("Barcode")
                        com.CommandText = "Insert into InvoiceDetail (InvNo,ProductID,ProductName,UOM,Qty,TPrice,Price,Discount,Total,BNo,Barcode) values ('" & txtInvNO.Text & "','" & PID & "','" & PName & "','" & Packing & "','" & Qty & "','" & TPrice & "','" & Price & "','" & Discount & "','" & InvTotal & "','0','" & Barcode & "')"
                        com.ExecuteNonQuery()
                        Narration = Narration & Barcode & "-" & PName & " Qty:" & Qty & " @" & Price & "," & "Amt:" & InvTotal & ", "
                        com.CommandText = "Update Product SET Product.LastSalesDate= '" & txtDate.EditValue & "' WHERE Product.ProductID = " & PID & ""
                        com.ExecuteNonQuery()
                    Next
    so i request to give me sample code demonstration inserting and updating master/details record in a proper way.


    Sunday, June 7, 2015 8:39 AM

Answers

  • The best way would be to pass the detail rows in a table-valued parameter and then use the MERGE statement to update the InvoiceDetails table.

    See this article on my web site for how to use table-valued parameters from .NET and there is kind of an example with MERGE in it_
    http://www.sommarskog.se/arrays-in-sql-2008.html

    As Uri says, stop building SQL strings by concatenating values into a single SQL string. This is entirely imperssible if any professional work.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Eric__Zhang Tuesday, June 9, 2015 8:06 AM
    • Marked as answer by Eric__Zhang Tuesday, June 16, 2015 1:12 AM
    Sunday, June 7, 2015 11:57 AM

All replies

  • PLEASE ,PLEASE STOP USING code like this 

     com.CommandText = "delete from CashSales WHERE InvoiceNo = '" & txtInvNo.Text & "'"
                        com.ExecuteNonQuery()

    Have you ever heard about SQL Injection?

    http://en.wikipedia.org/wiki/SQL_injection

    http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, June 7, 2015 9:16 AM
    Answerer
  • The best way would be to pass the detail rows in a table-valued parameter and then use the MERGE statement to update the InvoiceDetails table.

    See this article on my web site for how to use table-valued parameters from .NET and there is kind of an example with MERGE in it_
    http://www.sommarskog.se/arrays-in-sql-2008.html

    As Uri says, stop building SQL strings by concatenating values into a single SQL string. This is entirely imperssible if any professional work.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Eric__Zhang Tuesday, June 9, 2015 8:06 AM
    • Marked as answer by Eric__Zhang Tuesday, June 16, 2015 1:12 AM
    Sunday, June 7, 2015 11:57 AM