Automatic counter

  • Thread starter Thread starter Tommy Malone
  • Start date Start date
T

Tommy Malone

I'm using MSSQL and VB.

I want to pull an item from an "Inventory" table and assign it a
"TransactionID", then insert it into (or insert with a default value in the
TransactionID column) a "Transactions" table.

It would be easier to have the TransactionID before the item is written to
the "Transactions" table, but I'll take any help I can get.

My questions are:

1. How do I create a number formatted as 10 digits (say starting 0000000001)
and incrementing with each transaction to be inserted into "TransactionID"
and written to the "Transactions" table?

2. How do I ensure that "TransactionID" is unique across the "Transactions"
table, regardless of the inventory item? In other words, there can never be
two (0000000001)'s.

Thanks for your help. Sorry for such a poorly framed question.
 
You'd be better off writing this one as a stored procedure using
explicit transactions than writing it in VB.NET. Your stored procedure
code would check to make sure the number doesn't already exist and
commit/rollback accordingly.

If you want leading zeros, then you'll need to supply them in the
front-end as a format since SQL Server doesn't put them in numeric
data types.

--Mary
 
Back
Top