SP update statement error
I have a page where I process database record updates using a stored procedure. I had to alter my form page by ammending a recordset value to the names of the form fields. Thereby adding a recordsetID value to the end of each form field name...i.e. fieldname<%=(rs.Fields.Item("ABR_DETLS_ID").Value) %>
This has caused a break in my update statement where I call my stored procedure. I am geting a subscript out of range error because the fieldnames in my update statement are no longer valid. I believe I need to amend the value <%=(rs.Fields.Item("ABR_DETLS_ID").Value)%> to the form field names in my update statement.
Each value for <%=(rs.Fields.Item("ABR_DETLS_ID").Value)%> is passed in the form collection as a comma delimited list with the following form field name: detlsidA
So, detlsidA would have a comma delimited list with all values for <%=(rs.Fields.Item("ABR_DETLS_ID").Value)%>
I.E. (2, 3, 4, 10, 12, 50, etc.) whatever numbers.
So, each field in the form that is repeated using a loop would have the name:
fieldA2
fieldA3
fieldA4
fieldA10
fieldA12
etc.
Here is my update statement:
<%
Dim cb_status
If Request.Form("review") <> "" then
cb_status = Request.Form("review")
Else
cb_status = Request.Form("Inprocess")
End If
strSubmit = Request.Form("Submit")
If strSubmit = "Update" Then
Dim cmd_AlloUpdate__hdrid
cmd_AlloUpdate__hdrid = ""
if(Request("hdrid") <> "") then cmd_AlloUpdate__hdrid = Request("hdrid")
Dim cmd_AlloUpdate__ddo
cmd_AlloUpdate__ddo = ""
if(Request("ddomenuA") <> "") then cmd_AlloUpdate__ddo = Request("ddomenuA")
Dim cmd_AlloUpdate__ay
cmd_AlloUpdate__ay = ""
if(Request("yrA") <> "") then cmd_AlloUpdate__ay = Request("yrA")
Dim cmd_AlloUpdate__allotype
cmd_AlloUpdate__allotype = ""
if(Request("allotypeA") <> "" ) then cmd_AlloUpdate__allotype = Request("allotypeA")
Dim cmd_AlloUpdate__strategy
cmd_AlloUpdate__strategy = ""
if(Request("strategyA") <> "") then cmd_AlloUpdate__strategy = Request("strategyA")
Dim cmd_AlloUpdate__budgobj
cmd_AlloUpdate__budgobj = ""
if(Request("BOA") <> "") then cmd_AlloUpdate__budgobj = Request("BOA")
Dim cmd_AlloUpdate__fteclass
cmd_AlloUpdate__fteclass = ""
if(Request("fteclassA") <> "") then cmd_AlloUpdate__fteclass = Request("fteclassA")
Dim cmd_AlloUpdate__origamt
cmd_AlloUpdate__origamt = ""
if(Request("origamtA") <> "") then cmd_AlloUpdate__origamt = Request("origamtA")
Dim cmd_AlloUpdate__incrdecr
cmd_AlloUpdate__incrdecr = ""
if(Request("incrdecrA") <> "") then cmd_AlloUpdate__incrdecr = Request("incrdecrA")
Dim cmd_AlloUpdate__status
cmd_AlloUpdate__status = cb_status
'if(Request("review") <> "") then cmd_AlloUpdate__status = Request("review")
Dim cmd_AlloUpdate__detlsid
cmd_AlloUpdate__detlsid = ""
if(Request("detlsidA") <> "") then cmd_AlloUpdate__detlsid = Request("detlsidA")
Dim ddolist
Dim aylist
Dim allocationtypelist
Dim strategylist
Dim budgobjlist
Dim fteclasslist
Dim origamtlist
Dim incrdecrlist
Dim detlsidlist
ddolist = Split(Request.Form("ddomenuA"),", ")
aylist = Split(Request.Form("yrA"),", ")
allocationtypelist = Split(Request.Form("allotypeA"),", ")
strategylist = Split(Request.Form("strategyA"),", ")
budgobjlist = Split(Request.Form("BOA"),", ")
fteclasslist = Split(Request.Form("fteclassA"),", ")
origamtlist = Split(Request.Form("origamtA"),", ")
incrdecrlist = Split(Request.Form("incrdecrA"),", ")
detlsidlist = Split(Request.Form("detlsidA"),", ")
set cmd_AlloUpdate = Server.CreateObject("ADODB.Command")
cmd_AlloUpdate.ActiveConnection = MM_DBConn_STRING
cmd_AlloUpdate.CommandText = "dbo.ALLOCATION_UPDATE"
cmd_AlloUpdate.CommandType = 4
cmd_AlloUpdate.CommandTimeout = 0
cmd_AlloUpdate.Prepared = true
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@RETURN_VALUE", 3, 4)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@hdrid", 3, 1,4,cmd_AlloUpdate__hdrid)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@ddo", 200, 1,8000,cmd_AlloUpdate__ddo)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@ay", 200, 1,8000,cmd_AlloUpdate__ay)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@allotype", 200, 1,8000,cmd_AlloUpdate__allotype)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@strategy", 200, 1,8000,cmd_AlloUpdate__strategy)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@budgobj", 200, 1,8000,cmd_AlloUpdate__budgobj)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@fteclass", 200, 1,8000,cmd_AlloUpdate__fteclass)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@origamt", 200, 1,8000,cmd_AlloUpdate__origamt)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@incrdecr", 200, 1,8000,cmd_AlloUpdate__incrdecr)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@status", 200, 1,50,cmd_AlloUpdate__status)
cmd_AlloUpdate.Parameters.Append cmd_AlloUpdate.CreateParameter("@detlsid", 200, 1,8000,cmd_AlloUpdate__detlsid)
Loop_Max = UBound(detlsidlist)
For x = 0 to Loop_Max
cmd_AlloUpdate.Parameters.Item("@ddo").Value = ddolist(x)
cmd_AlloUpdate.Parameters.Item("@ay").Value = aylist(x)
cmd_AlloUpdate.Parameters.Item("@allotype").Value = allocationtypelist(x)
cmd_AlloUpdate.Parameters.Item("@strategy").Value = strategylist(x)
cmd_AlloUpdate.Parameters.Item("@budgobj").Value = budgobjlist(x)
cmd_AlloUpdate.Parameters.Item("@fteclass").Value = fteclasslist(x)
cmd_AlloUpdate.Parameters.Item("@origamt").Value = origamtlist(x)
cmd_AlloUpdate.Parameters.Item("@incrdecr").Value = incrdecrlist(x)
cmd_AlloUpdate.Parameters.Item("@detlsid").Value = detlsidlist(x)
cmd_AlloUpdate.Execute()
Next
The following section is where I need to ammend the recordset value to the fieldnames:
Dim cmd_AlloUpdate__ddo
cmd_AlloUpdate__ddo = ""
if(Request("ddomenuA") <> "") then cmd_AlloUpdate__ddo = Request("ddomenuA")
Dim cmd_AlloUpdate__ay
cmd_AlloUpdate__ay = ""
if(Request("yrA") <> "") then cmd_AlloUpdate__ay = Request("yrA")
Dim cmd_AlloUpdate__allotype
cmd_AlloUpdate__allotype = ""
if(Request("allotypeA") <> "" ) then cmd_AlloUpdate__allotype = Request("allotypeA")
Dim cmd_AlloUpdate__strategy
cmd_AlloUpdate__strategy = ""
if(Request("strategyA") <> "") then cmd_AlloUpdate__strategy = Request("strategyA")
Dim cmd_AlloUpdate__budgobj
cmd_AlloUpdate__budgobj = ""
if(Request("BOA") <> "") then cmd_AlloUpdate__budgobj = Request("BOA")
Dim cmd_AlloUpdate__fteclass
cmd_AlloUpdate__fteclass = ""
if(Request("fteclassA") <> "") then cmd_AlloUpdate__fteclass = Request("fteclassA")
Dim cmd_AlloUpdate__origamt
cmd_AlloUpdate__origamt = ""
if(Request("origamtA") <> "") then cmd_AlloUpdate__origamt = Request("origamtA")
Dim cmd_AlloUpdate__incrdecr
cmd_AlloUpdate__incrdecr = ""
if(Request("incrdecrA") <> "") then cmd_AlloUpdate__incrdecr = Request("incrdecrA")
Dim cmd_AlloUpdate__status
cmd_AlloUpdate__status = cb_status
'if(Request("review") <> "") then cmd_AlloUpdate__status = Request("review")
I've made several attempts without any success. Anyone know how to correct this problem?
Any help would be greatly appreciated.
Thanks in advance.
-D-
|
