locked
Problem in :Find a row from DataTable & update it RRS feed

  • Question

  • Hi All,

        In my VB.NET application, I am connecting to an SQL DB. Retrieving data & storing in a DataTable :

        Private Sub FillToworkAdapter()
            Dim sql As String = "SELECT ACCMST.accmst_nAccNo, ACCMST.accmst_nGrpNo, DOCHDR.dochdr_sDocType, DOCHDR.dochdr_sDocSeries, DOCHDR.dochdr_nDocNo, DOCHDR.dochdr_lDocDt, DOCHDR.dochdr_lDocDueDt, DSOTSTND.otstnd_dOtstndgAmt, DSOTSTND.otstnd_dDocLnAmt, DSOTSTND.otstnd_cOtstndgSign, DSOTSTND.MATCHAMT FROM (DSOTSTND INNER JOIN ACCMST ON DSOTSTND.accmst_nAccNo = ACCMST.accmst_nAccNo) INNER JOIN (DOCHDR INNER JOIN TXNTYP ON DOCHDR.dochdr_sDocType = TXNTYP.txntyp_sDocTyp) ON DSOTSTND.dochdr_lDocId = DOCHDR.dochdr_lDocId WHERE(((DOCHDR.dochdr_lDocDt) <= 20120630) And ((ACCMST.accmst_cLdgType) = '" + type + "')) ORDER BY ACCMST.accmst_nAccNo, ACCMST.accmst_nGrpNo;"
            toWork_Adap = New OleDb.OleDbDataAdapter(sql, connStr)
            toWork_Adap.Fill(toWork_Ds)
            toWork_Dt = toWork_Ds.Tables(0)
            ' Create 2 cols in toWork_Dt
            toWork_Dt.Columns.Add("MatchAmt", GetType(Double))
            toWork_Dt.Columns.Add(New DataColumn("MatchRefId", GetType(Double)))
            workingDg.DataSource = toWork_Dt
            Return
        End Sub

    This table has several records. I created another table to get Distinct accNo from the table. For each distinct accNo, I create 2 tables ctDt & dbDt that contains rows of Credit and Debit enteries respectively. As I got to work with all (max) credit enteries with ,ax debit enteries & get the outstanding figure for each debit entry.

                ' Pick up otstnd_dOtstndgAmt frmo toWork of "C" as workAmt
                crDt = GetRecordFromToWorkBySIGN("C", tempWordTb)
                dbDt = GetRecordFromToWorkBySIGN("D", tempWordTb)

    Now, I work on entries & time to update values in row & update the table :

    For Each crRow As DataRow In crDt.Rows
        '.............
              For Each drRow As DataRow In dbDt.Rows
                      '--------------
                      If (outAmt - dbAmt >= 0) Then
                            '..............
                       Else
                            ' Deduct partial db from cr amt & make cr = 0
                            Dim deducted As Double = outAmt
                            dbAmt = dbAmt - deducted
                            outAmt = outAmt - deducted
                            ' Get the row index from tempWordTb
                            Dim r As Integer = tempWordTb.Rows.IndexOf(drRow)  ' RETURNS -1
                            ' Update MatchAmt = left
                            drRow.Item("MatchAmt") = dbAmt
                            tempWordTb.Rows(r).Item("MatchAmt") = dbAmt
                            ' Update MatchRefId = 
                            drRow.Item("MatchRefId") = crRefId
                            tempWordTb.Rows(r).Item("MatchRefId") = crRefId
                            dbDt.AcceptChanges()
                            tempWordTb.AcceptChanges()
                        End If

    Now comes the issue & concern, am able to update MatchAmt & MatchRefId in drRow & dbDt.

    BUT, these wont be updated in tempWordTb, as dbDt is dynamically generated to just work on the enteries. So before updating drRow, I seek the drRow in tempWordTb to update the row in actual table who has respective adapter  & dataset to later update in the SQL.

    Why the Find Row returns -1 over here ? After all the drRow is a part of tempWordTb (indirectly thru dbDt).

    I beleive tempWordTb.AcceptChanges() will keep it updated. How do I update it to the SQL actual db later on ?

    Pls try to help and solve me with this query earliest, as m off-time working with this complicated structure & requirements.

    Thanks


    Thanks
    If you find any answer helpful, then click "Vote As Helpful" and if it also solves your question then also click "Mark As Answer".

    Monday, July 30, 2012 7:22 PM

