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 |
|
vk18
Posting Yak Master
146 Posts |
 Posted - 2006-09-29 : 16:57:19
|
| Hi Friends,I already posted this Question. I did modified a little.here is my query. I want to have this invoicenumber as autoincrement number like 1,2,3,4,5 and so on.. But when i run this query it is just adding '1' to all the columns. by the way i have one identity column in the table.Thxsql = "select max(invoicenumber) as maxinvoiceid from tblinv"Dim getMaxinvoiceid As Integer = dbfunctions.GetSingleIntData(CONNECTIONSTRING, sql, "maxinvoiceid")sql = "INSERT INTO tblInv(fkgroup,extension,invoicenumber,fkAdvertiser,fkAgency,fksubgroup,generationDate,BroadcastMonth) SELECT DISTINCT '" & Me.txtgroup.Text & "',0," & getMaxinvoiceid + 1 & ",fkadvertiser,fkagency,'" & Me.txtsubgroup.Text & "','" & Today.Date.Now & "','" & Me.ddbmonth.SelectedValue & "' from tblcontract WHERE date <= '" & Me.ddbmonth.SelectedValue & "' AND fkContractType = 'UNWIRED'" |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-29 : 19:15:25
|
| So you want to select a bunch of records from tblcontract and assign the next available invoice number to each one, and insert the rows into tblInv.You are assigning the next available invoice number, but using the same value for every row selected from tblcontract.You could select the data into a temporary table, having declared an IDENTITY column on the temporary table. So now all your rows have an ID number 1, 2, 3 ...And then you could insert into tblInv FROM the temporary table - but this time adding the current MAX invoice number to the Identity column in the temporary table.Ideally you should get the max(invoicenumber) as part of the Insert operation into tblInv so that it is atomic. In your VB code there is a window-of-opportunity between getting the maxinvoiceid and using it in the Insert statement - which means someone else could add the next invoice number in sequence between you getting maxinvoiceid and making your insert (but you could use Locks to prevent that happening)Kristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-30 : 03:32:17
|
| "Then why do you want to have again a sequence number"Madhi: As its an invoice number I assume that "no gaps" is for audit/tax-man reasonsKristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-09-30 : 04:19:40
|
| Well Kristem. I also heard that if invoice is deleted, all invoices should be renumbered so that there are no gaps. Is this normal practice everywhere?MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-30 : 05:56:37
|
Hehehe ... imagine mailing all your customers saying "Sorry, the invoice I sent you #12345 is now #12344"  I would not allow an "invoice" to be deleted once a record was created, it would have to have a Status column changed to CANCELLED or somesuch.I think what vk18 is grappling with is that IDENTITY would be no good for an Invoice number (assuming that no gaps are allowed for above reason) because of lost-numbers caused by any rollbackKristen |
|
|
|
|
|
|
|
|
Auto Increment Number