# Visual Basic > Visual Basic .NET > VS 2013 [RESOLVED] Data Saving Issue in SQL Table

## accmaster

*Hello everyone,*

I am facing an issue with my code and the issue is when I click on save button, sometimes the data doesn't save in 2 tables. It happens in "Add Mode" only. If I check in a table that data is not saved, then I click on edit and save it again then data is saved perfectly in both tables. I have 2 modes on my form. "Add Mode" & "Edit Mode", saving data in these 2 tables are out of these modes. Means whether it is an add or edit mode, first data is deleted from both tables and saved again.

1. TblTransMaser and 2. TblTransDetail.

This is an accounting and inventory software and I created 2 different tables for the same. The amazing part is that out of 300 invoices only 2 to 3 invoices don't save in these 2 tables.  I checked it several times in debug mode and couldn't find anything. I am posting my code and also my connection string. Please let me know if there is any issue in my code or something else. How to resolve this issue. I am using VB.net 2013 4.0 framework and SQL server 2014.

Thank you.
*Ladak*




```

Public Conn As New System.Data.SqlClient.SqlConnection
Public SqlConStr As String

SqlConStr = "Server= 'MyComputer'; Database=DataFileWZER; User Id=sa; Password=123456789#;"
Conn.ConnectionString = SqlConStr
Conn.Open()
```





