UPDATE Problem

[Joe Delphi]

Hi,

I am using ADO.NET 1.1 and am attempting to UPDATE a record in the
Access database. Problem is that the statements execute fine with no
error, but when I look at the database I see that the update did not really
happen. What is wrong? Do I need to somehow commit the transaction?
My code is below:

Dim cmdUpdate As System.Data.OleDb.OleDbCommand

daChangeRequests.UpdateCommand.CommandText = _
"UPDATE CHANGE_REQUESTS " & _
"SET TITLE = '" & Me.txtTitle.Text & "', " & _
"DESCRIPTION = '" & Me.txtDescription.Text & "', " & _
"PRIORITY = " & CInt(Me.ddlstPriority.SelectedItem.Text) & ", " & _
"SUB_BY = '" & Me.ddlstSubBy.SelectedItem.Text & "', " & _
"ASSIGNED_TO = '" & Me.ddlstAssigned.SelectedItem.Text & "', " & _
"NOTES = '" & Me.txtNotes.Text & "' " & _
"WHERE CR_ID = " & Me.txtCRID.Text

cmdUpdate = Me.daChangeRequests.UpdateCommand

Try
cnChangeRequests.Open()
cmdUpdate.ExecuteNonQuery()
lblSaved.Visible = True
lblSaved.ForeColor = White
lblSaved.Text = "CR Succesfully Updated"
Catch
cnChangeRequests.Close()
lblSaved.Visible = True
lblSaved.ForeColor = Red
lblSaved.Text = Err.Description
End Try
cnChangeRequests.Close()
End Sub

 

[W.G. Ryan - MVP]

Joe - it could be a few different things. There's two things though that are
potential problems that are worth mentioning first.

Instead of concatenating the control values in with the sql, I'd highly
recommend using parameters instead. one person with a last name like O'Brian
could break your statement as it stands now, not to mention giving rise to
injection attacks.

Next, it appears you're using a dataAdapter here. If it's configured
properly (with paramaeters and column mappings in place), you can do away
with the ExecuteNonQuery and just call Update. Make sure that the
dataset.HasChanges before calling update though...

Also, you want to take the cnChangeRequests.Close() call and stick it in a
Finally block. It's the only way to ensure that it's closed (well, if you're
using 2005 the using statement can do it too).

Ok, so what's the problem here? First thing I'd look at is to make sure I
can write to the db. Make sure you have write permissions on the directory
and that you can insert/update records.

Next, can you verify the Sql Statement that's being fired? If you use a
breakpoint and debug.Write the commandText and verify it's what you want.
I'm not sure where the DataAdapter's code is executing in comparison to the
values you're setting below, so let's verify that you have legit values in
there (for instance, there's a record matching the Where clause value) and
that the command is what you expect.

Let me know and we'll take it from there.

Cheers,

Bill

 

[Joe Delphi]

Thank you. I have implemented your changes but the update is still not
taking effect. By stepping through the debugger, I can tell that the
HasChanges method of the data adapter always returns a False even though I
have made changes in the text boxes on the form. New version of my code is
below:

With daChangeRequests.UpdateCommand
..Parameters(0).Value = CInt(Me.txtCRID.Text)
..Parameters(1).Value = Me.txtTitle.Text
..Parameters(2).Value = Me.txtDescription.Text
..Parameters(3).Value = CInt(Me.ddlstPriority.SelectedItem.Text)
..Parameters(4).Value = Me.ddlstStatus.SelectedItem.Text
..Parameters(5).Value = Me.ddlstSubBy.SelectedItem.Text
..Parameters(6).Value = Me.ddlstStatus.SelectedItem.Text
..Parameters(7).Value = Me.ddlstAssigned.SelectedItem.Text
..Parameters(8).Value = Me.txtNotes.Text
..Parameters(9).Value = Me.txtCRID.Text
End With

If DsChangeRequests1.HasChanges() Then
Try
cnChangeRequests.Open()
daChangeRequests.Update(DsChangeRequests1)
lblSaved.Visible = True
lblSaved.ForeColor = White
lblSaved.Text = "CR Succesfully Updated"
Catch
lblSaved.Visible = True
lblSaved.ForeColor = Red
lblSaved.Text = Err.Description
Finally
cnChangeRequests.Close()
End Try
Else
lblSaved.Text = "No changes made."
End If
End Sub

 

[W.G. Ryan - MVP]

If HasChanges is false, that's the problem. Call EndCurrentEdit on the
underlying binding - that should take the rows out of 'edit' mode hence
making the changes visible.