Solved

update method using stored procedure passing a datarow

Posted on 2006-11-09
7
313 Views
Last Modified: 2012-08-13
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
Comment
Question by:brillox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 

Author Comment

by:brillox
ID: 17905028
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
 

Author Comment

by:brillox
ID: 17905100
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
 

Author Comment

by:brillox
ID: 17905264
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
Independent Software Vendors: 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!

 

Author Comment

by:brillox
ID: 17905326
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
 

Author Comment

by:brillox
ID: 17905327
sorry

the line added is
daUpdate.UpdateCommand.ExecuteNonQuery()
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 500 total points
ID: 17921584
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question