```
        Call StartConnection()


        Dim Msg1, Msg2, Msg3 As Object
        Dim Amtx, Amtx2 As Double
        Dim V1 As Integer

        If Len(Trim(Modex)) = 0 Then
            MsgBox("Press Add or Edit Button Before Saving", vbInformation + vbOKOnly, "Save Info.")
            Exit Sub
        End If



        Dim Qry As String
        Dim Table = New DataTable
        Dim Adapter = New SqlDataAdapter

        Dim CostOfGoodsSold As Double = 0


        GlbErrorLine1 = Nothing
        GlbErrorLine2 = Nothing
        GlbErrorLine3 = Nothing
        GlbErrorLine4 = Nothing
        GlbErrorLine5 = Nothing
        GlbErrorLine6 = Nothing
        GlbErrorLine7 = Nothing


        For Each TextBox As TextBox In PanelHeader.Controls.OfType(Of TextBox)()
            TextBox.Text = TextBox.Text.Trim.Replace(",", "")
        Next

        For Each TextBox As TextBox In PanelFooter.Controls.OfType(Of TextBox)()
            TextBox.Text = TextBox.Text.Trim.Replace(",", "")
        Next




        Msg1 = ""
        AllowToSave = "YES"



        If Len(Trim(txtActCode.Text)) = 0 Or Len(Trim(txtActCodeContra.Text)) = 0 Then
            GlbErrorLine1 = "Cannot Save Without Account Code"
            AllowToSave = "NO"
        End If

        If Val(CboWarehouse.Tag) <= 0 Then
            GlbErrorLine2 = "Invalid Warehouse selected."
            AllowToSave = "NO"
        End If

        If CheckAccountingYear(txtDate.Value) = False Then
            GlbErrorLine3 = "Out of Accouting year."
            AllowToSave = "NO"
        End If


        If Val(txtTotalQuantity.Text) = 0 Then
            GlbErrorLine4 = "Invalid Quantity."
            AllowToSave = "NO"
        End If


      

        '
        If AllowToSave = "NO" Then
            FrmErrorOnSavingRecord.StartPosition = FormStartPosition.CenterParent
            FrmErrorOnSavingRecord.ShowDialog(Me)

            Exit Sub
        End If



        Dim PaymentTerms As String = ""
        If OptCash.Checked = True Then PaymentTerms = "Cash"
        If OptCredit.Checked = True Then PaymentTerms = "Credit"






        Amtx = 0
       


        Call CheckAllowToSave()
        If AllowToSave = "NO" Then Exit Sub


        '
        '
        Dim MsgItemIDNo As Object
        Dim MsgQty As Object
        Dim MsgPrice As Object
        Dim MsgDiscount As Object
        Dim MsgSTPercent As Object

        Dim Amt1 As Double
        Dim Amt2 As Double
        Dim Amt3 As Double
        Dim Amt4 As Double


     


        Dim SqlStr As String
        Dim cmd = New SqlCommand

        If Modex = "Edit Mode" Then


            ' Delete Records before saving
            SqlStr = "Delete From TblInvoiceDetail Where  Vnoc = '" & Vnoc & "' and Vnon = " & Val(txtVnon.Text) & " And OurCompanyCode = " & GlbCompanyCode
            cmd = New SqlCommand(SqlStr, Conn)
            cmd.ExecuteNonQuery()
            '
            SqlStr = "Delete From TblTransMaster Where  Vnoc = '" & Vnoc & "' and Vnon = " & Val(txtVnon.Text) & " And OurCompanyCode = " & GlbCompanyCode
            cmd = New SqlCommand(SqlStr, Conn)
            cmd.ExecuteNonQuery()
            '
            '
            '
            SqlStr = "Delete From TblTransDetail where Vnoc = '" & Vnoc & "' and Vnon = " & Val(txtVnon.Text) & " And OurCompanyCode = " & GlbCompanyCode
            cmd = New SqlCommand(SqlStr, Conn)
            cmd.ExecuteNonQuery()




            SqlStr = "Update TblInvoiceSummary " & _
            "SET [Dated] = '" & Format(CDate(txtDate.Value), "yyyy-MM-dd") & "'," & _
            "[Remarks] = '" & Trim(txtRemarks.Text) & "'," & _
            "[ActCode] = '" & txtActCode.Text & "'," & _
            "[PaymentTerms] = '" & PaymentTerms & "'," & _
            "[ExternalReference] = '" & txtExternalReference.Text.Trim & "'," & _
            "[ActCodeContra] = '" & txtActCodeContra.Text & "'," & _
            "[WarehouseCode] = " & Val(CboWarehouse.Tag) & "," & _
            "[GrossAmount] = " & Val(txtGrossTotal.Text.Replace(",", "")) & "," & _
            "[AmountAfterDiscount] = " & Val(txtAmountAfterDiscount.Text.Replace(",", "")) & "," & _
            "[TotalSalesTaxAmount] = " & Val(txtTotalSalesTaxAmount.Text.Replace(",", "")) & "," & _
            "[TotalExtraTaxAmount] = " & Val(txtTotalExtraTaxAmount.Text.Replace(",", "")) & "," & _
            "[TotalTaxAmount] = " & Val(txtNetAmount.Text.Replace(",", "")) & "," & _
            "[NetAmount] = " & Val(txtNetAmount.Text.Replace(",", "")) & "," & _
            "[CashPaid] = 0" & "," & _
            "[CreditDays] = " & Val(txtCreditDays.Text) & "," & _
            "[NetCost] = " & Val(txtTotalWeightedAverage.Text.Replace(",", "")) & "," & _
            "[PaidLater] = 0 " & "," & _
            "[ContactPerson] = '" & txtPONumber.Text & "', " & _
            "[ReferenceCode] = " & Val(txtReferenceCode.Text) & "," & _
            "[AddCharges] = " & Val(txtAddCharges.Text.Replace(",", "")) & "," & _
            "[Advance] =  0 " & _
            "WHERE Vnoc = '" & Vnoc & "' and Vnon = " & Val(txtVnon.Text) & " And OurCompanyCode = " & GlbCompanyCode

            cmd = New SqlCommand(SqlStr, Conn)
            cmd.ExecuteNonQuery()
            '


        End If


        If Modex = "Add Mode" Then
            Call GenerateNumber()
        End If







        If Modex = "Add Mode" Then
            '' Now Start Saving 
            '' TblInvoiceSummary
            SqlStr = "Insert Into TblInvoiceSummary (Dated, Vnoc, Vnon, UserID, Remarks, ActCode, ActCodeContra, CopiesPrinted, WarehouseCode, GrossAmount, TotalDiscount, AmountAfterDiscount, NetAmount, CashPaid, Balance, NetCost, Advance, PaidLater, ExternalReference, AddCharges, OurCompanyCode, TotalSalesTaxAmount, TotalExtraTaxAmount, TotalTaxAmount, PaymentTerms, ContactPerson, ReferenceCode, CreditDays) " & _
                                             "Values(@Dated, @Vnoc, @Vnon, @UserID, @Remarks, @ActCode, @ActCodeContra, @CopiesPrinted, @WarehouseCode, @GrossAmount, @TotalDiscount, @AmountAfterDiscount, @NetAmount, @CashPaid, @Balance, @NetCost, @Advance, @PaidLater, @ExternalReference, @AddCharges, @OurCompanyCode, @TotalSalesTaxAmount, @TotalExtraTaxAmount, @TotalTaxAmount, @PaymentTerms, @ContactPerson, @ReferenceCode, @CreditDays)"
            cmd = New SqlCommand(SqlStr, Conn)

            With cmd.Parameters
                .AddWithValue("@Dated", Format(CDate(txtDate.Value), "yyyy-MM-dd"))
                .AddWithValue("@Vnoc", Vnoc)
                .AddWithValue("@Vnon", Val(txtVnon.Text))
                .AddWithValue("@UserID", GlbUserID)
                .AddWithValue("@Remarks", Trim(txtRemarks.Text))
                .AddWithValue("@ActCode", txtActCode.Text)
                .AddWithValue("@ActCodeContra", txtActCodeContra.Text)
                .AddWithValue("@CopiesPrinted", 1)
                .AddWithValue("@WarehouseCode", Val(CboWarehouse.Tag))
                .AddWithValue("@ExternalReference", txtExternalReference.Text.Trim)
                .AddWithValue("@PaymentTerms", PaymentTerms)
                .AddWithValue("@CreditDays", Val(txtCreditDays.Text))

                Msg1 = txtGrossTotal.Text
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@GrossAmount", Amtx)

                Msg1 = txtDiscount.Text
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@TotalDiscount", Amtx)

                Msg1 = txtAmountAfterDiscount.Text
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@AmountAfterDiscount", Amtx)

                Msg1 = txtNetAmount.Text
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@NetAmount", Amtx)

                .AddWithValue("@CashPaid", 0)


                Msg1 = Val(txtAddCharges.Text.Replace(",", ""))
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@AddCharges", Amtx)


                .AddWithValue("@Balance", 0)

                Msg1 = txtTotalWeightedAverage.Text
                Amtx = VB.FormatNumber(Msg1, 4)
                .AddWithValue("@NetCost", Amtx)

                .AddWithValue("@Advance", 0)
                .AddWithValue("@PaidLater", 0)

                .AddWithValue("@OurCompanyCode", GlbCompanyCode)

                Msg1 = txtTotalSalesTaxAmount.Text
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@TotalSalesTaxAmount", Amtx)


                Msg1 = txtTotalExtraTaxAmount.Text
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@TotalExtraTaxAmount", Amtx)


                Msg1 = txtTotalTaxAmount.Text
                Amtx = VB.FormatNumber(Msg1, "10")
                .AddWithValue("@TotalTaxAmount", Amtx)



                .AddWithValue("@ContactPerson", txtPONumber.Text)
                .AddWithValue("@ReferenceCode", Val(txtReferenceCode.Text))

            End With

            cmd.ExecuteNonQuery()
        End If
        '
        '
        '
        '
        '
        '
        'TblInvoiceDetail
        Pb1.Minimum = 0
        Pb1.Maximum = Fp1.Sheets(0).RowCount
        Pb1.Value = 0

        CostOfGoodsSold = 0

        '20.12.2017
        Dim ChargesPerPiece As Double = 0
        If Val(txtTotalQuantity.Text) <> 0 Then ChargesPerPiece = Val(txtAddCharges.Text.Replace(",", "")) / Val(txtTotalQuantity.Text)


        For A1 = 0 To Fp1.Sheets(0).RowCount - 1

            Msg3 = (Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("IsDeleted").Index)).Value
            V1 = Val(Msg3)

            Msg2 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("ItemIDNumber").Index).Value

            If Len(Trim(Msg2)) <> 0 And V1 = 0 Then

                SqlStr = "Insert Into TblInvoiceDetail (Vnoc, Vnon, ItemCode, Quantity, TransQuantity, Price, Amount, Discount, NetAmount, WeightedAverage, IsDeleted, WarehouseCode, AddCharges, Size, OurCompanyCode, SalesTaxPercent, SalesTaxAmount, ExtraTaxPercent, ExtraTaxAmount) " & _
                                                "Values(@Vnoc, @Vnon, @ItemCode, @Quantity, @TransQuantity, @Price, @Amount, @Discount, @NetAmount, @WeightedAverage, @IsDeleted, @WarehouseCode, @AddCharges, @Size, @OurCompanyCode, @SalesTaxPercent, @SalesTaxAmount, @ExtraTaxPercent, @ExtraTaxAmount)"
                cmd = New SqlCommand(SqlStr, Conn)

                With cmd.Parameters
                    .AddWithValue("@Vnoc", Vnoc)
                    .AddWithValue("@Vnon", Val(txtVnon.Text))
                    .AddWithValue("@WarehouseCode", Val(CboWarehouse.Tag))

                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("ItemIDNumber").Index).Value
                    .AddWithValue("@ItemCode", Msg1)


                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("Size").Index).Value
                    If Msg1 = Nothing Then Msg1 = "-"
                    .AddWithValue("@Size", Msg1)


                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("Qty").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), "3")
                    .AddWithValue("@Quantity", Amtx)
                    .AddWithValue("@TransQuantity", Amtx * -1)

                    '20.12.2017
                    .AddWithValue("@AddCharges", Amtx * ChargesPerPiece)



                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("Price").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@Price", Amtx)


                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("Amount").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@Amount", Amtx)


                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("Discount").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@Discount", Amtx)


                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("NetAmount").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@NetAmount", Amtx)

                    .AddWithValue("@WeightedAverage", 0)

                    .AddWithValue("@IsDeleted", 0)
                    .AddWithValue("@OurCompanyCode", GlbCompanyCode)

                    '
                    '
                    '
                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("SalesTaxPercent").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@SalesTaxPercent", Amtx)

                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("SalesTaxAmount").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@SalesTaxAmount", Amtx)

                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("ExtraTaxPercent").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@ExtraTaxPercent", Amtx)

                    Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("ExtraTaxAmount").Index).Value
                    Amtx = VB.FormatNumber(Val(Msg1), 10)
                    .AddWithValue("@ExtraTaxAmount", Amtx)
                    '
                    '
                    '

                End With

                cmd.ExecuteNonQuery()

            End If


            '
            '
            Msg1 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("ItemIDNumber").Index).Value
            Msg2 = Fp1.Sheets(0).Cells(A1, Fp1.Sheets(0).Columns("Qty").Index).Value
            CostOfGoodsSold = Math.Round((CostOfGoodsSold + (CalculateWeightedAverageRateBackDatedSingleItem(txtDate.Text, Msg1) * Val(Msg2))), 2)
            '


            Pb1.Value = Pb1.Value + 1
        Next A1

        '
        '

        '' 
        '


        SqlStr = "Insert Into TblTransMaster (Dated, VoucherNo, Vnoc, Vnon, TotalAmount, UserID, SaveDate, SaveTime, SystemName, SystemIpAddress, OurCompanyCode, CreditDays, DueDate) " & _
                 "Values(@Dated, @VoucherNo, @Vnoc, @Vnon, @TotalAmount, @UserID, @SaveDate, @SaveTime, @SystemName, @SystemIpAddress, @OurCompanyCode, @CreditDays, @DueDate)"
        cmd = New SqlCommand(SqlStr, Conn)

        With cmd.Parameters
            .AddWithValue("@Dated", Format(CDate(txtDate.Value), "yyyy-MM-dd"))
            .AddWithValue("@VoucherNo", "")
            .AddWithValue("@Vnoc", Vnoc)
            .AddWithValue("@Vnon", Val(txtVnon.Text))

            .AddWithValue("@TotalAmount", Val(txtNetAmount.Text.Replace(",", "")))
            .AddWithValue("@UserID", GlbUserID)

            .AddWithValue("@SaveDate", Format(CDate(Date.Now), "yyyy-MM-dd"))
            .AddWithValue("@SaveTime", Now.ToShortTimeString())
            .AddWithValue("@SystemName", My.Computer.Name)
            .AddWithValue("@SystemIpAddress", System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList(0).ToString())
            .AddWithValue("@OurCompanyCode", GlbCompanyCode)

            .AddWithValue("@CreditDays", Val(txtCreditDays.Text))
            .AddWithValue("@DueDate", Format(CDate(txtDate.Value).AddDays(Val(txtCreditDays.Text)), "yyyy-MM-dd"))

        End With

        cmd.ExecuteNonQuery()
        '
        '
        '
        '
        'Party Credit
        '
        '
        SqlStr = "Insert Into TblTransDetail (Dated, Vnoc, Vnon, ActCode, Description, Amount, OurCompanyCode, Quantity, ReferenceCode)  Values(@Dated, @Vnoc, @Vnon, @ActCode, @Description, @Amount, @OurCompanyCode, @Quantity, @ReferenceCode)"
        cmd = New SqlCommand(SqlStr, Conn)

        With cmd.Parameters

            .AddWithValue("@Dated", Format(CDate(txtDate.Value), "yyyy-MM-dd"))
            .AddWithValue("@Vnoc", Vnoc)
            .AddWithValue("@Vnon", Val(txtVnon.Text))
            .AddWithValue("@ActCode", txtActCode.Text)
            .AddWithValue("@Description", txtRemarks.Text)

            Msg1 = txtNetAmount.Text.Replace(",", "")
            Amtx = VB.FormatNumber(Msg1, "10")
            .AddWithValue("@Amount", Amtx)

            .AddWithValue("@OurCompanyCode", GlbCompanyCode)
            .AddWithValue("@Quantity", Val(txtTotalQuantity.Text.Replace(",", "")))
            .AddWithValue("@ReferenceCode", Val(txtReferenceCode.Text))

        End With
        cmd.ExecuteNonQuery()
        '
        '
        '
        'Purchase Debit
        SqlStr = "Insert Into TblTransDetail (Dated, Vnoc, Vnon, ActCode, Description, Amount, OurCompanyCode, Quantity)  Values(@Dated, @Vnoc, @Vnon, @ActCode, @Description, @Amount, @OurCompanyCode, @Quantity)"
        cmd = New SqlCommand(SqlStr, Conn)

        With cmd.Parameters

            .AddWithValue("@Dated", Format(CDate(txtDate.Value), "yyyy-MM-dd"))
            .AddWithValue("@Vnoc", Vnoc)
            .AddWithValue("@Vnon", Val(txtVnon.Text))
            .AddWithValue("@ActCode", txtActCodeContra.Text)
            .AddWithValue("@Description", txtRemarks.Text)
            .AddWithValue("@UserID", GlbUserID)

            Msg1 = txtGrossTotal.Text.Replace(",", "")
            Amtx = VB.FormatNumber(Msg1, "10")
            .AddWithValue("@Amount", Amtx * -1)
            .AddWithValue("@OurCompanyCode", GlbCompanyCode)
            .AddWithValue("@Quantity", Val(txtTotalQuantity.Text.Replace(",", "")))

        End With
        cmd.ExecuteNonQuery()
        '
        '



        '
        '

        Call FormatColumns()
        Call GridTotal()
        '
        '
        '
        '
        '
        MsgBox("Record Saved" & vbCrLf & "Inv.No. " & Trim(txtVnon.Text), vbInformation + vbOKOnly, "Save Info.")

        Modex = ""
        FormCaption = VnocDetail & " (Browse Mode)"
        Call DisableAllTextBoxes()
        Call SetFormCaption()
        Call PreviousData()



        CmdAdd.Focus()
```

