|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Concurrency Checking - SQL Server Rowversion - Stored Procedures ASP.NET CommandRowVersion (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("ConcurrencyValue") 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 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) Show quote "Bari Allen" <b***@sos.REMOVETHIS.state.ga.us> wrote in message news:ON1BAGI7FHA.3752@tk2msftngp13.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("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("ConcurrencyValue") > 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" <b***@sos.REMOVETHIS.state.ga.us> wrote in message Here is some code I grabbed that is using RowVersion and converting back and news:ON1BAGI7FHA.3752@tk2msftngp13.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" forth... Dim rowVersion(8) 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 Show quote "Bari Allen" <b***@sos.REMOVETHIS.state.ga.us> schrieb im Newsbeitrag news:ON1BAGI7FHA.3752@tk2msftngp13.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("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("ConcurrencyValue") > 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! Show quote "Bari Allen" <b***@sos.REMOVETHIS.state.ga.us> wrote in message news:ON1BAGI7FHA.3752@tk2msftngp13.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("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("ConcurrencyValue") > 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 Show quote "msnews.microsoft.com" <b***@sos.REMOVETHIS.state.ga.us> schrieb im Newsbeitrag news:%23oCoPrY7FHA.744@TK2MSFTNGP10.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" <b***@sos.REMOVETHIS.state.ga.us> wrote in message > news:ON1BAGI7FHA.3752@tk2msftngp13.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("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("ConcurrencyValue") >> 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 >> >> > >  |
|||||||||||||||||||||||
Other interesting topics