Row Update Not Updating Data source

[Alex]

I've got a procedure designed to modify the contents of a single row
in a data table. The code appears to work fine in that it compiles
and executes without error and the changes are reflected in the
dataset. However, when I close and re-open the app, the changes are
lost, which means that they are not reaching the datasource. Can
anybody see why this is happening? I've searched all over and I think
that this should work - but it obviously doesn't (and I'm kind of a
moron). Any help is appreciated.

Thanks.

Dim strSQL As String
strSQL = "SELECT * FROM Orders WHERE OrderID = @OrderID"

cn.Open()

Dim da As New SqlDataAdapter(strSQL, cn)
da.SelectCommand.Parameters.AddWithValue("@OrderID ",
tbOrder.Text)

Dim tbl As New DataTable("Orders")
With tbl
.Columns.Add("OrderID", GetType(String))
.PrimaryKey = New DataColumn() {.Columns("OrderID")}
.Columns.Add("Item", GetType(String))
End With
da.Fill(tbl)

Dim rowToUpdate As DataRow
rowToUpdate = tbl.Rows.Find(tbOrder.Text)
strSQL = "UPDATE Orders " & _
"SET OrderID = @OrderID_New, " & _
"Item = @Item_New " & _
"WHERE OrderID = @OrderID_Old"

Dim cmdUpdate As New SqlCommand(strSQL, cn)
cmdUpdate.Parameters.AddWithValue("@OrderID_New",
rowToUpdate("OrderID"))
cmdUpdate.Parameters.AddWithValue("@Item_New",
rowToUpdate("Item"))

cmdUpdate.Parameters.AddWithValue("@OrderID_Old",
rowToUpdate("OrderID", DataRowVersion.Original))
cmdUpdate.Parameters.AddWithValue("@Item_Old",
rowToUpdate("Item", DataRowVersion.Original))

Try
Dim intRecordsAffected As Integer
intRecordsAffected = cmdUpdate.ExecuteNonQuery()
If intRecordsAffected = 1 Then
rowToUpdate.AcceptChanges()
ElseIf intRecordsAffected = 0 Then
MessageBox.Show("Update Failed - Query Affected No
Rows", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else : MessageBox.Show("Query affected " &
intRecordsAffected & " rows?!?", "Error - Multiple Records Found",
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try
End If
End Sub

[RobinS]

After you run this, and before it exits, is the data in the database?

If you are using SQLServerExpress, there's some setting when you add a data
source to your project that says "copy it over every time I run my app",
and you could be replacing the copy you have just updated.

Robin S.
--------------------------------

"Alex" <fk****@yahoo.comwrote in message
news:11*********************@o5g2000hsb.googlegrou ps.com...

I've got a procedure designed to modify the contents of a single row
in a data table. The code appears to work fine in that it compiles
and executes without error and the changes are reflected in the
dataset. However, when I close and re-open the app, the changes are
lost, which means that they are not reaching the datasource. Can
anybody see why this is happening? I've searched all over and I think
that this should work - but it obviously doesn't (and I'm kind of a
moron). Any help is appreciated.

Thanks.

Dim strSQL As String
strSQL = "SELECT * FROM Orders WHERE OrderID = @OrderID"

cn.Open()

Dim da As New SqlDataAdapter(strSQL, cn)
da.SelectCommand.Parameters.AddWithValue("@OrderID ",
tbOrder.Text)

Dim tbl As New DataTable("Orders")
With tbl
.Columns.Add("OrderID", GetType(String))
.PrimaryKey = New DataColumn() {.Columns("OrderID")}
.Columns.Add("Item", GetType(String))
End With
da.Fill(tbl)

Dim rowToUpdate As DataRow
rowToUpdate = tbl.Rows.Find(tbOrder.Text)
strSQL = "UPDATE Orders " & _
"SET OrderID = @OrderID_New, " & _
"Item = @Item_New " & _
"WHERE OrderID = @OrderID_Old"

Dim cmdUpdate As New SqlCommand(strSQL, cn)
cmdUpdate.Parameters.AddWithValue("@OrderID_New",
rowToUpdate("OrderID"))
cmdUpdate.Parameters.AddWithValue("@Item_New",
rowToUpdate("Item"))

cmdUpdate.Parameters.AddWithValue("@OrderID_Old",
rowToUpdate("OrderID", DataRowVersion.Original))
cmdUpdate.Parameters.AddWithValue("@Item_Old",
rowToUpdate("Item", DataRowVersion.Original))

Try
Dim intRecordsAffected As Integer
intRecordsAffected = cmdUpdate.ExecuteNonQuery()
If intRecordsAffected = 1 Then
rowToUpdate.AcceptChanges()
ElseIf intRecordsAffected = 0 Then
MessageBox.Show("Update Failed - Query Affected No
Rows", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else : MessageBox.Show("Query affected " &
intRecordsAffected & " rows?!?", "Error - Multiple Records Found",
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try
End If
End Sub

[Randy]

How can I determine if the data is in the db before it exits?

I don't think that the db is being copied. I know what you are
referring to, but I don't think that this is the case. I have similar
code to add and delete records and that code works just fine. If this
was the issue, I would think that it would be a problem in those
cases, too.

Other than that, do you see any problems with the code itself?

Thanks, Robin.

[RobinS]

You can determine if the data is in the db before it exits by doing
something like re-querying that specific record and displaying the values
in the record to see if they have changed. I would close your connection,
then re-open it and do this, and check and see if the values are there. If
they aren't, then they did not get committed.

Why are you doing this

Dim tbl As New DataTable("Orders")
With tbl
.Columns.Add("OrderID", GetType(String))
.PrimaryKey = New DataColumn() {.Columns("OrderID")}
.Columns.Add("Item", GetType(String))
End With

before this? The fill should get the field names.

da.Fill(tbl)

What is the value of intRecordsAffected after it runs the query?

Robin S.
---------------------
"Randy" <ra************@gmail.comwrote in message
news:11**********************@y5g2000hsa.googlegro ups.com...

How can I determine if the data is in the db before it exits?

I don't think that the db is being copied. I know what you are
referring to, but I don't think that this is the case. I have similar
code to add and delete records and that code works just fine. If this
was the issue, I would think that it would be a problem in those
cases, too.

Other than that, do you see any problems with the code itself?

Thanks, Robin.