none
Is this correct method ? RRS feed

  • Question

  • Hello Everyone,

     

    I am trying to update records in my MS Access database using the following code.

     

    Code Snippet

     

    Public Sub UpdateRecord(Optional ByVal strCatName As String = "", _

                            Optional ByVal strCatDesc As String = "")

    Dim blnUpdate As Boolean = False

    Dim cBuilder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daCatMaster)

    cBuilder.GetUpdateCommand()

     

    If (strCatName.Length > 0) And _

       (drCatMaster("CatName").ToString <> strCatName) Then

    drCatMaster.BeginEdit()

    drCatMaster("CatName") = strCatName

    drCatMaster.EndEdit()

    blnUpdate = True

    End If

    If (strCatDesc <> drCatMaster("CatDesc").ToString) Then

    drCatMaster.BeginEdit()

    drCatMaster("CatDesc") = strCatDesc

    drCatMaster.EndEdit()

    blnUpdate = True

    End If

    If blnUpdate Then

    daCatMaster.Update(dsCatMaster, "CatMaster")

    dtCatMaster.AcceptChanges()

    End If

    End Sub

     

     

     

    Please advice, is this correct method ? I am not receiving any error, but i just want to confirm.

     

    Thanks

    Thursday, August 9, 2007 10:11 AM

All replies

  • Move it from VB Express forum to .NET Framework Data Access and Storage forum for quicker responses.

     

    Best Regards,

    Martin Xie

    Monday, August 13, 2007 1:30 PM
  • You're duplicating work that the ADO classes are already doing for you:

    • You generally don't need to call BeginEdit() or EndEdit() when you're changing DataRow values.  You only need to do this when you have to suspend validation events (e.g. table constraints) while you're making a series of updates.
    • You don't need to use an Update flag to track whether or not you're updating the row; the table adapter's Update method will only execute an UPDATE command for rows that changed.

    If your method's really intended to update just the one DataRow, and not the whole DataTable, you should pass the row (and not the DataTable) to the TableAdapter's Update method.  As written, this method will push all pending changes made to the rows in the table out to the database, not just the one change you're making to this row.

     

    Without knowing more about your application, I can't say whether or not this method's even necessary.  Do you actually need to send every update back to the database the moment it's been made?  If not, there's no need to call the Update() method every time a row changes.

    Monday, August 13, 2007 8:29 PM
  • Hello Martin,

     

    Thanks for your suggestation.

     

    but i think i have a good answer from Rebert.

     

    Regards,

    Rajnikant

     

    Friday, August 17, 2007 6:24 PM
  • Hello Robert,

     

    First thing...

    As you suggested, I will pass the DataRow to the TableAdapter and remove the EndEdit() BeginEdit().

    This will definitely increase the performance.

     

    I will comeback soon on this.

     

     

    Second thing... My concern to send the data immediately to the database is,

    safety of data. I don't want to keep the data in cache. Once the modification is done,

    there is no need to keep the data unsaved.

     

    What do you think ?

     

     

    Next....

    This is an Inventory System.

     

     

    Most Important thing....

    Thanks & Best Regards.

    Friday, August 17, 2007 6:40 PM
  • I think that in general, I prefer to give the user control over the process, unless performing the update happens quickly enough that it has no impact on the user.  If I have 300 pieces of information to enter into a system, I will always choose entering them into Excel and uploading them (if possible) to putting them one at a time into a web form.  The risk that Excel (or my computer) will crash and lose my data is minuscule, and it's a risk I'm willing to expose myself to in order to avoid having to step through a tiresome enter/accept/wait for refresh cycle.  It's not hard to apply this idea to the design of custom software.

     

    I'm currently developing an application that's going to allow the user to enter huge amounts of information without ever sending it to the database.  The database is in a slow legacy application, and the users have modern PCs.  The application regularly saves the DataSet to the PC's hard drive.  (Actually the current version isn't using a DataSet but a custom object model, a design decision I seriously regret having made, and am unmaking just as fast as I can.  It's astounding how much code I'm throwing away in my refactoring.) 

     

    I can trust my users to know when they need to commit data back to the main database and when they don't.  (This is definitely not something that can be relied upon in general.)  And I leave it up to them to decide when they're going to incur the cost (i.e. waiting for the update to complete)

     

    But again, if the update's happening fast enough that the user doesn't have to wait for it, then sure, go right ahead.

    Saturday, August 18, 2007 7:42 AM