Solved

Prefill form with Incremental 9 didgit number

Posted on 2008-10-15
7
217 Views
Last Modified: 2010-05-18
I have a form that once filled will insert the data into a table called TempGIS
The form requires that you enter in a log number.
I want this log number to be prefilled when the person access the page and that number should not be a number that is already in the Table tempGIS.
So basicly each time a person visits the page a UNIQUE number will be prefilled in the Log_ID field.

Can this be done?
0
Comment
Question by:xeroxcanada
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22721845
The best way to do this would be to have a log ID.

create table TempGIS_LogID
 (LogID int identity(1,1) primary key clustered
 ,usedate datetime getdate())

and a retrieval procedure

create procedure up_GetLogId
as
insert into tempGIS_logid(usedate) values (getdate())
select '000000000' + cast(scope_identity() as varchar(9)) as LogID
go


Now if you execute up_GetLogId when you load your page, you will have a preallocated ID that can't be reused.
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 22722292
When the page loads and runs the procedure how do I get the Log_ID in a form field on that page.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22722343
That depends on what language you are using and how you do your database connections currently.  When that procedure executes, it will return a value.  The value will be a single record, single column called LogID.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:xeroxcanada
ID: 22722438
I am using Dreamweaver and ASP. So when the page loads runs the procedure do I create a varailable called Log_ID and put that into the Form Field?

Also when trying to create the table TEMPGIS_Log_ID I get the following error.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'getdate'.
0
 
LVL 13

Accepted Solution

by:
devsolns earned 75 total points
ID: 22722657
why not just generate a random GUID that will always be unique!


declare @guid uniqueidentifier
set @guid= NEWID()
print 'Value of @guidis: '+ RIGHT(CONVERT(varchar(255), @guid),9)
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 25 total points
ID: 22722710
Sorry... functions can't go in values clauses but the rest still applies.

The right 0 digits, while highly unlikely to repeat, are not guaranteed not to.  And the request is for an incremental number, not pseudo random alphanumeric (hex).

How are you doing your other database access.  You will just need to call and get the value from the procedure in the same way.



create table TempGIS_LogID

 (LogID int identity(1,1) primary key clustered

 ,usedate datetime default getdate())

go

create procedure up_GetLogId 

as

insert into tempGIS_logid(usedate) select getdate()

select '000000000' + cast(scope_identity() as varchar(9)) as LogID

go

Open in new window

0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 22727678
Thank You both for your help. Although both Items could have solved my problem I used only one and will keep the other option on the back burner.
I have included the Code that I used to solve the question.
Again Thank you very much for such a fast response

<%

Function createGuid()

  Set TypeLib = Server.CreateObject("Scriptlet.TypeLib")

  tg = TypeLib.Guid

  createGuid = left(tg, len(tg)-2)

  Set TypeLib = Nothing

End Function

%>
 

<%guidis = RIGHT(createGuid,9) %>

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now