Array with blank value

[Emmett Power]

Hi,

I have a form with a table with two fields SelectionID and Experience.
I am posting the data to a database using an array function. I have
set out the code below.

The problem I am having is where the Experience field is blank I get
an Microsoft OLE DB Provider for ODBC Drivers error '80040e21'. It
makes sense that a null value would cause the function problems.

I am sure that there is a very simple technique for getting around
this problem but so far my tinkering with the code hasn't come up with
it.

Any suggestions as to how to get around this would be much
appreciated.

With regards

Emmett Power

CODE
----
DIM arrSelectionID, arrExperience, i
DIM vrsSelection, Experience
DIM sqlString

Set objConn = Server.CreateObject("ADODB.Connection")
ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("../../fpdb/Database.mdb")
objConn.Open ConnectionString

arrSelectionID= split(request("SelectionID"),",")
arrExperience= split(request("Experience"),",")

for i = 0 to ubound(arrSelectionID)

vrsSelectionID = arrSelectionID(i)
vrsExperience = arrExperience(i)

sqlString = "UPDATE Selection SET
Experience='"&Trim(vrsExperience)&"' WHERE SelectionID
="&vrsSelectionID&""

objConn.Execute(sqlString)

next

%>

 

[Bob Barrows [MVP]]

Responses inline:

Hi,

I have a form with a table with two fields SelectionID and Experience.
I am posting the data to a database using an array function. I have
set out the code below.

The problem I am having is where the Experience field is blank I get
an Microsoft OLE DB Provider for ODBC Drivers error '80040e21'. It
makes sense that a null value would cause the function problems.

I am sure that there is a very simple technique for getting around
this problem but so far my tinkering with the code hasn't come up with
it.

Any suggestions as to how to get around this would be much
appreciated.

The best solution is to stop using dynamic sql and used save parameter
queries instead. I've posted many examples of calling saved parameter
queries so do a Google search if you are interested. See below for
workarounds with dynamic sql:

With regards

Emmett Power

CODE
----
DIM arrSelectionID, arrExperience, i
DIM vrsSelection, Experience
DIM sqlString

Set objConn = Server.CreateObject("ADODB.Connection")
ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("../../fpdb/Database.mdb")

Nothing to do with your problem, but you should stop using the "OLEDB
Provider for ODBC", which has been deprecated. Use the native Jet OLEDB
Provider instead:
ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= Server.MapPath("../../fpdb/Database.mdb")

objConn.Open ConnectionString

arrSelectionID= split(request("SelectionID"),",")
arrExperience= split(request("Experience"),",")

for i = 0 to ubound(arrSelectionID)

vrsSelectionID = arrSelectionID(i)
vrsExperience = arrExperience(i)

If len(vrsExperience ) = 0 Then vrsExperience = "Null"

sqlString = "UPDATE Selection SET
Experience='"&Trim(vrsExperience)&"' WHERE SelectionID
="&vrsSelectionID&""

'for debugging - uncomment the next line if you have problems
'Response.Write sqlString

<snip>

The above assumes that the Experience column allows Nulls (is not Required)

HTH,
Bob Barrows

 

[Emmett Power]

Bob,

Thanks for the help. We're not quite there though on the array. I've
added the code that you suggest but I am still having problems.

As you can see I've written in the Response.Write sqlString. Assuming
that you have three items on the list and that the first Experience is
blank the error thats coming back is:

"
UPDATE Selection SET Experience='Strongly agree' WHERE SelectionID
=83UPDATE Selection SET Experience='Strongly agree' WHERE SelectionID =
84

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '2'

ValidatePage3.asp, line 22
"

Line 22 is:


vrsExperience = arrExperience(i)

So as you can see if it finds a null value in the array it hangs up
before it gets to:

If len(vrsExperience ) = 0 Then vrsExperience = "Null"

Thanks for your help.

Regards

Emmett Power

 

[Bob Barrows [MVP]]

Bob,

Thanks for the help. We're not quite there though on the array. I've
added the code that you suggest but I am still having problems.

As you can see I've written in the Response.Write sqlString. Assuming
that you have three items on the list and that the first Experience is
blank the error thats coming back is:

"
UPDATE Selection SET Experience='Strongly agree' WHERE SelectionID
=83UPDATE Selection SET Experience='Strongly agree' WHERE SelectionID
= 84

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '2'

ValidatePage3.asp, line 22
"

Line 22 is:


vrsExperience = arrExperience(i)

So as you can see if it finds a null value in the array it hangs up
before it gets to:

It's not finding a "null value in the array": you've got an array that does
not have enough elements.

What you'll need to do is use Ubound to find out if you are attempting to
access a non-existent element:

If i > ubound(arrExperience) then
vrsExperience = "Null"
else
vrsExperience = arrExperience(i)
end if

Bob Barrows