How to update either an dataadapter or datarow

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.
Maria TorresData AnalystAsked:
Who is Participating?
Muhammad Ousama GhazaliConnect With a Mentor Solution Analyst & ArchitectCommented:
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.
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
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

Maria TorresData AnalystAuthor Commented:
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?

Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
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:
Maria TorresData AnalystAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.