Answers

  • Why the Find Row returns -1 over here ? After all the drRow is a part of tempWordTb (indirectly thru dbDt).

    Based on my understanding, the dbDT is not the same to the table tempWordTb. I'm not sure the way you create the dbDT  table since your create it by using the custom method GetRecordFromToWorkBySIGN. 

    However, you should not use the DataRowCollection.IndexOf Method is used to get the index of the specified DataRow object. You can get the index of a data row since it is not a row of the tempWordTb. Even the DataRow used to search has same structure and data with one of the rows in tempWordTb.

    The IndexOf method is used to search for Object. In the sample below, the row and tempWordTb.Rows[1] have same structure and data but they are not same object.

    Dim tempWordTb As New DataTable()
    tempWordTb.Columns.Add("A")
    tempWordTb.Columns.Add("B")
    tempWordTb.Rows.Add("a1", "b1")
    tempWordTb.Rows.Add("a2", "b2")
    tempWordTb.Rows.Add("a3", "b3")
    
    
    Dim dbDt As DataTable = tempWordTb.Clone()
    Dim dr As DataRow = dbDt.NewRow()
    dr.ItemArray = TryCast(tempWordTb.Rows(1).ItemArray.Clone(), Object())
    dbDt.Rows.Add(dr)
    For Each row As DataRow In dbDt.Rows
    	Dim i As Integer = tempWordTb.Rows.IndexOf(row)
    	'i =-1, you will alway get -1 since the datarow is not even a row of the tempWordTb 
    		'j = 1
    	Dim j As Integer = tempWordTb.Rows.IndexOf(tempWordTb.Rows(1))
    Next

    If the Table have primary key, then you can use the DataRowCollection.Find Method. Otherwise, you have to compare each item one by one.

    I beleive tempWordTb.AcceptChanges() will keep it updated. How do I update it to the SQL actual db later on ?

    I don't think you should call AcceptChanges method here. You should get change of the DataTable and handle them according to the row state. Since you modify the datarow, the row state should be modified, You can write the update command to update them to database. For more details, see http://msdn.microsoft.com/en-us/library/ms233819(v=vs.100).aspx.

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by truptivd Wednesday, August 1, 2012 4:00 PM
    Wednesday, August 1, 2012 11:37 AM

All replies

  • Why the Find Row returns -1 over here ? After all the drRow is a part of tempWordTb (indirectly thru dbDt).

    Based on my understanding, the dbDT is not the same to the table tempWordTb. I'm not sure the way you create the dbDT  table since your create it by using the custom method GetRecordFromToWorkBySIGN. 

    However, you should not use the DataRowCollection.IndexOf Method is used to get the index of the specified DataRow object. You can get the index of a data row since it is not a row of the tempWordTb. Even the DataRow used to search has same structure and data with one of the rows in tempWordTb.

    The IndexOf method is used to search for Object. In the sample below, the row and tempWordTb.Rows[1] have same structure and data but they are not same object.

    Dim tempWordTb As New DataTable()
    tempWordTb.Columns.Add("A")
    tempWordTb.Columns.Add("B")
    tempWordTb.Rows.Add("a1", "b1")
    tempWordTb.Rows.Add("a2", "b2")
    tempWordTb.Rows.Add("a3", "b3")
    
    
    Dim dbDt As DataTable = tempWordTb.Clone()
    Dim dr As DataRow = dbDt.NewRow()
    dr.ItemArray = TryCast(tempWordTb.Rows(1).ItemArray.Clone(), Object())
    dbDt.Rows.Add(dr)
    For Each row As DataRow In dbDt.Rows
    	Dim i As Integer = tempWordTb.Rows.IndexOf(row)
    	'i =-1, you will alway get -1 since the datarow is not even a row of the tempWordTb 
    		'j = 1
    	Dim j As Integer = tempWordTb.Rows.IndexOf(tempWordTb.Rows(1))
    Next

    If the Table have primary key, then you can use the DataRowCollection.Find Method. Otherwise, you have to compare each item one by one.

    I beleive tempWordTb.AcceptChanges() will keep it updated. How do I update it to the SQL actual db later on ?

    I don't think you should call AcceptChanges method here. You should get change of the DataTable and handle them according to the row state. Since you modify the datarow, the row state should be modified, You can write the update command to update them to database. For more details, see http://msdn.microsoft.com/en-us/library/ms233819(v=vs.100).aspx.

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by truptivd Wednesday, August 1, 2012 4:00 PM
    Wednesday, August 1, 2012 11:37 AM
  • Thanks Bob,

        I added 1 more col in tempWordTb name PriKey adding counter # to all entries. I thought instead of searching thru 5000+ entries for each entry, adding a primary key col with a counter {1, 2, 3, 4, 5, ...} also is much worthful.

        I get dbDt table as :

        ' Gets all records from toWork_Dt of the passed Sign
        Private Function GetRecordFromToWorkBySIGN(ByVal sign As Char, ByVal tbl As DataTable) As DataTable
            Dim drc As DataTable = Nothing
            Dim dv As New DataView
            dv.Table = tbl
            dv.RowFilter = "otstnd_cOtstndgSign='" & sign & "'"
            If (dv.Count > 0) Then
                dv.Sort = "dochdr_lDocDt ASC"
                drc = dv.ToTable
            End If
            Return drc
        End Function

    And Finally, I find the Row using the created col "PriKey":

                        ' Get the row index from tempWordTb
                        Dim obj As Object = drRow.Item("PriKey")
                        ' Update MatchAmt = left
                        drRow.Item("MatchAmt") = dbAmt
                        tempWordTb.Rows.Find(obj).Item("MatchAmt") = dbAmt
                        ' Update MatchRefId = 
                        drRow.Item("MatchRefId") = crRefId
                        tempWordTb.Rows.Find(obj).Item("MatchRefId") = crRefId
    

    I guess, after this as u said, to update later in SQL db, I can retrieve tempWordTb.Getchanges() and update the exisitng updated rows to sql DB.


    Thanks
    If you find any answer helpful, then click "Vote As Helpful" and if it also solves your question then also click "Mark As Answer".

    Wednesday, August 1, 2012 4:00 PM