?
Solved

How to update either an dataadapter or datarow

Posted on 2009-05-14
5
Medium Priority
?
494 Views
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)
        Next

        adapter.Update(dsPhys.Tables(cboTables.Text))
        Me.Cursor = Cursors.Arrow

        conn.Close()

    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
        Next


        ' 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)
                    Next
                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)
            Next

             row.AcceptChanges()

        End If

    End Sub

===============

Thank you in advance for your assistance.
0
Comment
Question by:CarmenMTorres
[X]
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
5 Comments
 
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
adapter.Update(dsPhys.Tables(cboTables.Text))

Open in new window

0
 

Author Comment

by:CarmenMTorres
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?

Thanks.
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24391258
CarmenMTorres:
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:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx
0
 

Author Comment

by:CarmenMTorres
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.

Thanks.
0
 
LVL 11

Accepted Solution

by:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

770 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