View Full Version : VBS Arrays and controlling ASP command object.execute()

02-21-2003, 07:00 PM
Being web development my problem involves ASP, VBScrit, HTML... so I don't know where best to post this... but here goes.

I'm using SQL Server 7 on the back end.

I send an HTML form such that a certain SET of fields (i.e. a "record") may or may not get filled in, so these fields may or may not exist in the request.form() when it all gets to the server. However this record set is always sent as a set. I.E. The HTML form may carry zero to many records. ADDITIONALLY, when the record(s) is sent, some fields may be blank (ie. "not required"). In the case of blank fields my intent is to get NULL into the DB.

Of course I want to execute the DB stuffing code only when the record is sent.

The "DB stuffing code" is getting executed even when the record is not sent in the form.

When sent, a blank field doesn't seem to be passing NULL, or anything at all, as a parameter to the stored procedure.

The course of events:
Create arrays - one for each field of the record Note
* the array is not given a specific size.
* the arrays are ALWAYS created - initialized at the beginning of the program.

Dim sReportWebAddrArray()

Populate the Array w/ form data. Note:
* This whole thing will not execute if this field was not sent in the form.
* This field will have been sent, even when blank, if a certain key field exists. This works.
* The array size is adjusted to the amount of data coming in. This works.
* The array element is set to NULL if the incoming field is blank.
* remember request.form arrays start at 1, VBS arrays start at 0.
* all my fields are handled in exactly the same manner. Only the names have been changed to protect the quilty.
* when I imbedd RESPONSE.WRITEs to look at the values as they are created, I get nothing in the right cases, and data when it's there.

if (Request.Form("frptWebAddr") <> "") then
if request.form("frptWebAddr").count > 0 then
reDim sReportWebAddrArray (request.form("frptWebAddr").count-1)

for i=1 to request.form("frptWebAddr").count
if (request.form("frptWebAddr")(i) = "") then
sReportWebAddrArray(i-1) = NULL
sReportWebAddrArray(i-1) = request.form("frptWebAddr")(i)
end if
end if
end if

Execute the SQL stored procedure here's where the problem seems to be Note:
* This loop SHOULD execute only if that key field (array) has data. It always exists, because it was DIMmed.
* The key to the whole problem probably lies in controlling the execution of this loop. It should not execute when the array contains nothing.

for i=0 to Ubound(sReportTitleArray)
' iPerson_ID and iDBOwner_ID are static
cmdReport("cTitle_ID").Value = sReportTitleArray(i)
cmdReport("cWebURL").Value = sReportWebAddrArray(i)
cmdReport("cFmtHard").Value = sReportFmtHardArray(i)
cmdReport("cFmtElec").Value = sReportFmtElecArray(i)
cmdReport("cFmtWeb").Value = sReportFmtWebArray(i)
cmdReport("cFmtNA").Value = sReportFmtNAArray(i)
cmdReport("iReport_ID").Value = sReportKeyArray(i)
cmdReport("cDelete").Value = sReportRemoveArray(i)


I have verified that the fields(s) are (or are not) sent properly. If the key field has data, the entire set is always sent. Some fields may be blank when sent.

The loop executes the proper # of times when data is sent. In these cases however, blank fields don't seem to be sending NULL as a parameter.

The loop executes once when there was no request.form fields sent. That's a bad thing.

When a field is purposefully blank, I get a DB error "expects parameterName which was not supplied". Setting to NULL Doesn't seem to be working.

REDIMming the arrays is working. When there are no fields sent, the array seems to simply be nothing. That is when I attempt to response.write the values (in a loop), the print-out loop (troubleshooting code not shown above) doesn't execute. The labels don't even print. That seems to be a good sign.

All array processing loops are controlled for loops using Ubound(ArrayName) as the termination.

Roy Sinclair
02-21-2003, 09:00 PM
Instead of

if (Request.Form("frptWebAddr") <> "") then


if (Len(Request.Form("frptWebAddr")) > 0) then

What you've getting from Request.Form("frptWebAddr") may not equal "" when the field wasn't sent.

02-21-2003, 09:54 PM
also Trim it. If someone hits space bar in a text field and leaves it blank then its length is > 0 unless it is trimmed.

if (Len(Trim(Request.Form("frptWebAddr"))) > 0) then

02-24-2003, 04:42 PM
Thanks for the ad-vice folks, but before I tried that I tried this:

I reworte this statement (@iReport_ID is the input parameter giving me trouble)

WHERE @iReport_ID = Report_ID)

to this:


Works like a champ:thumbsup:

P.S. Report_ID in the table does not allow NULLs, so this check for not null guarantees I have an new record or not (one I did not fetch from the DB originally).