Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

update method using stored procedure passing a datarow

I am trying to update a table in an sql database using optimistic concurrency, but I do not know how to compare current and existing values using a datarow

this is the stored procedure

ALTER PROCEDURE [dbo].[ProcUpdateConcAllValues]
      -- Add the parameters for the stored procedure here
      @ParamPersonName varchar(50),
      @ParamDepartmentID int,
      
      @ParamOriginalPeopleID  int,
      @ParamOriginalPersonName  varchar(50),
      @paramOriginalDepartmentID  int
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      Update People

    Set
        PersonName = @ParamPersonName,
            People.DepartmentID = @ParamDepartmentID
    Where
        PeopleID = @ParamOriginalPeopleID
      AND
        PersonName = @ParamOriginalPersonName
      AND
       People.DepartmentID = @ParamOriginalDepartmentID
END


this is the method that use the stored procedure

 Public Sub EditEmployeeAllValues(ByVal Employee As DataRow)

      Dim daUpdate As SqlDataAdapter

      Try

         daUpdate = New SqlDataAdapter("ProcUpdateConcAllValues", GetConn())

         'parameters to update
         daUpdate.UpdateCommand.Parameters.AddWithValue("@ParamPersonName", Employee.Item("PersonName", DataRowVersion.Current))
         daUpdate.UpdateCommand.Parameters.AddWithValue("@ParamDepartmentID", Employee.Item("DepartmentID", DataRowVersion.Current))

         'original parameters to the WHERE clause for optimisic concurrency
         daUpdate.UpdateCommand.Parameters.AddWithValue("@ParamOriginalPeopleID", Employee.Item("PeopleId", DataRowVersion.Original))
         daUpdate.UpdateCommand.Parameters.AddWithValue("@ParamOriginalPersonName", Employee.Item("PersonName", DataRowVersion.Original))
         daUpdate.UpdateCommand.Parameters.AddWithValue("@paramOriginalDepartmentID", Employee.Item("DepartmentID", DataRowVersion.Original))


         GetConn.Close()


      Catch ex As Exception

         Throw New Exception("Problem occurred in EditEmployeeAllvalues routine while trying to update a record")

      End Try

   End Sub

1) DO I NEED TO ADD SOMETHING ELSE IN THE ABOVE METOD (e.g. execute command or update database ..)

2) I need to figure out how to pass the datarow from the method call in the routine below

and this is the method that I am using to pass the datarow AND THIS IS WHAT I CAN'T FIGURE OUT

 Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click

      Dim PeopleID As Integer
      Dim PersonName As String
      Dim DepartmentID As Integer
      Dim ds As DataSet
      Dim row As DataRow



      PeopleID = CType(TxtID.Text, Int32)
      PersonName = Txtname.Text.ToString
      DepartmentID = CType(TextDeptID.Text, Int32)

      ds = UseSql.GetEmployee(PersonName)

      For intRow As Integer = 0 To ds.Tables("People").Rows.Count - 1
         Dim dr As DataRow = ds.Tables("People").Rows(intRow)

         If dr.Item(intRow) = PeopleID Then
            row.Item("PeopleID") = PeopleID
            row.Item("PersonName") = PersonName
            row.Item("DepartmentID") = DepartmentID
         Else

         End If

      Next

      UseSql.EditEmployeeAllValues(row)




   End Sub
End Class
0
brillox
Asked:
brillox
  • 5
1 Solution
 
brilloxAuthor Commented:
I came up with this .. but now I do not get any errors, but nothing happens

Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click

      Dim PeopleID As Integer
      Dim PersonName As String
      Dim DepartmentID As Integer
      Dim ds As DataSet

      PeopleID = CType(TxtID.Text, Int32)
      PersonName = Txtname.Text.ToString
      DepartmentID = CType(TextDeptID.Text, Int32)

      ds = UseSql.GetEmployee(PersonName)

      For intRow As Integer = 0 To ds.Tables("People").Rows.Count - 1

         If (ds.Tables("People").Rows(intRow).Item(0) = PeopleID) Then

            ds.Tables("People").Rows(intRow).Item(0) = PeopleID
            ds.Tables("PersonName").Rows(intRow).Item(1) = PersonName
            ds.Tables("DepartmentID").Rows(intRow).Item(2) = DepartmentID

            UseSql.EditEmployeeAllValues(ds.Tables("People").Rows(intRow))

         End If

      Next

   End Sub
0
 
