Insert Record and Retrieve autonumber extension

[galeemma]

I followed the instructions for useage correctly:
[url]http://www.tom-muck.com/extensions/help/insertretrieve/[/url]

Unfortunately it is producing the error below:

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or
ordinal.
/addRisk2.asp, line 81

Line 81 is the second line of the following excerpt:

if Len(TM_fields(i+1)) > 0 AND TM_fields(i+1)<> "''" then
TM_editCmd.Fields(TM_columns(i)) = TM_fields(i+1)

I am not entirely sure what I am looking for as I only have a tiny
understanding.....

here is the code before the body in its entirety

<%
' *** declare variables
TM_editAction = CStr(Request("URL"))
If (Request.QueryString <> "") Then
TM_editAction = TM_editAction & "?" & Request.QueryString
End If
' boolean to abort record edit
TM_abortEdit = false
%>
<%
' *** Insert Record and retrieve autonumber: set variables
If (CStr(Request("TM_insert")) <> "") Then
MM_editConnection = MM_conRisk_STRING
TM_editTable = "[Risk Unit]"
TM_editRedirectUrl = "managerConfirmAddRisk.asp"
TM_fieldsStr = "RiskDesc|value|Severity|value|Likelihood|value|Or iginator
(Authorised)|value|Date Risk Entered|value|Date risk score
changed|value|TM_insert|value"
TM_columnsStr =
"RiskDesc|',none,''|Severity|none,none,NULL|Likeli hood|none,none,NULL|[Originato
r (Authorised)]|',none,''|[Date Risk Entered]|',none,NULL|[Date risk score
changed]|',none,NULL|[RID Unit]|none,none,NULL"
' create the TM_fields and TM_columns arrays
TM_fields = Split(TM_fieldsStr, "|")
TM_columns = Split(TM_columnsStr, "|")
' set the form values
For i = LBound(TM_fields) To UBound(TM_fields) Step 2
TM_fields(i+1) = CStr(Request.Form(TM_fields(i)))
Next

' append the query string to the redirect URL
If (TM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, TM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
TM_editRedirectUrl = TM_editRedirectUrl & "?" & Request.QueryString
Else
TM_editRedirectUrl = TM_editRedirectUrl & "&" & Request.QueryString
End If
End If
TM_dontClose = false
Else
TM_dontClose = true
End If
%>
<%
' *** Insert Record and retrieve autonumber for MS Access
' *** ID value is stored in the TM_editCmd("youridcolumn") value
If (CStr(Request("TM_insert")) <> "") Then
' create the sql insert statement
TM_tableValues = ""
TM_dbValues = ""
For i = LBound(TM_fields) To UBound(TM_fields) Step 2
FormVal = TM_fields(i+1)
TM_typeArray = Split(TM_columns(i+1),",")
Delim = TM_typeArray(0)
If (Delim = "none") Then Delim = ""
AltVal = TM_typeArray(1)
If (AltVal = "none") Then AltVal = ""
EmptyVal = TM_typeArray(2)
If (EmptyVal = "none") Then EmptyVal = ""
if (EmptyVal = "NULL") then EmptyVal = ""
If (FormVal = "") Then
FormVal = EmptyVal
Else
If (AltVal <> "") Then
FormVal = AltVal
End If
End If
TM_fields(i+1) = FormVal
Next
If (Not TM_abortEdit) Then
' execute the insert using the AddNew method
set TM_editCmd = Server.CreateObject("ADODB.Recordset")
TM_editCmd.ActiveConnection = MM_editConnection
TM_editCmd.CursorType = 1
TM_editCmd.LockType = 3
TM_editCmd.Source = TM_editTable
TM_editCmd.Open
TM_editCmd.AddNew
For i = LBound(TM_fields) To UBound(TM_fields) Step 2
'If a value for the column name was passed in,
'set the column name equal to the value passed through the form...
if Len(TM_fields(i+1)) > 0 AND TM_fields(i+1)<> "''" then
TM_editCmd.Fields(TM_columns(i)) = TM_fields(i+1)
end if
Next
TM_editCmd.Update
Session("myID") = TM_editCmd("RID Unit")
If (TM_editRedirectUrl <> "") Then
TM_editCmd.ActiveConnection.Close
Response.Redirect(TM_editRedirectUrl)
End If
End If
End If
%>

any help would be greatly appreciated.
kind regards
Emma Gale

[danilocelic *TMM*]

galeemma wrote:

> I followed the instructions for useage correctly:
> [url]http://www.tom-muck.com/extensions/help/insertretrieve/[/url]
>
> Unfortunately it is producing the error below:
>
> ADODB.Recordset (0x800A0CC1)
> Item cannot be found in the collection corresponding to the requested name or
> ordinal.
> /addRisk2.asp, line 81

What that error tells me is that you are trying to insert into a column
name that isn't present in the database. I'd check to make sure that
your field names in the page within the form and the database column
names match up.


--
Danilo Celic
| Extending Knowledge, Daily [url]http://CommunityMX.com/[/url]

[Tom Muck]

"galeemma" <webforumsuser@macromedia.com> wrote in message
news:d48efl$co4$1@forums.macromedia.com...

> I followed the instructions for useage correctly:
> [url]http://www.tom-muck.com/extensions/help/insertretrieve/[/url]

Make sure your field names don't contain special characters (letters,
number. underscores only), and also try to eliminate spaces if at all
possible. Many times the database software will allow you to name things
improperly.

Tom