Solved

timestamp concurrency

Posted on 2006-12-01
8
358 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
  • 5
8 Comments
 
LVL 6

Expert Comment

by:riyazthad
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:brillox
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
PAQd, 500 points refunded.

DarthMod
CS Moderator
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Zoom web page in asp.net 2 34
Performance enhancement 39 25
how to add field in my script 2 24
Change data in datatable 8 15
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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 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

16 Experts available now in Live!

Get 1:1 Help Now