Data Access App. Block - Timestamp

Topics: Data Access Application Block
Sep 10, 2008 at 4:59 AM
I have a FormView which gets data from an ObjectDataSource, and I am struggling to delete a record via the Data Access Application Block (latest version).

<asp:ObjectDataSource ID="odsRoomsDetails" runat="server" OldValuesParameterFormatString="original_{0}"
    TypeName="HotelBooking.DataAccess.RoomsDB"
    SelectMethod="GetRoom"
    DeleteMethod="Delete"
    UpdateMethod="Save"
    EnableViewState="False">


The ObjectDataSource has the following delete parameters:

<DeleteParameters>
    <asp:ControlParameter ControlID="fvRoomsDetails" Name="RoomID" Type="Int32" />
    <asp:ControlParameter ControlID="fvRoomsDetails" Name="ChangeStamp" Type="String" />
</DeleteParameters>


This function is supposed to delete the record in the Rooms table but it does not.

Public Function Delete(ByVal RoomID As Int32, _
                         ByVal changeStamp As String, _
                         ByVal original_RoomID As Int32) As Boolean
    Dim enc As New System.Text.UTF8Encoding
    Dim bytes As Byte() = enc.GetBytes(changeStamp)
    Dim db As Database = DatabaseFactory.CreateDatabase()
    Dim cmd As DbCommand = db.GetStoredProcCommand("Rooms_Del", RoomID, bytes)

    db.ExecuteNonQuery(cmd)
End Function


The idea is that if another user has changed the record then the delete is not allowed. Here is the stored procedure (SQL Server).

ALTER PROCEDURE [dbo].[Rooms_Del]
(
 @RoomID int,
 @ChangeStamp timestamp
)
AS
SET NOCOUNT ON

DELETE FROM Rooms
WHERE RoomID = @RoomID
 AND ChangeStamp = @ChangeStamp

Can anyone tell me if I am handling the timestamp or parameters incorrectly? If I take out the timestamp parts, then the record is deleted fine. Somehow it seems that the timestamps are not matching when the stored procedure tries to carry out the delete.

Any help would be much appreciated.


Ed Sansbury

Sep 10, 2008 at 6:05 AM
Edited Sep 10, 2008 at 7:40 AM
I have since realised that I am passing the wrong value for ChangeStamp into the Delete function.
 
This line is grabbing the RoomID instead of the ChangeStamp field.
<asp:ControlParameter ControlID="fvRoomsDetails" Name="ChangeStamp" Type="String" />

If anyone knows how to set this parameter with the ChangeStamp that would be great. Should I use '<asp:Parameter' and set this programmatically?

***** Update *****

Ok I put the timestamp in a literal control and set an <asp:Parameter in the ObjectDataSource deleting event, by using FindControl. Still this did not work. Any ideas why? Is it possible to read in a timestamp stored as an Object, convert it to a string, and then convert it back for comparison with a timestamp in a SQL Server stored procedure?

***** Update *****

I thought this custom parameter way would work

Public Function Delete(ByVal room As RoomInfo) As Boolean
    Dim db As Database = DatabaseFactory.CreateDatabase()
    Dim cmd As DbCommand = db.GetStoredProcCommand("Rooms_Del", room.RoomID, room.ChangeStamp)

    db.ExecuteNonQuery(cmd)
End Function


ChangeStamp is declared as an object like so
Private _changeStamp As Object = String.Empty

Now I get the error message, "Failed to convert parameter value from a String to a Byte[]."

Help?!?



Thanks


Ed Sansbury
Sep 10, 2008 at 2:57 PM

Hi Ed,

The .NET type that maps to a timestamp is byte[]; that's what you should be storing and sending. Is the _changeStamp field ever set, other than in the initialization? What is it set to? What's the type of room.ChangeStamp?

Fernando

Sep 10, 2008 at 6:54 PM

Hi Fernando

I have setup the ObjectDataSource like so, and have removed the DeleteParameters.

<asp:ObjectDataSource ID="odsRoomsDetails" runat="server" OldValuesParameterFormatString="original_{0}"
    TypeName="HotelBooking.DataAccess.RoomsDB"
    SelectMethod="GetRoom"
    DeleteMethod="Delete"
    UpdateMethod="Save"
    EnableViewState="False"
    DataObjectTypeName="HotelBooking.Info.RoomInfo">
    <SelectParameters>
      <asp:Parameter Name="intRoomID" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>


I don't think the room As RoomInfo parameter is being passed, as I threw an exception like 'throw new exception("RoomNo: " & room.RoomNo.ToString)' and '0' was returned for RoomNo (I chose room 140).

  Public Function Delete(ByVal room As RoomInfo) As Boolean
    Dim db As Database = DatabaseFactory.CreateDatabase()
    Dim cmd As DbCommand = db.GetStoredProcCommand("Rooms_Del", room.RoomID, room.ChangeStamp)

    db.ExecuteNonQuery(cmd)
  End Function


I have a GetRoom function that returns the room and displays it in a FormView (timestamp is not displayed). I have just changed the conversion of the timestamp to Byte().

Public Function GetRoom(ByVal intRoomID As Int32) As List(Of RoomInfo)
  Dim db As Database = DatabaseFactory.CreateDatabase
  Dim objRooms As New List(Of RoomInfo)()

  Using dr As IDataReader = db.ExecuteReader("Rooms_Sel_ByID", intRoomID)
    While dr.Read
      Dim room As New RoomInfo

      room.RoomID = DirectCast(IIf(IsDBNull(dr("RoomID")), Nothing, dr("RoomID")), Int32)
      room.RoomNo = DirectCast(IIf(IsDBNull(dr("RoomNo")), Nothing, dr("RoomNo")), Int32)
      ...
      room.ChangeStamp = CType(dr("ChangeStamp"), Byte())

      objRooms.Add(room)
    End While
  End Using

  Return objRooms
End Function


RoomInfo is as follows. Again I have the ChangeStamp as byte() now as opposed to Object.

<Serializable()> _
Public Class RoomInfo

#Region "Private member variables"

  Private _roomID As Int32 = 0
  Private _roomNo As Int32 = 0
  ...
  Private _additionalNotes As String = String.Empty
  Private _roomStatus As Byte = 0
  Private _changeStamp As Byte()
  ...
  Public Property ChangeStamp() As Byte()
    Get
      Return _changeStamp
    End Get
    Set(ByVal value As Byte())
      _changeStamp = value
    End Set
  End Property

Any ideas why the Delete function is not being passed the RoomInfo object? I guess that is why the room is not being deleted.

Thanks very much for your help so far.


Ed




Sep 11, 2008 at 3:21 PM

Hi,

This is really an ASP.NET question. You're looking for ConflictDetection="CompareAllValues".

Regards,
Fernando

 

 

Sep 11, 2008 at 4:12 PM
ConflictDetection="CompareAllValues" seems to make a difference to the object creation. If anyone is struggling with the same thing, the only way I could get this to work was to add the ChangeStamp to DataKeyNames like so. I guess other fields could be added to DataKeyNames for the update process.

<asp:FormView ID="fvRoomsDetails" runat="server" DataKeyNames="RoomID,ChangeStamp"

Thanks again Fernando.

Regards.


Ed