Link to home
Start Free TrialLog in
Avatar of skaykay
skaykay

asked on

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!
Avatar of mmarinov
mmarinov

Hi skaykay,

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

Regards!
B..M
mmarinov
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
it is vb.net code you have to convert it to C#

hamood
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
Avatar of skaykay

ASKER

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!
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
Avatar of skaykay

ASKER

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...

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
Avatar of skaykay

ASKER

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?
Avatar of skaykay

ASKER

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!
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
Avatar of skaykay

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of mmarinov
mmarinov

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial