Generating Record ID Upon Submission

Report · Jul 08, 2008

I hope im explaining this correctly.

In the past, when using MX 2004 in a form that would create a new record in SQL, there was a simple function to immediately query the db for this new ID to attach to a custom email message upon successful record submission

Here's the code that would get added
-----[SET NOCOUNT ON] in the beginning and [SELECT @@IDENTITY AS NewID SET NOCOUNT OFF] at the end

MM_editQuery = "SET NOCOUNT ON insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ") SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
Set rsNewID=MM_editCmd.Execute
intNewID=rsNewID("NewID")
MM_editCmd.ActiveConnection.Close

-- you can then use intNewID to pull what the new record id will be upon submission.

My problem is this does not work in CS3. the record doesnt even get submitted so whatever this code is doing, it's completely stopping the submission process.

Is there anything other than what im trying that can fix this?

If I didnt supply enough info, please let me know.

Thanks!

Report · Jul 09, 2008

Drew B Drew B Drew wrote:
> My problem is this does not work in CS3. the record doesnt even get submitted
> so whatever this code is doing, it's completely stopping the submission process.
>
> Is there anything other than what im trying that can fix this?
>
> If I didnt supply enough info, please let me know.

It was never a built in function of MX 2004, I have only seen it
available in an extension. Tom Much does one that works with CS3:
http://www.tom-muck.com/extensions/help/insertretrieve/ and
WebAssist.com has it in there DataAssist package.

If I was to do it by hand, I would use a Stored Procedure to do the
insert and return the ID, its much safer than doing it on the page, less
prone to SQL injection.

Steve

Report · Jul 09, 2008

I would have loved to do it in a stored procedure, but doesnt SQL server 2005 no longer have stored procedures?

Either way, I appreciate the response and thank you for your help!

Report · Jul 09, 2008

Drew B Drew B Drew wrote:
> I would have loved to do it in a stored procedure, but doesnt SQL server 2005 no longer have stored procedures?
>
> Either way, I appreciate the response and thank you for your help!

Stored Procedures is how SQL 2005 works, its full of hundreds of them,
they are an integral part of SQL.

http://www.dmxzone.com/ShowDetail.asp?NewsId=3932

Steve