Autonumber from ASP and not SQL!

[Bubsa]

Good Afternoon team, I am looking for a bit of guidance please, I am a newbie so I appreciate there may be better ways to do what I am doing. I have a basic web application using VS2008 and with a SQL Backend. The database is a relational database and all tables have primary keys. One table Invoice has a PK which is InvoiceID within SQL this is set to Autonumber (identity).

What I need is for other column called InvoiceNumber to be updated to equal the InvoiceID + 20000 this needs to be an event with ASP after I have clicked insert. I have designed the insert page for this to use a SQLDatsource and a Details View with VS2008. Also what I would like is for this number or the InvoiceID to be held in memory so that when I use a redirect from this same page to update another table InvoiceItem this number automatically links the items with the Invoice number or ID could you advise best ways of doing the 2 tasks above please.

 

[ca8msm]

What I need is for other column called InvoiceNumber to be updated to equal the InvoiceID + 20000 this needs to be an event with ASP after I have clicked insert.

Why does it need to be afterwards?

Would it be possible to to either have a calculated column, or to have a second update statement in the insert procedure that enters that value for you? This way you are keeping the data within the database and not relying on your application to do it for you (imagine if you run an insert directly from SSMS, you would then have a record with no InvoiceNumber).


Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[Bubsa]

Hello ca8msm thanks for your interest in my issue, I may be thinking about this all wrong so again feel free to put me right. In response to your first question I believe it can only be done afterwards as only after you click insert would you be able to get the InvoiceID ? How would I do the Calculated column in Asp or SQL if its ASP would you do this in the mark-up ?

I am not using a stored procedure to enter the data in fact I have several dropdown list each with their own SQLDatasource but it is only the last Datasource that I am trying to bind back to the database if that makes sense.

 

[ca8msm]

What I was suggesting is that you could do several things to not have to make several database trips i.e.

1. Just return the number and don't even store it

select InvoiceID, InvoiceID+20000 as InvoiceNumber from table

2. Store it in a calculated column

create table invoices(
	InvoiceId int identity(1,1),
	InvoiceName varchar(20),
	InvoiceNumber AS InvoiceId+20000
)

3. Update it after insert

create proc insertInvoice
(
 @InvoiceName varchar(20)
)
as
insert invoices values (@InvoiceName)
update invoices set InvoiceNumber = InvoiceId+20000 where InvoiceId = scope_identity()

These are just meant as simple examples to demonstrate how you could do it, but that should give you a good starting point to research into them.

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[jmeckley]

I am not using a stored procedure to enter the data in fact I have several dropdown list each with their own SQLDatasource but it is only the last Datasource that I am trying to bind back to the database if that makes sense.

2 different things.
stored procs are compiled queries on the sql server. data source controls are an "easy" way to access data in the database. whether the datasource is tied to a stored proc or dynamic sql doesn't matter.

all that said, the more you rely on DataSource controls the less control you have over logic in your system. datasource controls only work in the most simple scenarios. and they cannot be optimized either. as a general rule, just don't use them.

Why are Invoice Numbers tied to a primary key at all? this itself is a questionable choice. the purpose of PK and FK in a relational database is simply to provide a relationship between rows in separate tables. there should not be any business meaning to PK or FK at all. that said generating the Invoice Number on the PK means the PK has a direct effect on how the order is defined.

You are basically incrementing the Invoice Number anyway (Pk + 200,000). PK is auto-incrementing, therefore the invoice number is too. just make the invoice number column auto-incrementing and start the seed at 200,000 instead of 0. that would solve the problem altogether. you also remove the PK having any business meaning at all.

2 other database generation options are:
1. a calculated column
2. insert trigger

I'm assuming you want to use auto-increment ids and Invoice numbers based on the PK because you don't want gaps in your invoice numbers. this will only work if you never rollback a transaction. rolling back a transaction doesn't rollback the auto-incremented value.

example:
[tt]
current auto-increment id = 1
-----------
begin transaction
insert order
current auto-increment is now 2
error!
rollback transaction
-----------
current auto-increment id = 2 (not 1)
[/tt]
there is another approach which I'll mention, but it doesn't sound like a good fit for your scenario. create the PK and Invoice number on the client (code). the shifts the balance of power from the database to application code. as with any choice there are pros and cons to this approach. I'll forgo the lengthy discussion for now.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259