I have an asp page in which the user completes some information and the data
is stored in an SQL database. I want to automatically assign an unique ID
number (can start with 1) to each record when the data is saved to the
database and display this number on the confirmation page.
This is probably much easier that I am making it. Any help would be
appreciated.
Thanks,CREATE TABLE dbo.CustomerData
(
CustomerDataID INT IDENTITY(1,1),
SomeData VARCHAR(32)
)
GO
CREATE PROCEDURE dbo.AddCustomerData
@.SomeData VARCHAR(32),
@.CustomerDataID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
SET @.CustomerDataID = SCOPE_IDENTITY();
END
GO
DECLARE @.id INT;
EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
SELECT new_id = @.id;
SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>I have an asp page in which the user completes some information and the
>data
> is stored in an SQL database. I want to automatically assign an unique ID
> number (can start with 1) to each record when the data is saved to the
> database and display this number on the confirmation page.
> This is probably much easier that I am making it. Any help would be
> appreciated.
> Thanks,|||Do I drop this code on the asp page?
"Aaron Bertrand [SQL Server MVP]" wrote:
> CREATE TABLE dbo.CustomerData
> (
> CustomerDataID INT IDENTITY(1,1),
> SomeData VARCHAR(32)
> )
> GO
> CREATE PROCEDURE dbo.AddCustomerData
> @.SomeData VARCHAR(32),
> @.CustomerDataID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
> SET @.CustomerDataID = SCOPE_IDENTITY();
> END
> GO
> DECLARE @.id INT;
> EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
> SELECT new_id = @.id;
> SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
>
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>
>|||No, this is T-SQL code, not ASP code. For some help running the stored
procedure from ASP, see an ASP newsgroup, if these articles don't clear it
up:
http://www.aspfaq.com/2201
http://www.aspfaq.com/params.htm
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
> Do I drop this code on the asp page?|||Gasp! No, you copy / paste it into the OnBlur event your website's flaming
logo. Please tell me this is not a e-commerce, financial, or defense related
website!
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
> Do I drop this code on the asp page?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Are you kidding. It is just an company Intranet page.
Man, if they let me program fo rthe feds, look out...lol
Just trying to automate a process.
Let me ask this on the example from Aaron.
I get that dbo.CustomerData is my DB name (Compliance). My ID field is
called RequestID so is that CustomerDataID or SomeData? What would the othe
r
one be (the name of the table?).
Sorry, not a programmer by trade but I certainly appreciate the help...
"JT" wrote:
> Gasp! No, you copy / paste it into the OnBlur event your website's flaming
> logo. Please tell me this is not a e-commerce, financial, or defense relat
ed
> website!
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
>
>|||OK, it takes me awhile to grasp the concept but here is what I got:
CREATE TABLE dbo.CommunityService
(
RefNum Int IDENTITY(1,1),
BranchDept VARCHAR(75),
Region VARCHAR(50),
EmployeeName VARCHAR(50),
CompletedBy VARCHAR(50),
Organization VARCHAR(125),
Contacts VARCHAR(50),
Phone VARCHAR(50),
Address VARCHAR(255),
CensusArea NTEXT,
ServiceType NTEXT,
ServiceDesc NTEXT,
BenefitDesc NTEXT,
MoneyEquip NTEXT,
Comments NTEXT,
TimeStamp DATETIME
)
GO
CREATE PROCEDURE dbo.AddRequestID
@.Region VARCHAR(50),
@.RefNum INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.CommunityService(Region) SELECT @.Region;
SET @.RefNum = SCOPE_IDENTITY();
END
GO
DECLARE @.id INT;
EXEC dbo.AddRequestID, @.id OUTPUT;
SELECT new_id = @.id;
SELECT RefNum, Region FROM dbo.CommunityService;
My question is that I am getting an error on Line 3 near ','. It all seems
OK so what am I missing?
********************************
"Ken D." wrote:
> Are you kidding. It is just an company Intranet page.
> Man, if they let me program fo rthe feds, look out...lol
> Just trying to automate a process.
> Let me ask this on the example from Aaron.
> I get that dbo.CustomerData is my DB name (Compliance). My ID field is
> called RequestID so is that CustomerDataID or SomeData? What would the ot
her
> one be (the name of the table?).
> Sorry, not a programmer by trade but I certainly appreciate the help...
> "JT" wrote:
>|||> My question is that I am getting an error on Line 3 near ','. It all
> seems
> OK so what am I missing?
Part of the code sample I sent. What does this mean?
> EXEC dbo.AddRequestID, @.id OUTPUT;
You forgot to include your region parameter:
EXEC dbo.AddRequestID 'NorthEast', @.id OUTPUT;|||Cool. I added 'test" and changed my seed to 0. It worked wonderful.
Thanks and so sorry for the ignorance.
Have a great day Aaron.
"Aaron Bertrand [SQL Server MVP]" wrote:
>
> Part of the code sample I sent. What does this mean?
>
> You forgot to include your region parameter:
> EXEC dbo.AddRequestID 'NorthEast', @.id OUTPUT;
>
>
>
>|||Aaron,
One last question. What is I wanted to add a prefix to the ID. Is that
possible?
"Aaron Bertrand [SQL Server MVP]" wrote:
> CREATE TABLE dbo.CustomerData
> (
> CustomerDataID INT IDENTITY(1,1),
> SomeData VARCHAR(32)
> )
> GO
> CREATE PROCEDURE dbo.AddCustomerData
> @.SomeData VARCHAR(32),
> @.CustomerDataID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
> SET @.CustomerDataID = SCOPE_IDENTITY();
> END
> GO
> DECLARE @.id INT;
> EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
> SELECT new_id = @.id;
> SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
>
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>
>
Valvert: