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.CreateOb ject("ADODB.Con nection")
ConnectionStrin g="DRIVER={Micr osoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath( "../../fpdb/Database.mdb")
objConn.Open ConnectionStrin g

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

for i = 0 to ubound(arrSelec tionID)

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

sqlString = "UPDATE Selection SET
Experience='"&T rim(vrsExperien ce)&"' WHERE SelectionID
="&vrsSelection ID&""

objConn.Execute (sqlString)

next

%>

[Bob Barrows [MVP]]

Re: Array with blank value

Responses inline:
Emmett Power wrote:[color=blue]
> 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.[/color]

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:
[color=blue]
>
> With regards
>
> Emmett Power
>
> CODE
> ----
> DIM arrSelectionID, arrExperience, i
> DIM vrsSelection, Experience
> DIM sqlString
>
> Set objConn = Server.CreateOb ject("ADODB.Con nection")
> ConnectionStrin g="DRIVER={Micr osoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath( "../../fpdb/Database.mdb")[/color]

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:
ConnectionStrin g="Provider=Mic rosoft.Jet.OLED B.4.0;" & _
"Data Source= Server.MapPath( "../../fpdb/Database.mdb")
[color=blue]
> objConn.Open ConnectionStrin g
>
> arrSelectionID= split(request(" SelectionID")," ,")
> arrExperience= split(request(" Experience"),", ")
>
> for i = 0 to ubound(arrSelec tionID)
>
> vrsSelectionID = arrSelectionID( i)
> vrsExperience = arrExperience(i )
>[/color]

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

[color=blue]
> sqlString = "UPDATE Selection SET
> Experience='"&T rim(vrsExperien ce)&"' WHERE SelectionID
> ="&vrsSelection ID&""[/color]

'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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Emmett Power]

Re: Array with blank value


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='Str ongly agree' WHERE SelectionID
=83UPDATE Selection SET Experience='Str ongly agree' WHERE SelectionID =
84

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '2'

ValidatePage3.a sp, 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(vrsExperien ce ) = 0 Then vrsExperience = "Null"

Thanks for your help.

Regards

Emmett Power


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

Re: Array with blank value

Emmett Power wrote:[color=blue]
> 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='Str ongly agree' WHERE SelectionID
> =83UPDATE Selection SET Experience='Str ongly agree' WHERE SelectionID
> = 84
>
> Microsoft VBScript runtime error '800a0009'
>
> Subscript out of range: '2'
>
> ValidatePage3.a sp, 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:[/color]

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(arrExper ience) then
vrsExperience = "Null"
else
vrsExperience = arrExperience(i )
end if

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.