0

i need to update (Id+1) from an sql table and in same query to select that id.

i'm using code below but i'm afraid that if more than one client pc's run the same query then i will have conflict and i will take wrong id

SqlCommand cmd2 = new SqlCommand("update TrnDocuments set TrnNumber+=1 ;select (TrnNumber)as TrnNum from TrnDocuments ", con);
SqlDataReader read2 = cmd2.ExecuteReader();
dim
  • 37
  • 1
  • 6
  • So is my query right? I think that in some cases if query will run from two different pcs i will have problem – dim Mar 19 '18 at 19:25
  • 1. Your statements are not predicated by a where clause. All records will be updated, and you will also be returning a recordset containing all TrnNumber 2. Normally fields would be wrapped in [brackets] and not (parenthesis) And yes, there is nothing preventing the data from being updated multiple times by multiple people and/or instances. – Mad Myche Mar 19 '18 at 19:28
  • It is the only row in table. – dim Mar 19 '18 at 19:30
  • 2
    Yes you will have problems with this approach. The concurrency issues can get really ugly, far worse than the simple issue you mentioned. Again...why not use an identity? Or a sequence if you are on 2012+ – Sean Lange Mar 19 '18 at 19:34
  • Can identy to be used with update? Which is the differnece? Cause i make it manually. Can you give an example which is the best way just for a simple approch?UPDATE-SELECT (UPDATED ID) – dim Mar 19 '18 at 19:38
  • What ever it is that you are tying to do I am confident there is a better way. – paparazzo Mar 19 '18 at 19:40
  • @paparazzo I dont need theory...If someone's knows an answer just right it. And i am confident that there is better way! thats why i'm asking in forum.Should i use SqlTransaction.Commit? – dim Mar 19 '18 at 19:46
  • Your issue is that you don't need an update at all but you are stuck on that concept. You don't need the TrnDocuments table. When you use an IDENTITY property on a column, it will *automatically* increment the column by 1. So you don't ever need the update. You simply insert a new row for the transaction, the database creates the record with a new ID, incremented by 1 (or more if you choose so). You don't run into concurrency issues because the database won't duplicate the ID of an IDENTITY column. – Jacob H Mar 19 '18 at 20:09
  • I don't really understand why everyone suggests identity if OP tries to implement a counter/sequence (since he has one row table). He needs just one increasing number, not millions of useless rows with identity. To the OP - if you need sequence - use it (if you are on version 2012+): https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql. As for your query - you can update and select together with UPDATE ... OUTPUT statement. – Evk Mar 19 '18 at 20:13
  • How many rows are in `TrnDocuments`? What is `TrnNumber` used for? – mjwills Mar 19 '18 at 20:33
  • TrnNumber is an invoice increment. Evk has right. i dont want multiple inserts i want to get output from updated row. – dim Mar 19 '18 at 21:07
  • In that case the duplicate is exactly what you need. See right at the top of this page. – mjwills Mar 19 '18 at 21:09

0 Answers0