HOW COULD I UPDATE DATASET WITHOUT USING FOR LOOP IN ONE COMMAND

[DeepKiran]

HOW COULD I UPDATE DATASET WITHOUT USING FOR LOOP IN ONE COMMAND............/////////////////

[Reverend Jim]

It depends on what kind of update you are trying to do and that depends on things like

1. are you updating one table or multiple tables
2. are you updating one field or multiple fields

The more information you can provide the more likely you are to get a useful response.

[ryanjayson]

Can you post code?

[DeepKiran]

I am Updating Multiple fields in One data Set

[DeepKiran]

There is the code .............
 Private Sub BtnView_Click(sender As System.Object, e As System.EventArgs) Handles BtnView.Click
        cn.Open()
        qry = "delete from tbFinanceData "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()
        cn.Close()

        Dim objComnFunction As New CommonFunction
        objComnFunction.trfdCashData()
        objComnFunction.trfdBankData()
        objComnFunction.trfdJournalData()
        objComnFunction.trfdPurchaseData()
        objComnFunction.trfdPurchaseReturnData()
        objComnFunction.trfdSalesData()
        objComnFunction.trfdSalesReturnData()
        objComnFunction.trfdiformData()
        objComnFunction.trfdChallanData()
        objComnFunction.trfdJformData()

        '**** for Opening
        cn.Open()
        qstr = "delete * from tbFinanceTemp"
        cmod = New OleDb.OleDbCommand(qstr, cn)
        cmod.ExecuteNonQuery()

        'qstr = "Drop Table tempTrialBalance"
        'cmod = New OleDb.OleDbCommand(qstr, cn)
        'cmod.ExecuteNonQuery()

        qstr = "Drop Table TempTrial"
        cmod = New OleDb.OleDbCommand(qstr, cn)
        cmod.ExecuteNonQuery()


        qry = "INSERT INTO tbFinanceTemp ( ActCode, DrAmount,CrAmount ) SELECT tbAccountMaster.ActCode, tbAccountMaster.OpnBalance,0 FROM tbAccountMaster WHERE tbAccountMaster.OpnBalance > 0 And tbAccountMaster.OpnType = 'D' "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "INSERT INTO tbFinanceTemp ( ActCode, CrAmount, DrAmount ) SELECT tbAccountMaster.ActCode, tbAccountMaster.OpnBalance,0 FROM tbAccountMaster WHERE tbAccountMaster.OpnBalance > 0 And tbAccountMaster.OpnType = 'C' "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "INSERT INTO tbFinanceTemp SELECT tbFinanceData.* FROM tbFinanceData Where tbFinanceData.VoucherDate < # " & dtStartDate.Value & "#   "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "SELECT tbFinanceTemp.ActCode, Sum(tbFinanceTemp.DrAmount) AS SumOfDrAmount, Sum(tbFinanceTemp.CrAmount) AS SumOfCrAmount, tbFinanceTemp.BalanceAmount, tbFinanceTemp.BalanceType "
        qry += "Into TempTrial FROM tbFinanceTemp GROUP BY tbFinanceTemp.ActCode, tbFinanceTemp.BalanceAmount, tbFinanceTemp.BalanceType"
        DsTempTrial = New DataSet
        ad = New OleDbDataAdapter(qry, cn)
        ad.Fill(DsTempTrial, "tblTempTrial")

        'cmod = New OleDb.OleDbCommand(qry, cn)
        'cmod.ExecuteNonQuery()

        'qry = "UPDATE TempTrial SET TempTrial.BalanceAmount = TempTrial.SumOfDrAmount-TempTrial.SumOfCrAmount"
        '        cmod = New OleDb.OleDbCommand(qry, cn)
        '        cmod.ExecuteNonQuery()
        '        cn.Close()

    ( //////// here i want to remove the table TempTrial And wand to upadte the data set DsTempTrial)))

        '**** for Closing
        cn.Open()
        qstr = "delete * from tbFinanceTemp"
        cmod = New OleDb.OleDbCommand(qstr, cn)
        cmod.ExecuteNonQuery()

        qry = "INSERT INTO tbFinanceTemp ( ActCode, BalanceAmount ) SELECT TempTrial.ActCode, TempTrial.BalanceAmount FROM TempTrial  "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        'qstr = "Drop Table TempTrial"
        'cmod = New OleDb.OleDbCommand(qstr, cn)
        'cmod.ExecuteNonQuery()

        qry = "INSERT INTO tbFinanceTemp SELECT tbFinanceData.* FROM tbFinanceData Where tbFinanceData.VoucherDate >= # " & dtStartDate.Value & "# and tbFinanceData.VoucherDate <= # " & dtEndDate.Value & "#   "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tbFinanceTemp SET tbFinanceTemp.DrWeight = 0"
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tbFinanceTemp SET tbFinanceTemp.CrWeight = 0"
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "SELECT tbBsHead.BsName, tbAccountMaster.ActCode, tbAccountMaster.ActName, tbFinanceTemp.BalanceType, Sum(tbFinanceTemp.BalanceAmount) AS SumOfBalanceAmount, Sum(tbFinanceTemp.DrAmount) AS SumOfDrAmount, Sum(tbFinanceTemp.CrAmount) AS SumOfCrAmount, Sum(tbFinanceTemp.CrWeight) As CreditClosing, Sum(tbFinanceTemp.DrWeight) As DebitClosing Into tempTrialBalance "
        qry += "FROM tbBsHead INNER JOIN (tbAccountMaster INNER JOIN tbFinanceTemp ON tbAccountMaster.ActCode = tbFinanceTemp.ActCode) ON tbBsHead.BsCode = tbAccountMaster.BsCode "
        qry += "GROUP BY tbBsHead.BsName, tbAccountMaster.ActCode, tbAccountMaster.ActName, tbFinanceTemp.BalanceType, tbFinanceTemp.CrWeight, tbFinanceTemp.DrWeight "
        qry += "ORDER BY tbBsHead.BsName, tbAccountMaster.ActName"
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET SumOfBalanceAmount = 0 Where SumOfBalanceAmount Is Null "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET SumOfDrAmount = 0 Where SumOfDrAmount Is Null "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET SumOfCrAmount = 0 Where SumOfCrAmount Is Null "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()


        qry = "UPDATE tempTrialBalance SET CreditClosing = (SumOfBalanceAmount + SumOfDrAmount) - SumOfCrAmount"
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET DebitClosing = (SumOfBalanceAmount + SumOfDrAmount) - SumOfCrAmount"
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET CreditClosing = 0 Where CreditClosing > 0 "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET CreditClosing = 0-CreditClosing  "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET DebitClosing = 0 Where DebitClosing < 0 "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET BalanceType = 'Dr' Where SumOfBalanceAmount>=0 "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qry = "UPDATE tempTrialBalance SET BalanceType = 'Cr' Where SumOfBalanceAmount<0 "
        cmod = New OleDb.OleDbCommand(qry, cn)
        cmod.ExecuteNonQuery()

        qstr = "delete * from tempTrialBalance Where tempTrialBalance.DebitClosing = 0 and tempTrialBalance.CreditClosing = 0 "
        cmod = New OleDb.OleDbCommand(qstr, cn)
        cmod.ExecuteNonQuery()

        qry = "SELECT tempTrialBalance.BsName, tempTrialBalance.ActName, tempTrialBalance.SumOfBalanceAmount, tempTrialBalance.BalanceType, tempTrialBalance.SumOfDrAmount, tempTrialBalance.SumOfCrAmount "
        qry += ", tempTrialBalance.DebitClosing, tempTrialBalance.CreditClosing, tempTrialBalance.ActCode,tbCompany.CmpName, tbCompany.CmpAddress1, tbCompany.CmpCity "
        qry += " FROM tempTrialBalance,tbCompany "
        qry += "Order By tempTrialBalance.BsName, tempTrialBalance.ActName"
        DataSetTrialBalance = New DataSet
        ad = New OleDbDataAdapter(qry, cn)
        ad.Fill(DataSetTrialBalance, "tblTrialBalance")
        cn.Close()

        DgTrialBalance.Visible = True
        DgTrialBalance.Rows.Clear()
        DgTrialBalance.Focus()

        txtTotalCredit.Clear()
        txtTotalDebit.Clear()
        Dim i As Integer
        For i = 0 To DataSetTrialBalance.Tables("tblTrialBalance").Rows.Count - 1 Step 1
            txtTotalCredit.Text = Val(txtTotalCredit.Text) + Val(DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("CreditClosing"))
            txtTotalDebit.Text = Val(txtTotalDebit.Text) + Val(DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("DebitClosing"))

            DgTrialBalance.Rows().Add(New String() {
                DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("BsName"),
                DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("ActName"),
                String.Format("{0:f2}", Math.Abs(Val(DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("SumOfBalanceAmount")))),
                DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("BalanceType"),
                String.Format("{0:f2}", Val(DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("SumOfDrAmount"))),
                String.Format("{0:f2}", Val(DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("SumOfCrAmount"))),
                String.Format("{0:f2}", Math.Abs(DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("DebitClosing"))),
                String.Format("{0:f2}", Math.Abs(DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("CreditClosing"))),
                DataSetTrialBalance.Tables("tblTrialBalance").Rows(i).Item("ActCode")
            })
        Next
    End Sub

[Reverend Jim]

I see a large number of queries in a large block of undocumented code. What are you trying to do in one query?

B y the way, because you are doing multiple queries using the same connection it is better to open it at the start, then close it once all the queries are done.

[ryanjayson]

I think you should do this on Stored Procedure. Just created stored procedure and pass the parameters to it.