Solved

timestamp concurrency

Posted on 2006-12-01
8
392 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

734 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