Solved

Prefill form with Incremental 9 didgit number

Posted on 2008-10-15
7
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

752 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