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!
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!
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
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
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
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
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!
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
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
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
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
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?
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.Defau lt.GetByte s(val);
I reckon I'm getting this problem coz of this assignment... pls help
KK!
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.Defau
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
have you add the @RequestID parameter?
also where do you assign the @TS ?
Regards!
B..M
mmarinov