How to update either an dataadapter or datarow

Posted on 2009-05-14
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
  • 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 250 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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