Concurrency Checking - SQL Server Rowversion - Stored Procedures ASP.NET Command

I'm trying to test for concurrency, using a SQL Stored Procedure on a
RowVersion (timestamp) Field. The vb code I'm using is as follows

Dim cmd As New SqlCommand("ConcurrencyCheck", cn)
Dim intID as integer = MyDataset.Tables("MyTable").Rows(0).Item("ID")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add("@ID", intID)

Dim concurParam As New SqlParameter
concurParam.ParameterName = "@Concurrency"
concurParam.Value =
MyDataset.Tables("MyTable").Rows(0).Item("Concurre ncyValue")
concurParam.SqlDbType = SqlDbType.Binary
cmd.Parameters.Add(concurParam)

cn.Open()

If cmd.ExecuteScalar() > 0 Then
Record Found ...
Else
Record not found ...
End If

cmd.Dispose()
cn.Close()
cn.Dispose()

Prior to submitting the form, I run a different stored procedure which
populates the "MyDataset" from the same row using:
SELECT * FROM MyTable WHERE ID = @ID

The code in the stored procedure that submits the form is:
SELECT Count(*) FROM MyTable WHERE ID = @ID AND ConcurrencyValue =
@Concurrency

How do I convert the originally retrieved data (RowVersion) back to a true
binary in ASP.NET to send it back to SQL? It currently is storing the field
as an array, for some reason. Thus, when I test this procedure and now
(without a doubt) no one has modified the record since, it ALWAYS returns
"record not found"

Thanks in advance for any help on this.

Bari

Bari Allen

a sql timestamp should come across as an 8 byte binary array. be sure to
specify size of 8. sql wants an 8 byte literal.

SELECT Count(*) FROM MyTable
WHERE ID = @ID
AND ConcurrencyValue = 0x000000000000906A

-- bruce (sqlwork.com)


"Bari Allen" <> wrote in message
news:...
> I'm trying to test for concurrency, using a SQL Stored Procedure on a
> RowVersion (timestamp) Field. The vb code I'm using is as follows
>
> Dim cmd As New SqlCommand("ConcurrencyCheck", cn)
> Dim intID as integer = MyDataset.Tables("MyTable").Rows(0).Item("ID")
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
> cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
> cmd.Parameters.Add("@ID", intID)
>
> Dim concurParam As New SqlParameter
> concurParam.ParameterName = "@Concurrency"
> concurParam.Value =
> MyDataset.Tables("MyTable").Rows(0).Item("Concurre ncyValue")
> concurParam.SqlDbType = SqlDbType.Binary
> cmd.Parameters.Add(concurParam)
>
> cn.Open()
>
> If cmd.ExecuteScalar() > 0 Then
> Record Found ...
> Else
> Record not found ...
> End If
>
> cmd.Dispose()
> cn.Close()
> cn.Dispose()
>
> Prior to submitting the form, I run a different stored procedure which
> populates the "MyDataset" from the same row using:
> SELECT * FROM MyTable WHERE ID = @ID
>
> The code in the stored procedure that submits the form is:
> SELECT Count(*) FROM MyTable WHERE ID = @ID AND ConcurrencyValue =
> @Concurrency
>
> How do I convert the originally retrieved data (RowVersion) back to a true
> binary in ASP.NET to send it back to SQL? It currently is storing the
> field
> as an array, for some reason. Thus, when I test this procedure and now
> (without a doubt) no one has modified the record since, it ALWAYS returns
> "record not found"
>
> Thanks in advance for any help on this.
>
> Bari
>
>

"Bari Allen" <> wrote in message
news:...
> How do I convert the originally retrieved data (RowVersion) back to a true
> binary in ASP.NET to send it back to SQL? It currently is storing the
> field
> as an array, for some reason. Thus, when I test this procedure and now
> (without a doubt) no one has modified the record since, it ALWAYS returns
> "record not found"

Here is some code I grabbed that is using RowVersion and converting back and
forth...

