Tech Off Thread

3 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Working with DataSets

Back to Forum: Tech Off
  • User profile image
    W3bbo

    'lo

    I've written a quick proggy that removes spaces from a field in a database table and logs them to a stringbuilder.

    Can someone confirm that I'm using the correct implementation of the DataAdapter.Update() method?

            Dim Adapter As New System.Data.OleDb.OleDbDataAdapter(strSQL, Conn)
            
            Adapter.Fill(DataSet)
            
            
            ' Remove spaces from all the filenames
            
            Dim i As Integer
            Dim SB As New System.Text.StringBuilder()
            
            Dim strNew As String
            Dim strOld As String
            
            For i = 0 To DataSet.Tables("Product").Rows.Count - 1
            
                strOld = DataSet.Tables("Product").Rows(i).Item("")
                    
                ' Does the filename contain a space?
                If strOld.ToString.IndexOf(" ")
                
               
        ' If so, remove the space and add it to the
    stringbuilder's log    
                    strNew = Replace(strOld, " ", "_")
                    
                    SB.Append(strOld & " -> " & strNew)
                    
                    DataSet.Tables("Product").Rows(i).Item("") = strNew
                    
                End If
                
                
                
            Next
            
            ' Comit changes back to the database
            DataSet.AcceptChanges()
            
            Adapter.Update(DataSet)    
    


    Thanks

  • User profile image
    Maurits

    Why not just

    UPDATE
        Product
    SET
        FileName = Replace(FileName, " ", "_")
    WHERE
        FileName LIKE '% %'

    EDIT: Oh I see you want a log

  • User profile image
    JPeless

    You don't want to call AcceptChanges() on the dataset until AFTER the adapter has written those changes to the database via the Update method call.

    Calling the AcceptChanges will change any RowState for the rows to Unchanged from Modified and it only "commits" it to the in-memory dataset.  Then when you call the Update method it won't see any "Modified", "Deleted", or "Added" rows to execute the update/insert/delete commands against.

    It should be find to call the adapter's Update method this way (once you switch the order), unless you have more than one table in the dataset and you only want to update one, etc.

    Jared

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.