Tutorials
Retrieve new ID using a Command
This tutorial cover the use of the Command in Dreamweaver MX to insert a new record into a SQL Server database and retrieve the newly created identity number.
The first thing to do is to build a standard Insert command using the Command dialogue.
Once you have done that, delve into the code and modify as follows:
Find this line:
cmdInsert.CommandText = "INSERT INTO dbo.tblUser (UserName) VALUES ('" + Replace(cmdInsert__varUsername, "'", "''") + ") "
And replace it with this line:
cmdInsert.CommandText = "SET NOCOUNT ON;INSERT INTO dbo.tblUser (UserName) VALUES ('" + Replace(cmdInsert__varUsername, "'", "''") + ");SELECT @" & "@IDENTITY AS ID"
Find this line:
cmdInsert.Execute()
And replace it with the following:
Set rsID = cmdInsert.Execute()
IF (NOT rsID.EOF) THEN
varLastID = rsID.Fields.Item("ID").Value
END IF
What happens here is the insert takes place and the newly created ID is retrieved and stuffed into a recordset. We then query the recordset for the new ID and assign it to a variable called varLastID.
Notes
- The last part of the modified Insert statement is broken up around the @@ symbols to avoid potential problems.
- If you want to be really neat and tidy, you can close the recordset and set it to nothing by adding the relevant code after the variable is assigned the value from the recordset. The code to do that would be as follows:
Set rsID = cmdInsert.Execute()
IF (NOT rsID.EOF) THEN
varLastID = rsID.Fields.Item("ID").Value
rsID.Close()
Set rsID = Nothing
END IF
Thats it!
There are several ways and explanations out there that cover Insert and Retrieve ID's using standard Insert server behaviours in Dreamweaver MX - even some excellent extensions to do it, but nothing on using the Command to do the same thing - so here it is!
I hope this helps someone.
© Copyright 2008 - robgt.com