Problem in using TimeStamp datatype (SQL Server) with C#

Hello Experts,

I'm developing a web application where I need to have concurrency control in updation. I was tyring to achieve this with the SQL Server build in datatype TIMESTAMP but for some reason I'm not able to acheive it with this.

I'm using this code but at no point of time I'm able to successfully update a record (even if there is only one usre who makes an update). TS is the timestamp colum in the table and I'm making a parameterized query to update this.

sql = "UPDATE Request SET Flag = 'A' WHERE RequestID = @RequestID AND TS = @TS";

myParams[1] = new SqlParameter("@Flag", SqlDbType.Char);
myParams[1].Value = mFlag.Trim();

myParams[2] = new SqlParameter("@TS", SqlDbType.Timestamp);
myParams[2].SourceVersion = DataRowVersion.Original;
myParams[2].SourceColumn = "TS";

' Prepare a command cmd and execute
cmd.ExecuteNonQuery();

But with this method, the update never succeeds at any point of time. Is there anything I'm missing or making wrong? Any other way to do it? Pls help me, am struck.

TIA,
KK!
LVL 4
skaykayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmarinovCommented:
Hi skaykay,

have you add the @RequestID parameter?
also where do you assign the @TS  ?

Regards!
B..M
mmarinov
0
hamoodCommented:
Use query like this

dim RequestID as String = "value1"
dim TS as String = "value2"
sql = "UPDATE Request SET Flag = 'A' WHERE RequestID = " & RequestID  &  " AND TS = " & TS
You do not need to create paramerters for parameterized query


Hamood
0
hamoodCommented:
it is vb.net code you have to convert it to C#

hamood
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

mmarinovCommented:
hamood,

actually this won't work because the sqltimestamp in .net is byte array
you have to create the timestamp as string manual

Regards!
B..M
mmarinov
0
skaykayAuthor Commented:
Hi mmarinov,

Sorry, I just gave a sample.. include the paramater to have RequestID also. But when that particular request ID record is updated by one user, no other user should be able to edit.

I hope I'm clear now with my question.

Pls help.

TIA,
KK!
0
mmarinovCommented:
skaykay,

this is true - that is the reason that you have to get records in some period - so you can update timestamp's values on the client
the meaning of the timestamp is to give you information if the records is modified - so if you don't get the record ( for example in the Page_Load ) you will not be able to update
you can do something like that
1. try to udpate
2. if the ts is different - popup a message that the user has been update from different user and the page should be refreshed

Regards!
B..M
mmarinov
0
skaykayAuthor Commented:
Yep, this should ideally happen when someother user has updated the same record after another user has retrieved it and trying to save it.. But it is happening for me when there is only one user who is trying to open and edit it. All the time (TS = @TS) fails and hence no time my update is succeeded...

0
mmarinovCommented:
skaykay,

have you tried
myParams[2] = new SqlParameter("@TS", SqlDbType.Timestamp);
myParams[2].Value = byte[] value
without the other 2 properties of the sqlparameter object

Regards!
B..M
mmarinov
0
skaykayAuthor Commented:
Sorry, I don't understand it completely. Instead of taking the original value from the table directly, you want me to try assigning it directly? Where do I assign the value?
0
skaykayAuthor Commented:
Hi,

I'm half crossed I guess now. In the query I will not be able to query TS = @TS to compare two binary values.. instead I should be using TSEQUAL function to compare 2 time stamps.

With this now my problem has reversed. At all instances my update succeeds (though 2 users modiy it simultaneously).

Can you help me out on how to store the TS value in byte[] on the form load and then pass it to the update function to check for the TS? I'm using this... am I making something wrong?

Byte[] TStamp;
TStamp = System.Text.Encoding.Default.GetBytes(val);

I reckon I'm getting this problem coz of this assignment... pls help

KK!
0
mmarinovCommented:
the sqltimestamp type is equal to the byte[] in c#
so when you pass value to the parameter

Parameters[2].Value = put here the byte[] array

when you receive the records from db

byte[] TS = (byte[])Parameter[2].Value;

B..M
mmarinov
0
skaykayAuthor Commented:
It doesn't allow me to convert a string to Byte[] by directly typecasting... It says, Cannot convert string to Byte[]. That is the reason I went for that method.
0
mmarinovCommented:
why string? this means that Parameter[2].Value returns you a string value - can you post the declaration of this second parameter
here how i use it :
parameters.Param_TS = (System.Byte[])sqlCommand.Parameters["@TS"].Value;

B..M
mmarinov
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.