Insert Page and Auto-Number

Intro

This tutorial explains how to create an Insert Page with an Auto-Number field. I'm sure that I'm not the first one to face this problem. I've been looking around at various web sites, including UDZone.com, and didn't manage to find a simple solution that would work.

Overview

• Problem: When creating an insert with UD, you cannot retreive the Auto-Number of the current record for later use.
• Solution: Requires a little amount of coding to work around the problem.

The solution is to work with a recordset instead of a command. The difference is rather important. A Command is a one time SQL execution that allows you to insert, delete, or update your database. The problem is that once the command has executed, you can't manipulate the record you just modified. This is where the recordset comes in.

Important Update

I have updated this tutorial because I created simple extensions which you can use instead of the usual "Insert Page" behavior. The extensions are actually a lot simpler than what I described before. I noticed that a lot of the parsing done by the UltraDev behavior is not necessary if you plan to use ASP Recordsets and MS Access. It should also work with SQL Server.

Details

This is what the old Code looks like when you use the "Insert Page" behavior:

<%
' *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request("MM_insert")) <> "") Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),",")
Delim = MM_typeArray(0)
If (Delim = "none") Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none") Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none") Then EmptyVal = ""
If (FormVal = "") Then
FormVal = EmptyVal
Else
If (AltVal <> "") Then
FormVal = AltVal
ElseIf (Delim = "'") Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''") & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable ...
 ...  & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

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
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

The updated code as it appears using the Server Behavior:

<%
' *** Insert Record: ...

If (CStr(Request("MM_insertAuto")) <> "") Then

If (Not MM_abortEdit) Then
'Execute the insert
'Retrieve record (assume a 1-1 relationship)
Set MM_rs = Server.CreateObject("ADODB.Recordset")
MM_rs.ActiveConnection = MM_editConnection
MM_rs.Source = "SELECT * FROM " & MM_editTable
MM_rs.CursorType = 1
MM_rs.CursorLocation = 2
MM_rs.LockType = 3
MM_rs.Open()
MM_rs_numRows = 0

MM_rs.AddNew

' Fill in the fields on the form
Dim varTemp
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
varTemp = MM_fields(i+1)
If (varTemp <> "NULL") And (varTemp <> "") Then
MM_rs.Fields(MM_columns(i)).value = varTemp
End if
Next

MM_rs.Update
MM_rs.MoveLast
MM_AutoNum = MM_rs.Fields(MM_editColumn).value
MM_rs.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

The code that has been removed was used to parse the correct type for a given field in an SQL Command. However, since I'm not using an SQL command, the whole code can be removed and replaced by a loop.

You can download the Extension Package here.