Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

timestamp concurrency

Posted on 2006-12-01
8
Medium Priority
?
407 Views
Last Modified: 2010-05-18
I am unable to retrieve and pass back a timestamp to my stored procedure for an update method.

Please help..  


STORED PROCEDURE CODE

ALTER PROCEDURE [dbo].[ProcUpdateConcTimestamp]
      -- Add the parameters for the stored procedure here
      @ParamPeopleID int,      
      @ParamPersonName varchar(50),
      @ParamDepartmentID int,
      @paramOriginalTimestamp TimeStamp
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,
            DepartmentID = @ParamDepartmentID

      WHERE TimeStampCol = @paramOriginalTimestamp and
              PeopleID = @ParamPeopleID

END


#####################################################

CODE FROM WHICH i POPULATE A DUMMY STRONGLY TYPED DATASET

 Private Function LoadDummyDs() As People
      Dim DumDs As New People
      Dim OriginalDs As DataSet
      Dim TotalRows As Integer
      Dim PeopleRow As People.PeopleRow

      Dim PeopleID As Int32
      Dim PersonName As String
      Dim DepartmentId As Integer
      Dim TimeStamp(8) As Byte




      Try

         'Load original database for concurrency comaprison data
         OriginalDs = UseSql.GetPerson(PersonNameForUpdate)

         'get the total number of rows
         TotalRows = OriginalDs.Tables("People").Rows.Count

         'create a new row into the strongly typed dataset
         PeopleRow = DumDs.Tables(0).NewRow

         'cast value
         PeopleID = CType(TxtID.Text, Int32)

         'get current updated values from user
         PersonName = Txtname.Text
         DepartmentId = CmbDept.SelectedValue

         TimeStamp = OriginalDs.Tables(0).Rows(0).Item("TimeStampCol")


         'build a new row with updated values and original values
         PeopleRow("PeopleID") = PeopleID
         PeopleRow("DepartmentID") = DepartmentId
         PeopleRow("PersonName") = PersonName
         PeopleRow("CurrentTimeStampCol") = TimeStamp

         PeopleRow("OriginalPersonName") = OriginalDs.Tables("People").Rows(TotalRows - 1).Item("PersonName")
         PeopleRow("OriginalPersonID") = OriginalDs.Tables("People").Rows(TotalRows - 1).Item("PeopleID")
         PeopleRow("OriginalDepartmentID") = OriginalDs.Tables("People").Rows(TotalRows - 1).Item("DepartmentID")

         'add row to datatable
         DumDs.Tables("People").Rows.Add(PeopleRow)

         Return DumDs

      Catch ex As SqlException

         Throw New Exception(ex.Message)
      End Try

   End Function


##################################################


Function from which I call the stored procedure

Public Sub EditPersonsTS(ByVal ds As People)

      Dim PersonName As String
      Dim DepartmentID As Int32
      Dim PeopleID As Int32
      Dim TimeStamp(8) As Byte



      Dim RecordsAdded As Integer = 0
      Dim Command As SqlCommand
      Dim NumRows As Integer


      Try
         NumRows = ds.Tables("People").Rows.Count

         PeopleID = ds._People(0)("PeopleID", DataRowVersion.Current)
         PersonName = ds._People(0)("PersonName", DataRowVersion.Current)
         DepartmentID = ds._People(0)("DepartmentID", DataRowVersion.Current)


         Command = New SqlCommand()
         Command.Connection = GetConn()
         Command.CommandType = CommandType.StoredProcedure
         Command.CommandText = "ProcUpdateConcTimestamp"

         OpenConn()

         ' Add updated parameters
         Command.Parameters.AddWithValue("@ParamPeopleID", PeopleID)
         Command.Parameters.AddWithValue("@ParamPersonName", PersonName)
         Command.Parameters.AddWithValue("@ParamDepartmentID", DepartmentID)

         'Compare with timestamp

         Command.Parameters.Add("@paramOriginalTimestamp", SqlDbType.Timestamp, 8).Value = _
         ds._People(0)("CurrentTimeStamp", DataRowVersion.Current)

         RecordsAdded = Command.ExecuteNonQuery()

         CloseConn()

         If RecordsAdded > 0 Then

            CustomMessage = "A total of....   " & RecordsAdded & " has been edited" & NewLine
            CustomMessage += "..................."

            Validate = True

         Else
           
            Throw New ApplicationException("Another user has changed this record. " & _
                                                        "Your update was cancelled.")
            Validate = False

         End If

      Catch ex As SqlException
         CustomMessage = "Please send an email to the developer at brilliante@rsktech.com" & NewLine
         CustomMessage += ex.ToString
      End Try


   End Sub

##############################

The only problem is teh TIMESTAMP !  
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
8 Comments
 
LVL 6

Expert Comment

by:riyazthad
ID: 18054663
Where ever you use TimeStamp , try to change to  SqlDbType.VarBinary of size 8

for ex

Command.Parameters.Add("@paramOriginalTimestamp", SqlDbType.VarBinary, 8).Value = _
         ds._People(0)("CurrentTimeStamp", DataRowVersion.Current)

0
 

Author Comment

by:brillox
ID: 18055121
but SqlDbType.VarBinary  is only accepted within the dataAdapter add property

in your opinion is the rest of the code (regarding timestamp) ok ?
0
 

Author Comment

by:brillox
ID: 18055190
I Now have this

'Compare with timestamp
            Command.Parameters.Add("@paramTimestampCol", SqlDbType.VarBinary, 8).Value = _
                     ds._People(0)("CurrentTimeStamp", DataRowVersion.Current)


            RecordsAdded = Command.ExecuteNonQuery()

No exceptions, but recordsAdded has a value of -1 and no changes are made to the database record
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: 18055251
But I think that I missunderstud concurrency using timestamp...

Now the WHERE part of the sp is

WHERE TimeStampCol = @ParamTimeSTampCol And PeopleID = @ParamPeopleID

but TimeStampCol will never be the same of @ParamTimeSTampCol  because when I load the database I guess the timestamp change
0
 

Author Comment

by:brillox
ID: 18055337
This below is part os the strongly typed ds into which the timestamp is laoded and then after the dataset is modified get back to the database via th stored procedure


              <xs:element name="CurrentTimeStamp" msprop:Generator_UserColumnName="CurrentTimeStamp" msprop:Generator_ColumnVarNameInTable="columnCurrentTimeStamp" msprop:Generator_ColumnPropNameInRow="CurrentTimeStamp" msprop:Generator_ColumnPropNameInTable="CurrentTimeStampColumn" type="xs:unsignedByte" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
    <xs:unique name="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//mstns:People" />
      <xs:field xpath="mstns:PeopleID" />
    </xs:unique>
  </xs:element>
</xs:schema>
0
 

Author Comment

by:brillox
ID: 18057391
I found the solution.....

Basically when I created the strongly datatyped dataset in Visual Studio 2005 by dragging the "people" table from the server explorer, VS gived the type of System.byte to my TimeStamp. In the property column for the timestamp there was no type of System.byte()

Is since this morning that I am trying to cast, recast, delete, recode etc.. now, at 11:25 UK time I get so pist off that I tried to add () to the SYstem.byte type in VS.

guess what..... VS accepted the change in the property and my timestamp it now works fine. Obviously has been retrieved and passed again as an array of bytes

Timestamp() as byte

PLease if any of you think to a better solutiom, give me a code sample and I  will be more than happy to accept it and give points. If within 2 days, no asnwer have been given, I will ask to close this question
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 18221886
PAQd, 500 points refunded.

DarthMod
CS Moderator
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

688 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