Solved

Prefill form with Incremental 9 didgit number

Posted on 2008-10-15
7
218 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Viewers will learn how the fundamental information of how to create a table.

815 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

9 Experts available now in Live!

Get 1:1 Help Now