Passing ASP Variables

[Darren Woodbrey]

I have a form that submits to a SQL database. The table in SQL uses an
identity field for the primary key called "entry". This is so I can get the
primary key to auto-increment. The form posts to a page with a SQL insert
statement. On this page I run a select statement to retrieve the entry
variable.

declare @entry int
set @entry = (select scope_identity())
SELECT entry
FROM wo_main
WHERE (entry = @entry)

This will display the the primary key that was just entered. I now want to
use response.redirect or something else to redirect to a links page
immediately. The problem is getting the @entry variable to pass to the
links page.

I can get this to work if the origianl form is actually submitting the
primary key field using a respose.write but can not figure out how to do it
with the primary key being auto-assigned. Any help would be very much
appreciated. Thanks

Darren
MCP

[Ray at]

Use a stored procedure as so:


CREATE PROC something (@yourData varchar(50)) AS
SET NOCOUNT ON
INSERT INTO wo_main (columnName) VALUES (@yourData)
SELECT SCOPE_IDENTIY()
GO


<%
sValue = "something"
Set rs = yourADOObject.Execute("EXEC something '" & sValue & "')
iNewIdentity = rs.Fields.Item(0).Value
rs.Close : Set rs = Nothing
yourADOObject.Close : Set yourADOObject = Nothing
%>

Another option is to set your scope_identity to be an output parameter and
use a command object to get it. Some would argue that this is better. I
believe you can read more about that here.
[url]http://www.aspfaq.com/show.asp?id=2201[/url] I personally don't, and I don't have
any excuse or reason that is valid!

Ooh, also see here: [url]http://www.aspfaq.com/show.asp?id=2174[/url]

Ray at work




"Darren Woodbrey" <darrenwoodbrey@hpfairfield.com> wrote in message
news:uK7Ek4iIEHA.3128@TK2MSFTNGP10.phx.gbl...

> I have a form that submits to a SQL database. The table in SQL uses an
> identity field for the primary key called "entry". This is so I can get

the

> primary key to auto-increment. The form posts to a page with a SQL insert
> statement. On this page I run a select statement to retrieve the entry
> variable.
>
> declare @entry int
> set @entry = (select scope_identity())
> SELECT entry
> FROM wo_main
> WHERE (entry = @entry)
>
> This will display the the primary key that was just entered. I now want

to

> use response.redirect or something else to redirect to a links page
> immediately. The problem is getting the @entry variable to pass to the
> links page.
>
> I can get this to work if the origianl form is actually submitting the
> primary key field using a respose.write but can not figure out how to do

it

> with the primary key being auto-assigned. Any help would be very much
> appreciated. Thanks
>
> Darren
> MCP
>
>

[Bullschmidt]

4GuysFromRolla.com : ASP FAQS : Databases, General
How do I get the record number of a just added record, using an Access
database table? by Bill Wilkinson - 11/3/2000
[url]http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79[/url]

ADO FAQ - Q9) How do I get the AutoNumber (or Identity) for a newly
inserted record?
[url]http://www.able-consulting.com/ADO_Faq.htm#Q9[/url]

INFO: Identity and Auto-Increment Fields in ADO 2.1 and Beyond
[url]http://support.microsoft.com/support/kb/articles/q233/2/99.ASP[/url]

INFO: Identity (AutoIncrement) Columns in ADO or RDS
[url]http://support.microsoft.com/support/kb/articles/q195/9/10.asp[/url]

User Tips: Getting the ID of the Just Inserted Database Record by Dan H.
- 12/26/2000
[url]http://www.4guysfromrolla.com/webtech/tips/t122600-1.shtml[/url]

Best regards,
J. Paul Schmidt, Freelance ASP Web Consultant
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!