Solved

update method using stored procedure passing a datarow

Posted on 2006-11-09
7
286 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

13 Experts available now in Live!

Get 1:1 Help Now