Row Update Not Updating Data source

1 May 2007 5:10 AM

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

1 May 2007 7:12 AM

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.
--------------------------------

Show quoteHide quote

"Alex" <fkm***@yahoo.com> wrote in message
news:1177996216.644875.57890@o5g2000hsb.googlegroups.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
>

1 May 2007 3:35 PM

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.

3 May 2007 5:26 AM

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.
---------------------
Show quoteHide quote

"Randy" <randy.eastl***@gmail.com> wrote in message
news:1178033742.156297.182820@y5g2000hsa.googlegroups.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.
>