Solved

update method using stored procedure passing a datarow

Posted on 2006-11-09
7
273 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 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

18 Experts available now in Live!

Get 1:1 Help Now