----------


## Peter Swinkels

At a glance:

1. Object data type. Try to avoid late binding.
2. Try to avoid using the Microsoft.VisualBasic namespace as much as possible.
3. Don't use the addition operator ("+") to combine flags (e.g. vbInformation + vbOkOnly). Use the logical bitwise "Or" operator instead.
4. Lots of "Exit Subs". Try add more structure to the flow of your code.
5. Don't cram everything into a single procedure.
6. Always initialize variables when you declare them.
7. Turn "Option Explicit" and "Option Strict" on if you haven't done so already.
8. Seven GlbErrorLine variables? Use lists or arrays instead.
9. "AllowToSave" appears to be intended as a Boolean. Why is it a string?
10. Use the "AndAlso" or "OrElse" operators in conditional expressions. (e.g. If (expression) Then)
11. Look up what string interpolation is.
12. "Modex" looks like it should be an enumeration of options instead of a string.
13. "txtDate" is a misleading name for something that can't be a textbox (those don't have a value property).

Also don't post enormous chunks of code that could not possibly be debugged by someone else because they depend on a ton of other stuff and expect someone to magically fix it for you. This looks like a case where better coding could prevent issues.

----------


## accmaster

> At a glance:
> 
> 1. Object data type. Try to avoid late binding.
> 2. Try to avoid using the Microsoft.VisualBasic namespace as much as possible.
> 3. Don't use the addition operator ("+") to combine flags (e.g. vbInformation + vbOkOnly). Use the logical bitwise "Or" operator instead.
> 4. Lots of "Exit Subs". Try add more structure to the flow of your code.
> 5. Don't cram everything into a single procedure.
> 6. Always initialize variables when you declare them.
> 7. Turn "Option Explicit" and "Option Strict" on if you haven't done so already.
> ...



Beautiful explanation! Thank you buddy.

I created 2 separate procedure for saving both modules. Hope this will work for me.

Ladak

----------

