|
| |
|
|
Welby
Posts: 59
Joined: 11/12/2002
Status: offline
 |
Generating an Invoice Number - 11/18/2002 10:06:12
Does anyone know of a way (and I' m sure the genius minds here do -- is that enough smooching to get a good answer :) ) to get an invoice number added to a database as someone is posting the data?
I want to have a number generated - and when I e-mail a confirmation to the person who inputs the data - they will have the invoice number. I would like the number to be in the format of MMDDYYXX -- that would make it easiest. I currently have a field on my form for INVOICE number, but I don' t want people to make it up - and if multiple people are in (which is rare) I don' t want them to just pick the next highest number just in case someone submits before them. I definately want to avoid duplicates.
Any help would be GREATLY appreciated.
Welby
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Generating an Invoice Number - 11/18/2002 13:44:48
If you' re using MMDDYYXX, I' ll assume that the ' generated' part is the ' XX' . Have you checked out getting the ID of the last submitted record? That may help - add that (or maybe the last 2 or 3 digits) to the date string and you should have your number.
Does that make sense at all??
| |
|
|
|
Welby
Posts: 59
Joined: 11/12/2002
Status: offline
|
RE: Generating an Invoice Number - 11/18/2002 14:59:00
Actually - it makes sense - until I read the following:
NOTE this topic is only relevant if using ACCESS as a database, this method does not work for SQL
and of course I' m using SQL.
So, I' m still on the hunt.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Generating an Invoice Number - 11/18/2002 16:30:48
I' m quite sure I' ve done similar with SQL server - I just have to go back thru some of my older ' stuff' .....[:' (]. I' ll see if I can find it and get back to you.
In the mean time, anyone else have a solution?
| |
|
|
|
Doug G
Posts: 1189
Joined: 12/29/2001
From: SoCal
Status: offline
|
RE: Generating an Invoice Number - 11/18/2002 18:03:27
I don' t know about DRW stuff, in straight asp code you can issue " SELECT @@IDENTITY as NewID" to get the last AutoNumber or Identity value.
http://support.microsoft.com/default.aspx?scid=KB;en-us;q232144
I would probably use a separate table to act as a counter table.
set rs = conn.execute(" SELECT NextInv FROM MyCounters WHERE counterID = 1" )
lngInvNumber = clng(rs(" NextInv" )
< Message edited by Doug G -- 11/18/2002 6:05:44 PM >
_____________________________
======
Doug G
======
| |
|
|
|
Welby
Posts: 59
Joined: 11/12/2002
Status: offline
|
RE: Generating an Invoice Number - 11/27/2002 11:04:35
Try as I might - I can' t understand this statement: SELECT @@IDENTITY as NewID
When I run it - I get 10 results from NEW ID column as NULL. I' m still doing my best to learn SQL (and Since I' m not a programmer - it' s not been too easy). My columns in my table are as follows: Inspection_id(the auto number column) - Location, Date, Manager, Employee. If someone can give me some insight on how to word the select statement to pull the last record entered (the highest INSPECTION_ID) I would appreciate it. BTW - the table name is: inspection.
Thanks again -
Welby
PS - I have been going through some of the beginner forums to try to learn as much as I can - but if anyone can point me to a good SQL book that talks about writing SQL Query' s - or Statements - I would really appreciate it!
| |
|
|
|
Doug G
Posts: 1189
Joined: 12/29/2001
From: SoCal
Status: offline
|
RE: Generating an Invoice Number - 11/27/2002 23:27:09
If you use an Autonumber column (Access) or identity column (SQL Server) when you insert a new record into your table you don' t include the identity column in the insert SQL, the database will automatically increment the identity column for you.
Immediately after that, you issue another SQL command to retrieve the last autonumber/identity value used by your connection to the database. Here is a sample in asp, assuming con as your already established and opened ado connection and rs is defined as an ado recordset:
<%
' Note: the identity column is not included in the column list
strSql = " INSERT INTO table (col1, col2, col3) VALUES (' value1' , ' value2' , value3' )"
con.execute strSql
'
' When the above insert is completed, the identity column will be incremented.
' You now need to retrieve the new identity value using @@IDENTITY
' The new identity value is returned as a recordset in the code below.
' This is multi-user safe, you' ll only get the last identity value from your db connection.
strSql = " SELECT @@IDENTITY as NewID"
set rs = con.execute(strSql)
'
' Now you have a recordset holding your ID value, put it to some use
nLastID = rs(" NewID" ) ' This moves the value to a local variable
' nLastID = rs(0) ' You can do this and leave out the column name
'
rs.close
set rs = nothing
%>
_____________________________
======
Doug G
======
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 