Catch-22 With Error Trapping

[DonP]

Hi All!

I'm kind of stuck! I have an Access database that is being queried via a series of VB scripts and it is the last one that gives details where I am occasionally having a problem. There are two tables between which a comparison is made on two of the fields. One table, the main one with the majority of the information, is for a specific record ID, while the other has about fifteen records where it needs to choose a match on one field. The problem is, occasionally there is not a match, such as when a new record is added to the main table but before it has a match in the second. Other times it is just a plain typo that causes the problem and, since we do not provide the data ourselves, we can only proof read it to a point when it comes to us.

When I try to catch the problem with if statements, I need to use an rs but when it doesn't exist it still crashes. Originally is was in the sql statement itself where the match was being made, which crashed too if there wasn't a match. I thought by moving it outside the sql statement, there would be a better chance of catching the error! It is the fact that I seem to need an dpval = rs("dp&quot in the if statement that crashes it when there isn't a match! Any help is appreciated as I've been pulling my hair out over this one!

Don

[tt]sql = "SELECT [HELPSCC ORIGINATING AGENCY].[Data Provider] AS [DP], agency.[Originating Agency] AS [OA] FROM [HELPSCC ORIGINATING AGENCY], agency WHERE [AGENCY].[ID] = " & AID
set rs = Server.CreateObject("ADODB.Recordset&quot
rs.Open sql, conn

dpval = rs("DP&quot
oaval = rs("OA&quot

if dpval <> oaval then
rs.MoveFirst
while not rs.eof and dpval <> oaval

if dpval <> oaval then
rs.movenext
dpval = rs(&quot;dp&quot
end if
rs.MoveNext
wend[/tt] [sig]<p>Don<br><a href=mailto:don@ctagroup.org>don@ctagroup.org</a><br><a href=

http://www.helpscc.org/>HelpSCC

- Health & Human Services Database</a><br>Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)[/sig]

 

[DonP]

Oops! I had a little typo in my loop causing it to give odd results. Here is what it should say:

[tt] dpval = rs(&quot;DP&quot
oaval = rs(&quot;OA&quot

if dpval <> oaval then
rs.MoveFirst
while not rs.eof and oaval <> rs(&quot;dp&quot
if oaval <> rs(&quot;dp&quot then
rs.movenext
dpval = rs(&quot;dp&quot
end if
wend
end if[/tt]

This works fine unless there is no match at all and that is the part I can't figure out! The first dpval = rs(&quot;DP&quot seems to cause no problems but the rs(&quot;DP&quot in the loop does if the record set does not have a match.

Don [sig]<p>Don<br><a href=mailto:don@ctagroup.org>don@ctagroup.org</a><br><a href=

http://www.helpscc.org/>HelpSCC

- Health & Human Services Database</a><br>Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)[/sig]

 

[khorjak]

It's probably crashing because you're issuing a rs.MoveFirst before checking for rs.eof. Try checking for eof first (no match). [sig]<p>Choo Khor<br><a href=mailto:choo.khor@intelebill.com>choo.khor@intelebill.com</a><br>[/sig]

 

[DonP]

Thanks!

I was hoping it was something as simple as that, but when I tried moving it up, the script times out (I left the original remmed out where it had been, for reference):

[tt]if dpval <> oaval then
while not rs.eof and oaval <> rs(&quot;dp&quot
rs.MoveFirst
' while not rs.eof and oaval <> rs(&quot;dp&quot
if oaval <> rs(&quot;dp&quot then
rs.movenext
dpval = rs(&quot;dp&quot
end if
wend
end if[/tt]

Also tried it as:

if dpval <> oaval then
while not rs.eof
rs.MoveFirst
' while not rs.eof and oaval <> rs(&quot;dp&quot
if oaval <> rs(&quot;dp&quot then
rs.movenext
dpval = rs(&quot;dp&quot
end if
wend
end if

I also tried a slight variation like this, but it still crashes when there is no match and this one times out when there is a match:

[tt]if dpval <> oaval then
while not rs.eof
rs.MoveFirst
while not rs.eof and oaval <> rs(&quot;dp&quot
if oaval <> rs(&quot;dp&quot then
rs.movenext
dpval = rs(&quot;dp&quot
end if
wend
wend
end if[/tt]

Then I tried a slightly different one yet, which functionally put it back the way it was in my last post where it works as long as there is a match, but not if there is not:

[tt]if dpval <> oaval then
while not rs.eof and oaval <> rs(&quot;dp&quot
rs.MoveFirst
while not rs.eof and oaval <> rs(&quot;dp&quot
if oaval <> rs(&quot;dp&quot then
rs.movenext
dpval = rs(&quot;dp&quot
end if
wend
wend
end if[/tt]

Basically the record set contains two columns: one with different values in each field, and the other with all the same value in each field. It works fine as long as there is a match somewhere but crashes if there is not. There will never be more than one match but there are times when there is none.

Don [sig]<p>Don<br><a href=mailto:don@ctagroup.org>don@ctagroup.org</a><br><a href=

http://www.helpscc.org/>HelpSCC

- Health & Human Services Database</a><br>Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)[/sig]

 

[DonP]

I forgot to say that the initial value for dpval is equal to the first entry in the record set. It is only when I start specifying a record set call in the loop that the script begins to have problems. Without the loop and record set calls, it doesn't crash at all but of course, there is only a match when the item I select coincides with the first line in the record set.

Don [sig]<p>Don<br><a href=mailto:don@ctagroup.org>don@ctagroup.org</a><br><a href=

http://www.helpscc.org/>HelpSCC

- Health & Human Services Database</a><br>Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)[/sig]

 

[NickBulka]

why specify rs.movefirst? aren't you already at the first record?

FYI: a trick I learned a long time ago is to append an empty string to values you retrieve from a recordset. If the field is null, appending the empty string gets you around the &quot;invalid use of null&quot; problem.

For example:

instead of

dpval = rs(&quot;dp&quot

use

dpval = rs(&quot;dp&quot & &quot;&quot;
[sig]<p>nick bulka<br><a href=mailto: > </a><br>[/sig]

 

[DonP]

Hi Nick,

I had already tried that since I saw it in another post, but it caused an &quot;Exception Occurred&quot; error on the line with the & &quot;&quot; . If I add it to the earlier line with the rs(&quot;dp&quot, I then get the error on that line. Here it is:

[tt]if dpval <> oaval then
while not rs.eof and oaval <> rs(&quot;dp&quot
if oaval <> rs(&quot;dp&quot then
rs.movenext
dpval = rs(&quot;dp&quot & &quot;&quot;
end if
wend
end if[/tt]

Still, if there IS a match, it works fine!

What about converting the record set into an array? Wouldn't it be dealing with just text then and if there is no match, then it simply wouldn't match but not crash? I saw another post with that idea but I am just a beginner as VBscript so wasn't sure how to do it or even if it would work.

Don [sig]<p>Don<br><a href=mailto:don@ctagroup.org>don@ctagroup.org</a><br><a href=

http://www.helpscc.org/>HelpSCC

- Health & Human Services Database</a><br>Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)[/sig]

 

[NickBulka]

Don,
Appending one string to another should not cause an exception. (by the way, I'm asuming a non-numeric field). I have been doing this since VB2 with great success. However, I can't explain what else might cause the exception.

What's the exact error message? [sig]<p>nick bulka<br><a href=mailto: > </a><br>[/sig]

 

[DonP]

Hi Nick,

Yes, it's text only in all fields. The problem of course is when there is not a match, which is what I started with two weeks ago! It worked and still works fine when there is a match. Here is the full error:

[tt]error '80020009'
Exception occurred.

/helpscc/cgi-bin/details.asp, line 32 [/tt]

Don [sig]<p>Don<br><a href=mailto:don@ctagroup.org>don@ctagroup.org</a><br><a href=

http://www.helpscc.org/>HelpSCC

- Health & Human Services Database</a><br>Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)[/sig]

 

[NickBulka]

Don,
Have you tried Microsoft's support site? Go to support.microsoft.com and enter &quot;80020009&quot; as your search criteria. There are a lot of known problems listed. maybe one is yours.

nick bulka

 

[DonP]

Nick,

I hadn't had a chance to do so since this is the first I saw this error. But now that you reminded me, I did it but it couldn't find a match.

Don

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[Guest_imported]

I'm not sure that this can cause the problem and, maybe, missing a point here, but why do you need if...then in your while loop?

if dpval <> oaval then
rs.MoveFirst
while not rs.eof and oaval <> rs(&quot;dp&quot
[red]if oaval <> rs(&quot;dp&quot then[/red]
rs.movenext
dpval = rs(&quot;dp&quot
[red]end if[/red]
wend
end if
You're checking this condition in the while statement?!
And I think rs.movenext and
dpval = rs(&quot;dp&quot
need to switch places.

 

[DonP]

Hi guestg,

That was my manager's idea and at the time, it made sense. Later on, I was wondering the same thing. To me it is redundant. So in other words, I'm not sure why it's there! You are right that it doesn't need to be there, though it is not what is causing the current problem.

Don

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[Guest_imported]

Hi, Don!
The other thing I was wondering about is that you're saying that this code works fine with matches; but it does something (rs.movenext) only if the fields' values don't match.What does it do if the values do match?

 

[DonP]

Hi! If the values match, it goes on to close the record set and then another sql statement is called which pulls up the actual data from the main database and presents it on the screen. If there is not a match, it is my intention to have it use a different sql statement yet that does not call on the rs(&quot;dp&quot value. That part has been tested and is all working. It is just the loop on a record set with no value that is keeping it from going that far right now, though I can force it by removing the entire loop.

The URL is at the bottom of all these postings if you want to see for yourself. Just select a search icon, then another (please note that a couple of the buttons just load an HTML file, though most do not. You should be able to see the difference by the speed with which it loads), then select something from the resulting list. The details page has a line way down at the bottom where it says &quot;Data Provided By:&quot; that causes the crash when there is no value. I've already taked care of the bottom part though so now it is only the loop that needs the repair.

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

One more thing in case it helps - basically this loop is only to find out if there is a match or not between the two tables. What the values are doesn't matter at this point because another sql statement will take care of that part.

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

Since I am only trying to compare and don't care about the actual data values themselves, I am giving it a try as an array. But I am not sure exactly how to do it. I'm not even sure if this is the say to go or not and if it is, I don't know how to return a usable value either to my dpval and oaval, or to something else that would indicate if there is a match or not. Here is what I have so far and I would appreciate any more help that can be given:

[tt]
dpval = &quot;&quot;
oaval = &quot;&quot;

' Get Originating Agency / Data Provider Recordset, return as an Array
Function FetchOriginatingAgency
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;HelpSCCDB&quot;,&quot;&quot;,&quot;&quot;
sql = &quot;SELECT [HELPSCC ORIGINATING AGENCY].[Data Provider] AS [DP], agency.[Originating Agency] AS [OA] FROM [HELPSCC ORIGINATING AGENCY], agency WHERE [AGENCY].[ID] = &quot; & AID
Dim rs
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open &quot;rs.Open sql, conn&quot;
FetchOriginatingAgency = rs.GetRows() ' Return data as an Array
rs.close
Set rs = Nothing
End Function
[/tt]

The script is in fact moving past this part so the syntax is probably fairly close, but without values for my two variables, I can't tell if it is correct or not. The general idea came from Microsoft's Web site.

Don

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)