INSERT INTO… return ID

I just know I’m going to forget this if I don’t write it down. Within VBScript/ASP and SQL, this is how I insert a row to a table and retrieve the ID (key) of the row just inserted.

dim dbs
dim rst
set dbs = server.createobject("adodb.connection")
set rst = server.createobject("adodb.recordset")
dbs.open your DB connection information

Dim rowKey
strSQL= "SET NOCOUNT ON;" &_
	"INSERT INTO " &_
		"theTable (" &_
			"ModifiedDate, " &_
			"CreatedDate, " &_
			"otherStuff" &_
		") " &_
	"VALUES (" &_
		"'" & now & "', " &_
		"'" & now & "', " &_
		"'" & SomeData & "'" &_
	");" &_
	"SELECT @@IDENTITY AS recID;SET NOCOUNT OFF;"
rst = dbs.execute(strSQL)
rowKey = rst("recID")

The variable, rowKey, now holds the record ID of the inserted row. Hopefully I look here first next time…

Leave a Reply

 

Staypressed theme by Themocracy