[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


How to update either an dataadapter or datarow

Posted on 2009-05-14
Medium Priority
Last Modified: 2013-11-15
I'm fairly new to OleDbDataAdapter and I am having trouble trying to update a microsoft access database.  I'm hoping someone can tell me why in my code it appears the datarow is being updated but when I open the actual database to veiw the contents, the specific record was not updated.  Below is my code:


Private Sub btnProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcess.Click

        Dim connection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath() & "\" & "worksheet.mdb;"
        Dim conn As New OleDbConnection(connection)
        Dim adapter As New OleDbDataAdapter
        Dim cmd As OleDbCommand
        Dim dTable As DataTable
        Dim sSQL As String
        Dim dsPhys As DataSet = New DataSet

        If cboTables.Text = "" Then
            MsgBox("Please select the name of the table to shift addresses", MsgBoxStyle.OKOnly, "Missing Table Selection")
            Exit Sub
        End If

        sSQL = String.Concat("SELECT * FROM ", cboTables.Text, " order by last, first")
        cmd = New OleDbCommand(sSQL, conn)
        adapter.SelectCommand = cmd
        adapter.Fill(dsPhys, Trim(cboTables.Text))

       Me.Cursor = Cursors.WaitCursor

        For Each row As DataRow In dsPhys.Tables(cboTables.Text).Rows
            bFlag = False
            Call beginShifting(row)

        Me.Cursor = Cursors.Arrow


    End Sub

 Private Sub beginShifting(ByRef row As DataRow)

        Dim arrTmp(4, 6) As String
        Dim i, j, m As Integer
        Dim temp(6) As String

        ' Populate/Initialize multidimensional array

        For i = 0 To 3
            arrTmp(i, 0) = row("date_Addr" & CStr(i + 1)).ToString
            arrTmp(i, 1) = row("Address" & CStr(i + 1)).ToString
            arrTmp(i, 2) = row("City" & CStr(i + 1)).ToString
            arrTmp(i, 3) = row("Zip" & CStr(i + 1)).ToString
            arrTmp(i, 4) = row("County" & CStr(i + 1)).ToString
            arrTmp(i, 5) = row("Phone" & CStr(i + 1)).ToString

        ' Sort multidimensional array in descending order

        For i = 0 To 3
            For j = i + 1 To 3
                If (arrTmp(i, 0) < arrTmp(j, 0)) Or (arrTmp(i, 0) = arrTmp(j, 0) And arrTmp(i, 1) < arrTmp(j, 1)) Then
                    bFlag = True
                    For m = 0 To 5
                        temp(m) = arrTmp(i, m)
                        arrTmp(i, m) = arrTmp(j, m)
                        arrTmp(j, m) = temp(m)
                End If
            Next j
        Next i

        If bFlag = True Then
            For i = 1 To 4
                row("date_Addr" & CStr(i)) = arrTmp(i - 1, 0)
                row("address" & CStr(i)) = arrTmp(i - 1, 1)
                row("city" & CStr(i)) = arrTmp(i - 1, 2)
                row("zip" & CStr(i)) = arrTmp(i - 1, 3)
                row("county" & CStr(i)) = arrTmp(i - 1, 4)
                row("phone" & CStr(i)) = arrTmp(i - 1, 5)


        End If

    End Sub


Thank you in advance for your assistance.
Question by:CarmenMTorres
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24390843
Try removing row.AcceptChanges() from beginShifting routine.
However, after doing above you may receive an error saying that Select command cannot be used for updating. So you have to set an Update Command to the adapter as follows:

Dim parameter As OleDbParameter = Nothing
Dim cmdUpdate As OleDbCommand = New OleDbCommand("UPDATE TableName SET FieldA = @FieldA, FieldB = @FieldB WHERE KeyID = @KeyID")
cmdUpdate.Parameters.Add("@FieldA", OleDbType.VarChar, 20, "FieldA")
cmdUpdate.Parameters.Add("@FieldB", OleDbType.VarChar, 20, "FieldB")
parameter = cmdUpdate.Parameters.Add("@KeyID, OleDbType.Integer, 4, "KeyID")
parameter.SourceVersion = DataRowVersion.Original
adapter.UpdateCommand = cmdUpdate

Open in new window


Author Comment

ID: 24391193
My understanding from reading several manuals is that I should be able to update the row directly (i.e., datarow).  The materials that I read indicates that all I have to do is reference the column and then assign the new value to it by using a simple assignment operator.  Afterwards, I then use the DataAdapter Update method to update the table.

I don't understand why I should have to use parameters.

Can I or cannot update datarow directly?  If so, how?

LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24391258
Your understanding is right but given that Primary Key information is available within the Select Command's SQL statement. When I suggested the code above, I mentioned that removing the row.AcceptChanges may give an error so in that case UpdateCommand must be provided.
For a better discussion/documentation see the official documentation for UpdateCommand property here:

Author Comment

ID: 24450055
Is there a way of updating a record without having to specify the parameters.  The reason I'm asking is because I would have to explicitly list 24 parameters that are being updated.

LVL 11

Accepted Solution

Muhammad Ousama Ghazali earned 750 total points
ID: 24455652
Replace @FieldA, @FieldB etc from the UPDATE QUERY and directly concatenate the values if you don't wish to have the parameters. In that case, you too have to specify the 24 values and the use of & sign to concatenate may make it a long statement that might have to be broken into multilines. Anyways, if it is what is easy for you, go ahead with it but remember parameterized approach is always better, secure, more readable and easy to manage.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question