Row Update Not Updating Data source

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Alex
    #1

    Row Update Not Updating Data source

    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.SelectComman d.Parameters.Ad dWithValue("@Or derID",
    tbOrder.Text)

    Dim tbl As New DataTable("Orde rs")
    With tbl
    .Columns.Add("O rderID", GetType(String) )
    .PrimaryKey = New DataColumn() {.Columns("Orde rID")}
    .Columns.Add("I tem", GetType(String) )
    End With
    da.Fill(tbl)

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

    Dim cmdUpdate As New SqlCommand(strS QL, cn)
    cmdUpdate.Param eters.AddWithVa lue("@OrderID_N ew",
    rowToUpdate("Or derID"))
    cmdUpdate.Param eters.AddWithVa lue("@Item_New" ,
    rowToUpdate("It em"))

    cmdUpdate.Param eters.AddWithVa lue("@OrderID_O ld",
    rowToUpdate("Or derID", DataRowVersion. Original))
    cmdUpdate.Param eters.AddWithVa lue("@Item_Old" ,
    rowToUpdate("It em", DataRowVersion. Original))

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

    Tags: None
    • RobinS
      #2
      Re: Row Update Not Updating Data source

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

      If you are using SQLServerExpres s, 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" <fkml99@yahoo.c omwrote in message
      news:1177996216 .644875.57890@o 5g2000hsb.googl egroups.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.SelectComman d.Parameters.Ad dWithValue("@Or derID",
      tbOrder.Text)
      >
      Dim tbl As New DataTable("Orde rs")
      With tbl
      .Columns.Add("O rderID", GetType(String) )
      .PrimaryKey = New DataColumn() {.Columns("Orde rID")}
      .Columns.Add("I tem", GetType(String) )
      End With
      da.Fill(tbl)
      >
      Dim rowToUpdate As DataRow
      rowToUpdate = tbl.Rows.Find(t bOrder.Text)
      strSQL = "UPDATE Orders " & _
      "SET OrderID = @OrderID_New, " & _
      "Item = @Item_New " & _
      "WHERE OrderID = @OrderID_Old"
      >
      Dim cmdUpdate As New SqlCommand(strS QL, cn)
      cmdUpdate.Param eters.AddWithVa lue("@OrderID_N ew",
      rowToUpdate("Or derID"))
      cmdUpdate.Param eters.AddWithVa lue("@Item_New" ,
      rowToUpdate("It em"))
      >
      cmdUpdate.Param eters.AddWithVa lue("@OrderID_O ld",
      rowToUpdate("Or derID", DataRowVersion. Original))
      cmdUpdate.Param eters.AddWithVa lue("@Item_Old" ,
      rowToUpdate("It em", DataRowVersion. Original))
      >
      Try
      Dim intRecordsAffec ted As Integer
      intRecordsAffec ted = cmdUpdate.Execu teNonQuery()
      If intRecordsAffec ted = 1 Then
      rowToUpdate.Acc eptChanges()
      ElseIf intRecordsAffec ted = 0 Then
      MessageBox.Show ("Update Failed - Query Affected No
      Rows", "", MessageBoxButto ns.OK, MessageBoxIcon. Error)
      Else : MessageBox.Show ("Query affected " &
      intRecordsAffec ted & " rows?!?", "Error - Multiple Records Found",
      MessageBoxButto ns.OK, MessageBoxIcon. Error)
      End If
      Catch ex As Exception
      MessageBox.Show (ex.Message)
      Finally
      cn.Close()
      End Try
      End If
      End Sub
      >

        Comment

        • Randy
          #3
          Re: Row Update Not Updating Data source

          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.

            Comment

            • RobinS
              #4
              Re: Row Update Not Updating Data source

              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("Orde rs")
              With tbl
              .Columns.Add("O rderID", GetType(String) )
              .PrimaryKey = New DataColumn() {.Columns("Orde rID")}
              .Columns.Add("I tem", GetType(String) )
              End With

              before this? The fill should get the field names.

              da.Fill(tbl)

              What is the value of intRecordsAffec ted after it runs the query?

              Robin S.
              ---------------------
              "Randy" <randy.eastland @gmail.comwrote in message
              news:1178033742 .156297.182820@ y5g2000hsa.goog legroups.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.
              >

                Comment

                Previous template Next
                Working...