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

[Bari Allen]

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("Con currencyCheck", cn)
Dim intID as integer = MyDataset.Table s("MyTable").Ro ws(0).Item("ID" )
cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. Add("@ReturnVal ue", SqlDbType.Int)
cmd.Parameters( 0).Direction = ParameterDirect ion.ReturnValue
cmd.Parameters. Add("@ID", intID)

Dim concurParam As New SqlParameter
concurParam.Par ameterName = "@Concurren cy"
concurParam.Val ue =
MyDataset.Table s("MyTable").Ro ws(0).Item("Con currencyValue")
concurParam.Sql DbType = SqlDbType.Binar y
cmd.Parameters. Add(concurParam )

cn.Open()

If cmd.ExecuteScal ar() > 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 ConcurrencyValu e =
@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

[Bruce Barker]

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 ConcurrencyValu e = 0x0000000000009 06A

-- bruce (sqlwork.com)
"Bari Allen" <ba**@sos.REMOV ETHIS.state.ga. us> wrote in message
news:ON******** ******@tk2msftn gp13.phx.gbl...

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("Con currencyCheck", cn)
Dim intID as integer = MyDataset.Table s("MyTable").Ro ws(0).Item("ID" )
cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. Add("@ReturnVal ue", SqlDbType.Int)
cmd.Parameters( 0).Direction = ParameterDirect ion.ReturnValue
cmd.Parameters. Add("@ID", intID)

Dim concurParam As New SqlParameter
concurParam.Par ameterName = "@Concurren cy"
concurParam.Val ue =
MyDataset.Table s("MyTable").Ro ws(0).Item("Con currencyValue")
concurParam.Sql DbType = SqlDbType.Binar y
cmd.Parameters. Add(concurParam )

cn.Open()

If cmd.ExecuteScal ar() > 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 ConcurrencyValu e =
@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

[Greg Burns]

"Bari Allen" <ba**@sos.REMOV ETHIS.state.ga. us> wrote in message
news:ON******** ******@tk2msftn gp13.phx.gbl...

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(8) As Byte
....
With cmd.Parameters. Add("@RowVersio n", SqlDbType.Times tamp)
.Value = rowVersion
.Direction = ParameterDirect ion.InputOutput
End With
....
rowVersion = CType(cmd.Param eters("@RowVers ion").Value, Byte())

Greg

[Daniel Walzenbach]

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 uniqueidentifie r
, @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 FunctionErrorWr ite
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 FunctionErrorWr ite
end
/* SET NOCOUNT ON */
FunctionExit:
RETURN 0

FunctionErrorUn known:
RETURN -1

FunctionErrorWr ite:
RETURN -2

FunctionErrorWr ongID:
RETURN -3

FunctionErrorIn terimChanged:
RETURN -4

Does this help you?

Regards

Daniel Walzenbach


"Bari Allen" <ba**@sos.REMOV ETHIS.state.ga. us> schrieb im Newsbeitrag
news:ON******** ******@tk2msftn gp13.phx.gbl...

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("Con currencyCheck", cn)
Dim intID as integer = MyDataset.Table s("MyTable").Ro ws(0).Item("ID" )
cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. Add("@ReturnVal ue", SqlDbType.Int)
cmd.Parameters( 0).Direction = ParameterDirect ion.ReturnValue
cmd.Parameters. Add("@ID", intID)

Dim concurParam As New SqlParameter
concurParam.Par ameterName = "@Concurren cy"
concurParam.Val ue =
MyDataset.Table s("MyTable").Ro ws(0).Item("Con currencyValue")
concurParam.Sql DbType = SqlDbType.Binar y
cmd.Parameters. Add(concurParam )

cn.Open()

If cmd.ExecuteScal ar() > 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 ConcurrencyValu e =
@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

[msnews.microsoft.com]

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" <ba**@sos.REMOV ETHIS.state.ga. us> wrote in message
news:ON******** ******@tk2msftn gp13.phx.gbl...

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("Con currencyCheck", cn)
Dim intID as integer = MyDataset.Table s("MyTable").Ro ws(0).Item("ID" )
cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. Add("@ReturnVal ue", SqlDbType.Int)
cmd.Parameters( 0).Direction = ParameterDirect ion.ReturnValue
cmd.Parameters. Add("@ID", intID)

Dim concurParam As New SqlParameter
concurParam.Par ameterName = "@Concurren cy"
concurParam.Val ue =
MyDataset.Table s("MyTable").Ro ws(0).Item("Con currencyValue")
concurParam.Sql DbType = SqlDbType.Binar y
cmd.Parameters. Add(concurParam )

cn.Open()

If cmd.ExecuteScal ar() > 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 ConcurrencyValu e =
@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

[Daniel Walzenbach]

Good to hear :-) You are welcome!

Cheers,

Daniel Walzenbach

"msnews.microso ft.com" <ba**@sos.REMOV ETHIS.state.ga. us> schrieb im
Newsbeitrag news:%2******** *******@TK2MSFT NGP10.phx.gbl.. .

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" <ba**@sos.REMOV ETHIS.state.ga. us> wrote in message
news:ON******** ******@tk2msftn gp13.phx.gbl...

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("Con currencyCheck", cn)
Dim intID as integer = MyDataset.Table s("MyTable").Ro ws(0).Item("ID" )
cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. Add("@ReturnVal ue", SqlDbType.Int)
cmd.Parameters( 0).Direction = ParameterDirect ion.ReturnValue
cmd.Parameters. Add("@ID", intID)

Dim concurParam As New SqlParameter
concurParam.Par ameterName = "@Concurren cy"
concurParam.Val ue =
MyDataset.Table s("MyTable").Ro ws(0).Item("Con currencyValue")
concurParam.Sql DbType = SqlDbType.Binar y
cmd.Parameters. Add(concurParam )

cn.Open()

If cmd.ExecuteScal ar() > 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 ConcurrencyValu e =
@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