Solved

update method using stored procedure passing a datarow

Posted on 2006-11-09
7
280 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now