Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-11-08
13
Medium Priority
?
3,348 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:skaykay
  • 6
  • 5
  • 2
13 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 12522141
Hi skaykay,

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

Regards!
B..M
mmarinov
0
 
LVL 4

Expert Comment

by:hamood
ID: 12522174
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
 
LVL 4

Expert Comment

by:hamood
ID: 12522180
it is vb.net code you have to convert it to C#

hamood
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Expert Comment

by:mmarinov
ID: 12522216
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
 
LVL 4

Author Comment

by:skaykay
ID: 12522284
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
 
LVL 28

Expert Comment

by:mmarinov
ID: 12522294
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
 
LVL 4

Author Comment

by:skaykay
ID: 12522311
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
 
LVL 28

Expert Comment

by:mmarinov
ID: 12522320
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
 
LVL 4

Author Comment

by:skaykay
ID: 12522351
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
 
LVL 4

Author Comment

by:skaykay
ID: 12522725
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
 
LVL 28

Expert Comment

by:mmarinov
ID: 12522919
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
 
LVL 4

Author Comment

by:skaykay
ID: 12530169
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
 
LVL 28

Accepted Solution

by:
mmarinov earned 300 total points
ID: 12532053
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month10 days, 13 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question