• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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)
        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
CarmenMTorres
Asked:
CarmenMTorres
  • 3
  • 2
1 Solution
 
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
adapter.Update(dsPhys.Tables(cboTables.Text))

Open in new window

0
 
CarmenMTorresAuthor 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?

Thanks.
0
 
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
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
 
CarmenMTorresAuthor 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.

Thanks.
0
 
Muhammad Ousama GhazaliSolution 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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now