ASP ? - Get PK Value on INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ASP ? - Get PK Value on INSERT

Author

Topic

aspnewbb
Starting Member

31 Posts

Posted - 2006-08-18 : 11:58:33

Basicly I have a table that gets contact info and stores it from a form AND emails me...

Problem is I want to just email a link to the ContactInfo.. which should be referenced via the unique PK

so whwn i view it, it would be nice to reference the row just inserted...

ala view.asp?emailpk=141

figured there woudl be something like recaffected out there.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-18 : 12:01:34

Is it an identity?
If so you can get it via select scope_identity() - you will need to run this in the same batch.
Best to create an SP for the insert and return the value as an output parameter.

In v2005 you can output the value from the insert statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aspnewbb
Starting Member

31 Posts

Posted - 2006-08-18 : 13:54:34

considering im a newb

can you maybe give me an example (dont wanna screw it up)

right now it looks like


insertSQL = "INSERT INTO b2Request(CompName,AccountRep,Email,FName,LName,Address1,City,State,Country,Zip,Phone,Comments,AccountRepEmail) VALUES" & _
			"('" & jsCompName & "','" & jsAccountRep & "','" & jsEMail & "','" & jsFName & "','" & jsLName & "','" & jsAddress1 & "','" & jsCity & "','" & jsState & "','" & jsCountry & "','" & jsZip & "','" & jsPhone & "','" & jsComments & "','" & mailjsAccountRep & "')"

set conn=server.createobject("adodb.connection")
conn.open conn_string


conn.Execute insertSQL,recaffected

how would i do that and get its pk number into a variable?

Thanks man

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-31 : 08:00:12

[code]insertSQL = "SET NOCOUNT ON;" & _
"INSERT INTO b2Request(CompName,AccountRep,Email,FName,LName," & _
" Address1,City,State,Country,Zip,Phone,Comments,AccountRepEmail) " & _
"VALUES ('" & jsCompName & "','" & jsAccountRep & "','" & jsEMail & "', " & _
" '" & jsFName & "','" & jsLName & "','" & jsAddress1 & "','" & jsCity & "', " & _
" '" & jsState & "','" & jsCountry & "','" & jsZip & "','" & jsPhone & "', " & _
" '" & jsComments & "','" & mailjsAccountRep & "');" & _
"SELECT SCOPE_IDENTITY() AS NewID;"

set conn=server.createobject("adodb.connection")
conn.open conn_string
SET RS = conn.Execute(insertSQL) '--> don't know what this is --> ,recaffected

IF NOT RS.EOF THEN NewID = TRIM(RS("NewID"))[/code]

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-31 : 08:24:14

Do not concatenate your strings like this!!! Use parameters, or better yet, use stored procedures. It's one of the most important things any beginner can learn when working with SQL and it will make your life much easier.

The post here: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx explains why and shows examples using ADO.NET but it still applies to ADO.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 08:41:22

Here is an example for ADO and VB
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-31 : 09:44:55

and here's a dtabase access layer you can force everyone to use
http://www.nigelrivett.net/VB/VB6DataAccessLayer.html
http://www.nigelrivett.net/DOTNET/DotNetDBAccess.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources