Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jadi
Starting Member
20 Posts |
 Posted - 2009-03-09 : 10:33:30
|
| I have a problem:1. I have to select the next invoice number from one table. 2. Then i have to insert this invoice number as it is in three other tables.3. Then once i have done so i have to go and update the invoice number by +1 (increment by 1).4.unlock table which has invoice numberAnd i have to do this for each row in the datatable.During this whole period i do not want anyone to access (read or change) that invoice number. We can lock the table or row. Does not matter.How can i do it?I am not suppose to use store procedure.Lock table ... does not work. select invoice number with (readlock) ....... only works while this stement is runned.I want to have the table or row locked until i have done 1,2 and 3rd steps.then unlock it.I have to execute sql stmts via vb.net code so i will need way to lock a table and unlock it unless i mention it.Please help me. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-09 : 11:01:14
|
| Can I clarify - Do you mean ?1. Get the Max(invoiceNumber)+ 1 from tableA ? - what is format of this table i.e. does it only hold one row which is updated or is a new row added in step 3. ?2. Insert Max(invoice number)+ 1 into tableB, tableC and tableD.3. Update tableA set invoiceNumber = invoiceNumber + 1 (is this only one row in table or do you mean INSERT new row?)4. Unlock tableA - you should not need to lock table - all of these operations can be combined into a single transaction - which would be easier if you set it up as a stored procedure. |
 |
|
|
jadi
Starting Member
20 Posts |
Posted - 2009-03-09 : 13:44:33
|
| 1. Get the invoiceNumber from tableA 2. Insert invoice number into tableB, tableC and tableD.3. Update tableA set invoiceNumber = invoiceNumber + 1 There are other rows too.4. Unlock tableA - invoicenumber in table A is the last invoice number in the whole system. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-10 : 06:31:33
|
| Sorry but point 3 is still confusing do you want to UPDATE ie. change the value of invoiceNumber for either one or all rows already in table, or do you want to INSERT a new row which has an invoiceNumber of invoiceNumber + 1?If it is INSERT - is invoiceNumber an identity column (auto-incrementing)? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 06:39:11
|
| i think what you need is to make invoiceNumber an identity column. then after each insert use SCOPE_IDENTITY to get last generated id value and insert it to other tables.If you want to do insert in batch, use OUTPUT operator to get generated id values to a table variable. then insert it from there to other tables |
|
|
|
|
|
|
Locking and unlocking of table explicilty