create a file name using auto number method, MS SQ

kumiko · June 9th, 2008, 01:48 AM

hello there. i need some idea. coz i duno how to start my searching.
i m using asp + ms sql and wan to do a function like:

i wan to generate a filename using tis formula:
the current date +underscore+get the last generate number from the DB+ any characters(autogenerate) ==> then insert into another column such as filename

09062008_123456abc.jpg?

* red color =date
* purple color=number from the last record in DB
* orange color= any characters(autogenerate)

can some one guide me how to do a searching? or how is the concept? thank you very much

Old Pedant · June 9th, 2008, 01:42 PM

I think the best way to do this is in steps:

(1) Create your table with an IDENTITY column (that is, an autonumber column). So let's say you have these fields in your table:
    CREATE TABLE demo (
        id INT IDENTITY,
        filename VARCHAR(30) NULL,
        whatever INT,
        ... other fields ...
        )

(2) Insert the new record, *without* specifying the file name
    INSERT INTO demo ( whatever, ... ) VALUES( 777, ... )

(3) Then perform an UPDATE query to put the filename in place:
    UPDATE demo
    SET filename = CONVERT(VARCHAR(10),Year(getDate())*10000+Month(ge tDate())*100+Day(getDate())
                 + '_'
                 + CONVERT(VARCHAR(20),@@IDENTITY)
                 + '.jpg'

If you do this all in a Stored Procedure, then you can return the new filename. So:

Code:

CREATE PROCEDURE getFileNameForNewFile( 
     @something INT,
     ...any other parameters...
     )
AS
DECLARE @newid INT
DECLARE @daynumber INT
DECLARE @fiilename VARCHAR(50)

SET NOCOUNT ON -- ensure intermediate results not seen

-- add the record, without the filename
INSERT INTO demo ( whatever, ... ) VALUES( @something, ... )

-- get the id value just added
SELECT @newid = @@IDENTITY

-- see remarks below
SET @daynumber = Year(getDate())*10000+Month(getDate())*100+Day(getDate()
-- so use daynumber and newid to create the new filename
SET @filename = = CONVERT(VARCHAR(10),@daynumber)
              + '_' 
              + CONVERT(VARCHAR(20),@newid)
              + '.jpg'
-- and change the record in the db so the filename is what we just created
UPDATE demo SET filename = @filename WHERE id = @newid
-- and now we *WANT* the caller to see ...
SET NOCOUNT OFF
SELECT @filename ' ... what we return as the new filename

*UNTESTED*!!! Off the top of my head.

Remarks: I changed you 09062008 to 20080609. Reason: Now your filenames will "sort" correctly when you do
     ORDER BY filename
As your code was written,
     09062008
would come *before*
     03152009
By using YYYYMMDD pattern, the names will just naturally be correctly ordered!

Old Pedant · June 9th, 2008, 02:53 PM

I missed your requirement for the "abc" characters in there.

Any *REAL* reason for them??? The name is guaranteed to be unique just because of the IDENTITY value being part of the name, so why do you need "abc"???

IN any case, I'd probably just pass them INTO the Stored Proc rather than messing with generating them in T-SQL code. Just pass them as, say, the first parameter to the SP.