brilloxAuthor Commented:
small changes again, not this method works,

 For intRow As Integer = 0 To ds.Tables("People").Rows.Count - 1

         If (ds.Tables("People").Rows(intRow).Item(0) = PeopleID) Then

            ds.Tables("People").Rows(intRow).Item(0) = PeopleID
            ds.Tables("People").Rows(intRow).Item(1) = PersonName
            ds.Tables("People").Rows(intRow).Item(2) = DepartmentID

            UseSql.EditEmployeeAllValues(ds.Tables("People").Rows(intRow))

         End If


but definitely there is a lot wrong into

Public Sub EditEmployeeAllValues(ByVal Employee As DataRow)
0
 
brilloxAuthor Commented:
I changed method and I am now working with datatable with which i am a bit more confident....  

Now no exception or errors, but nothing changed into the database. below the update procedure and the buttonclick procedure

##########  UPDATE PROCEDURE  #######

 Public Sub EditEmployeeAllValues(ByVal Employee As DataTable, ByVal intRow As Integer)

      Dim daUpdate As SqlDataAdapter
      Dim cmdUpdate As SqlCommand
      Dim EmployeeId As Integer
      Dim EmployeeName As String
      Dim DepartmentID As Integer


      Try
         cmdUpdate = New SqlCommand("ProcUpdateConcAllValues", GetConn())
         daUpdate = New SqlDataAdapter(cmdUpdate)
         daUpdate.UpdateCommand = cmdUpdate

         EmployeeId = Employee.Rows(intRow).Item(0)
         EmployeeName = Employee.Rows(intRow).Item(1)
         DepartmentID = Employee.Rows(intRow).Item(2)


         'parameters to update
         daUpdate.UpdateCommand.Parameters.AddWithValue("@ParamPersonName", EmployeeId)
         daUpdate.UpdateCommand.Parameters.AddWithValue("@ParamDepartmentID", EmployeeName)

         'original parameters to the WHERE clause for optimisic concurrency
         daUpdate.UpdateCommand.Parameters.AddWithValue( _
         "@ParamOriginalPeopleID", Employee.Rows(intRow).Item("PeopleID", DataRowVersion.Original))

         daUpdate.UpdateCommand.Parameters.AddWithValue( _
         "@ParamOriginalPersonName", Employee.Rows(intRow).Item("PersonName", DataRowVersion.Original))

         daUpdate.UpdateCommand.Parameters.AddWithValue( _
         "@paramOriginalDepartmentID", Employee.Rows(intRow).Item("DepartmentID", DataRowVersion.Original))

         GetConn.Close()

      Catch ex As SqlException

         ' Throw New Exception("Problem occurred in EditEmployeeAllvalues routine while trying to update a record")
         MessageBox.Show(ex.ToString)

      End Try

   End Sub


##### BUTTONCLICK #############

 Private Sub BtnShowPerson_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnShowPerson.Click

      Dim param As String
      param = CmbSearch.Text
      TxtInfo.Text = param



      If (param <> "") Then

         PanelPerson.Enabled = True

         TxtID.DataBindings.Add("Text", UseSql.GetEmployee(param).Tables("People"), "PeopleID")
         Txtname.DataBindings.Add("Text", UseSql.GetEmployee(param).Tables("People"), "PersonName")
         TxtDept.DataBindings.Add("Text", UseSql.GetEmployee(param).Tables("People"), "DepartmentName")
         TextDeptID.DataBindings.Add("Text", UseSql.GetEmployee(param).Tables("People"), "DepartmentID")

         StorePersonName = Txtname.Text

      Else
         MessageBox.Show("Please choose a person from the combobox", "Person name required", MessageBoxButtons.OK)
         CmbSearch.Focus()

      End If


   End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
brilloxAuthor Commented:
I added the daUpdate.UpdateCommand.executenonscalar line

and now I get

---------------------------

---------------------------
System.Data.SqlClient.SqlException: Incorrect syntax near 'ProcUpdateConcAllValues'.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at WorkTest.Sql.EditEmployeeAllValues(DataTable Employee, Int32 intRow) in C:\work\WorkTest\WorkTest\Sql.vb:line 171
---------------------------
OK  
---------------------------
0
 
brilloxAuthor Commented:
sorry

the line added is
daUpdate.UpdateCommand.ExecuteNonQuery()
0
 
Jeff CertainCommented:
I usually use da.Update(dataTable).

This will handle all the hard stuff for you, and only update the added/deleted/modified rows.

Of course, you have to have the update command created first.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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