Dim rowVersion( As Byte
....
With cmd.Parameters.Add("@RowVersion", SqlDbType.Timestamp)
.Value = rowVersion
.Direction = ParameterDirection.InputOutput
End With
....
rowVersion = CType(cmd.Parameters("@RowVersion").Value, Byte())

Greg

Bari,

what I do is that I convert the TimeStamp value in a BigInt value which I
can easily pass back and forth to my stored proc. An example would look like
the following:

CREATE PROCEDURE dbo.spTest
(
@OIDTest uniqueidentifier
, @SomeValue as int

, @VersionOld bigint = 0 output
)

AS

declare @Error int
declare @rowcount int

update tblTest set

SomeValue = @SomeValue

where OIDTest = @OIDTest and TimeStp = @VersionOld
select @error = @@error, @rowcount = @@rowcount

if @error > 0
begin
-- an error occurred
goto FunctionErrorWrite
end

if (@rowcount = 1)
begin
-- everything is fine

-- get a new timeStamp
select @VersionOld = convert(bigint, TimeStp)
from tblTest
where OIDTest = @OIDTest
-- Transaktion durchführen
goto FunctionExit
end
else
begin
goto FunctionErrorWrite
end


/* SET NOCOUNT ON */
FunctionExit:
RETURN 0

FunctionErrorUnknown:
RETURN -1

FunctionErrorWrite:
RETURN -2

FunctionErrorWrongID:
RETURN -3

FunctionErrorInterimChanged:
RETURN -4

Does this help you?

Regards

Daniel Walzenbach




"Bari Allen" <> schrieb im Newsbeitrag
news:...
> I'm trying to test for concurrency, using a SQL Stored Procedure on a
> RowVersion (timestamp) Field. The vb code I'm using is as follows
>
> Dim cmd As New SqlCommand("ConcurrencyCheck", cn)
> Dim intID as integer = MyDataset.Tables("MyTable").Rows(0).Item("ID")
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
> cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
> cmd.Parameters.Add("@ID", intID)
>
> Dim concurParam As New SqlParameter
> concurParam.ParameterName = "@Concurrency"
> concurParam.Value =
> MyDataset.Tables("MyTable").Rows(0).Item("Concurre ncyValue")
> concurParam.SqlDbType = SqlDbType.Binary
> cmd.Parameters.Add(concurParam)
>
> cn.Open()
>
> If cmd.ExecuteScalar() > 0 Then
> Record Found ...
> Else
> Record not found ...
> End If
>
> cmd.Dispose()
> cn.Close()
> cn.Dispose()
>
> Prior to submitting the form, I run a different stored procedure which
> populates the "MyDataset" from the same row using:
> SELECT * FROM MyTable WHERE ID = @ID
>
> The code in the stored procedure that submits the form is:
> SELECT Count(*) FROM MyTable WHERE ID = @ID AND ConcurrencyValue =
> @Concurrency
>
> How do I convert the originally retrieved data (RowVersion) back to a true
> binary in ASP.NET to send it back to SQL? It currently is storing the
> field
> as an array, for some reason. Thus, when I test this procedure and now
> (without a doubt) no one has modified the record since, it ALWAYS returns
> "record not found"
>
> Thanks in advance for any help on this.
>
> Bari
>
>

Thank you to everyone that replied.

When I tried to convert a value from a field in the dataset's table to byte,
I got an error, because the implicit conversion wasn't allowed from an array
(the default conversion sent back from the timestamp column).

Thus, I went with Daniel's suggestion to convert the value to a BigInt
within the retrieval stored procedure's select statement. That way, I can
store the entire recordset in a dataset, when it is returned. Then, I send
back the BigInt value to the stored procedure that tests for concurrency
(for updates). This worked great.

Thank you, Daniel!

"Bari Allen" <> wrote in message
news:...
> I'm trying to test for concurrency, using a SQL Stored Procedure on a
> RowVersion (timestamp) Field. The vb code I'm using is as follows
>
> Dim cmd As New SqlCommand("ConcurrencyCheck", cn)
> Dim intID as integer = MyDataset.Tables("MyTable").Rows(0).Item("ID")
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
> cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
> cmd.Parameters.Add("@ID", intID)
>
> Dim concurParam As New SqlParameter
> concurParam.ParameterName = "@Concurrency"
> concurParam.Value =
> MyDataset.Tables("MyTable").Rows(0).Item("Concurre ncyValue")
> concurParam.SqlDbType = SqlDbType.Binary
> cmd.Parameters.Add(concurParam)
>
> cn.Open()
>
> If cmd.ExecuteScalar() > 0 Then
> Record Found ...
> Else
> Record not found ...
> End If
>
> cmd.Dispose()
> cn.Close()
> cn.Dispose()
>
> Prior to submitting the form, I run a different stored procedure which
> populates the "MyDataset" from the same row using:
> SELECT * FROM MyTable WHERE ID = @ID
>
> The code in the stored procedure that submits the form is:
> SELECT Count(*) FROM MyTable WHERE ID = @ID AND ConcurrencyValue =
> @Concurrency
>
> How do I convert the originally retrieved data (RowVersion) back to a true
> binary in ASP.NET to send it back to SQL? It currently is storing the
field
> as an array, for some reason. Thus, when I test this procedure and now
> (without a doubt) no one has modified the record since, it ALWAYS returns
> "record not found"
>
> Thanks in advance for any help on this.
>
> Bari
>
>

Good to hear You are welcome!

Cheers,

Daniel Walzenbach

"msnews.microsoft.com" <> schrieb im
Newsbeitrag news:%...
> Thank you to everyone that replied.
>
> When I tried to convert a value from a field in the dataset's table to
> byte,
> I got an error, because the implicit conversion wasn't allowed from an
> array
> (the default conversion sent back from the timestamp column).
>
> Thus, I went with Daniel's suggestion to convert the value to a BigInt
> within the retrieval stored procedure's select statement. That way, I can
> store the entire recordset in a dataset, when it is returned. Then, I
> send
> back the BigInt value to the stored procedure that tests for concurrency
> (for updates). This worked great.
>
> Thank you, Daniel!
>
> "Bari Allen" <> wrote in message
> news:...
>> I'm trying to test for concurrency, using a SQL Stored Procedure on a
>> RowVersion (timestamp) Field. The vb code I'm using is as follows
>>
>> Dim cmd As New SqlCommand("ConcurrencyCheck", cn)
>> Dim intID as integer = MyDataset.Tables("MyTable").Rows(0).Item("ID")
>> cmd.CommandType = CommandType.StoredProcedure
>> cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
>> cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
>> cmd.Parameters.Add("@ID", intID)
>>
>> Dim concurParam As New SqlParameter
>> concurParam.ParameterName = "@Concurrency"
>> concurParam.Value =
>> MyDataset.Tables("MyTable").Rows(0).Item("Concurre ncyValue")
>> concurParam.SqlDbType = SqlDbType.Binary
>> cmd.Parameters.Add(concurParam)
>>
>> cn.Open()
>>
>> If cmd.ExecuteScalar() > 0 Then
>> Record Found ...
>> Else
>> Record not found ...
>> End If
>>
>> cmd.Dispose()
>> cn.Close()
>> cn.Dispose()
>>
>> Prior to submitting the form, I run a different stored procedure which
>> populates the "MyDataset" from the same row using:
>> SELECT * FROM MyTable WHERE ID = @ID
>>
>> The code in the stored procedure that submits the form is:
>> SELECT Count(*) FROM MyTable WHERE ID = @ID AND ConcurrencyValue =
>> @Concurrency
>>
>> How do I convert the originally retrieved data (RowVersion) back to a
>> true
>> binary in ASP.NET to send it back to SQL? It currently is storing the
> field
>> as an array, for some reason. Thus, when I test this procedure and now
>> (without a doubt) no one has modified the record since, it ALWAYS returns
>> "record not found"
>>
>> Thanks in advance for any help on this.
>>
>> Bari
>>
>>
>
>