Wrox Programmer Forums
|
ASP Pro Code Clinic As of Oct 5, 2005, this forum is now locked. No posts have been deleted. Please use "Classic ASP Professional" at: http://p2p.wrox.com/forum.asp?FORUM_ID=56 for discussions similar to the old ASP Pro Code Clinic or one of the other many remaining ASP and ASP.NET forums here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP Pro Code Clinic section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 17th, 2005, 10:06 PM
Authorized User
 
Join Date: Apr 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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-


 
Old February 18th, 2005, 09:40 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

;;;I am geting a subscript out of range error because the fieldnames in my update statement are no longer valid

How do you know this?
What do you mean by no longer valid?
If fields in your update statement are no longer valid, shouldnt these be changed?

This error usually occurs when you are referencing an element of an array that does not exist.

Wind is your friend
Matt





Similar Threads
Thread Thread Starter Forum Replies Last Post
sp update error 1222 Gunny SQL Server 2000 1 August 21st, 2006 12:47 PM
Error when trying to use an UPDATE statement wayne62682 Access VBA 2 April 4th, 2006 07:50 PM
Update Statement some error thas123 ADO.NET 1 January 8th, 2006 10:30 AM
Syntax Error in the update statement annie_t Access ASP 1 February 1st, 2005 12:48 PM
UPDATE statement returns syntax error AviatorTim Classic ASP Databases 2 January 8th, 2004 05:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.