This is a multi-part message in MIME format.



------=_NextPart_000_0013_01C18635.283F24A0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Hello all



I am having a problem with an 'Exception Occurred' error.



I have retrieved two recordsets from 2 related tables and I am trying to 

run

a Do While loop to display data from both recordsets that is related.



Basically i want the data to display like so:



record form table1 --->record form table 2 (with same id)

                   --->record from table 2 (with same id)



record from table1 --->record form table 2 (with same id)

                   --->record from table 2 (with same id)



etc.



everything works fine except that when Table1("id") no longer matches

Table2("id") I am run into an exception Occurred error.



Code as follows:

' CODE TO SELECT THE RECORDSETS

SQL1=3D"SELECT * FROM shows ORDER BY show_id DESC"

SQL2=3D"SELECT * FROM artists ORDER BY show_id DESC"

Dim getdata

DIM getart

set getdata=3DServer.CreateObject("ADODB.Recordset")

set getart=3DServer.CreateObject("ADODB.Recordset")

getdata.open SQL1, conn

getdata.movefirst

getart.open SQL2, conn

getart.movefirst



'CODE TO DISPLAY THE RECORDSETS GROUPED ACCORDING TO LIKE ID'S







<%





Do While not getdata.EOF









response.write  getdata("show_name")

response.write getdata("date_start") &"-"& getdata("date_end")

response.write getdata("show_id")





if getart("show_id") <> getdata("show_id") then

exit do

else



do WHILE getdata("show_id") =3D getart("show_id")



response.write "ARTIST:"& getart("artist_fname") &" "&

getart("artist_lname") &"<br>"

response.write "ARTIST ID:"& getart("id") &"<br>"

getart.movenext

loop

end if

getdata.movenext

loop



%>

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D



How do I compare to like sets without getting this error? As you can see 

I

tried an IF statement to exit the loop but maybe I putting in the wrong

spot?





TIA

spencer

Um, why don't you do a JOIN, and join the Shows table on the artists table.

This means you'd only have 1 recordset, and I think you're logic will be

simpler as well.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Spencer Saunders" <ssaunders@i...>

Subject: [access_asp] Exception Occurred Error





Hello all



I am having a problem with an 'Exception Occurred' error.



I have retrieved two recordsets from 2 related tables and I am trying to run

a Do While loop to display data from both recordsets that is related.



Basically i want the data to display like so:



record form table1 --->record form table 2 (with same id)

                   --->record from table 2 (with same id)



record from table1 --->record form table 2 (with same id)

                   --->record from table 2 (with same id)



etc.



everything works fine except that when Table1("id") no longer matches

Table2("id") I am run into an exception Occurred error.



Code as follows:

' CODE TO SELECT THE RECORDSETS

SQL1="SELECT * FROM shows ORDER BY show_id DESC"

SQL2="SELECT * FROM artists ORDER BY show_id DESC"



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

you can use the filter like this:

<% 





Do While not getdata.EOF

response.write  getdata("show_name")

response.write getdata("date_start") &"-"& getdata("date_end")

response.write getdata("show_id")



getart.filter="showid="&getdata("show_id")

if not getart.eof then

do while not getart.eof 

....

loop

end if



getdata.movenext

loop



%>





> 

> Hello allI am having a problem with an 'Exception Occurred' 

> error.I have retrieved two recordsets from 2 related tables and I am 

> trying to runa Do While loop to display data from both recordsets that is 

> related.Basically i want the data to display like so:record form 

> table1 --->record form table 2 (with same 

> id)                   

> --->record from table 2 (with same id)record from table1 

> --->record form table 2 (with same 

> id)                   

> --->record from table 2 (with same id)etc. everything works 

> fine except that when Table1("id") no longer matchesTable2("id") I am run 

> into an exception Occurred error.Code as follows:' CODE TO SELECT 

> THE RECORDSETSSQL1="SELECT * FROM shows ORDER BY show_id 

> DESC"SQL2="SELECT * FROM artists ORDER BY show_id DESC"Dim 

> getdataDIM getartset 

> getdata=Server.CreateObject("ADODB.Recordset")set 

> getart=Server.CreateObject("ADODB.Recordset")getdata.open SQL1, 

> conngetdata.movefirstgetart.open SQL2, 

> conngetart.movefirst'CODE TO DISPLAY THE RECORDSETS GROUPED 

> ACCORDING TO LIKE ID'S<% Do While not 

> getdata.EOFresponse.write  

> getdata("show_name")response.write getdata("date_start") &"-"& 

> getdata("date_end")response.write getdata("show_id")if 

> getart("show_id") <> getdata("show_id") thenexit doelsedo 

> WHILE getdata("show_id") = getart("show_id")response.write 

> "ARTIST:"& getart("artist_fname") &" "&getart("artist_lname") 

> &"<br>"response.write "ARTIST ID:"& getart("id") 

> &"<br>"getart.movenextloopend 

> ifgetdata.movenextloop%>================How do I 

> compare to like sets without getting this error? As you can see Itried an IF 

> statement to exit the loop but maybe I putting in the 

> wrongspot?TIAspencer




> 

>  

----------------------------------------------

»¶Ó­Ê¹Óà km169 µç×ÓÓʼ
ϵͳ http://email.km169.net

Thank you for useing km169 Email system

Ken;



The problem with the inner join is that it retrieves too many instances of

the same show because there could be 1 show listed in Table 1 with 16

corresponding records in table two. So when I output the records I get

something that looks like this:



show1 ---->artist1

show1---->artist2

show1----->artist3



when I really just want this:



show1 --->artist1, artist2, artist3.



How would I go about stepping through the records when each instance of an

artist has it's own show attached to it (but they're duplicates). Does that

make sense? I can't think of another way to explain it.







----- Original Message -----

From: Ken Schaefer <ken@a...>

To: Access ASP <access_asp@p...>

Sent: December 16, 2001 6:37 PM

Subject: [access_asp] Re: Exception Occurred Error





> Um, why don't you do a JOIN, and join the Shows table on the artists

table.

> This means you'd only have 1 recordset, and I think you're logic will be

> simpler as well.

>

> Cheers

> Ken

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Spencer Saunders" <ssaunders@i...>

> Subject: [access_asp] Exception Occurred Error

>

>

> Hello all

>

> I am having a problem with an 'Exception Occurred' error.

>

> I have retrieved two recordsets from 2 related tables and I am trying to

run

> a Do While loop to display data from both recordsets that is related.

>

> Basically i want the data to display like so:

>

> record form table1 --->record form table 2 (with same id)

>                    --->record from table 2 (with same id)

>

> record from table1 --->record form table 2 (with same id)

>                    --->record from table 2 (with same id)

>

> etc.

>

> everything works fine except that when Table1("id") no longer matches

> Table2("id") I am run into an exception Occurred error.

>

> Code as follows:

> ' CODE TO SELECT THE RECORDSETS

> SQL1="SELECT * FROM shows ORDER BY show_id DESC"

> SQL2="SELECT * FROM artists ORDER BY show_id DESC"

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

>




$subst('Email.Unsub').

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Spencer Saunders" <ssaunders@i...>

Subject: [access_asp] Re: Exception Occurred Error





: Ken;

:

: The problem with the inner join is that it retrieves too many instances of

: the same show because there could be 1 show listed in Table 1 with 16

: corresponding records in table two. So when I output the records I get

: something that looks like this:

:

: show1 ---->artist1

: show1---->artist2

: show1----->artist3

:

: when I really just want this:

:

: show1 --->artist1, artist2, artist3.

:

: How would I go about stepping through the records when each instance of an

: artist has it's own show attached to it (but they're duplicates). Does

that

: make sense? I can't think of another way to explain it.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



You could use some logic in your ASP to deal with this.



<%

If not objRS.EOF then



    lngShowID = objRS("ShowID")



    Do While Not objRS.EOF



        If objRS("ShowID") = lngShowID

            ' We are still in the same show

            ' Write out Artist's details

        Else

            ' We are up to the next show

            ' Close the former HTML table, start new HTML table

            ' update lngShowID

            ' write out artist's details

        End If



        objRS.movenext



    Loop



End If

%>



It really helps if you write out the logic for your page (eg on a piece of

paper) before you start writing out your code. It makes it 10x easier to

write code because you have some idea of what needs to happen when...



Cheers

Ken

Thanks so much ken,

the logic works brilliantly. I just have one more question though.- Using

your logic as a  plan I got the table  generated the way I wanted but as

soon as I changed the ORDER BY clause in my SQL from ASC to DESC I  get an

error:



"ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested

operation requires a current record"



It's fine when it's ordered ASCENDING.



Why should the code have to be written any differently when the RS is

ordered another way>?



Thanks for all your help so far.



spence





Code as follows:

=================================

SQL="SELECT * from shows, artists WHERE shows.show_id = artists.show_id

ORDER BY shows.show_id DESC"





<table border=1 bordercolor="#ACB1BF" cellpadding=2 cellspacing=0

width=100%>

<%



Do While not getdata.EOF



%>





<tr><td bgcolor='#333333'><font face='arial' size=2 color='white'>

<%

response.write"Show name: <b>"& getdata("show_name") &"</b> SHOW ID:"&

getdata("show_id") %>



</font></td></tr><tr><td bgcolor='#999999'>

<font face='arial' size=1 color='white'>

<%RESPONSE.WRITE "SHOW DATES:"& getdata("date_start") &"-"&

getdata("date_end") &"</font></td></tr>"





'===========================

' AS LONG AS THERE ARE STILL RECORDS TO SORT THROUGH.....

'==============================

if NOT getdata.eof then



varshow=getdata("show_id")

'=============================

'CREATE A LOOP THAT ONLY EXISTS IF THERE ARE RECORDS

'============================

do while NOT getdata.eof



'============================

'TYPE OUT ARTIST INFORMATION AS LONG AS THE CURRENT RECORD IS = TO THE

VARIABLE WE SET ABOVE- BUT STILL STEP THROUGH THE RECORDSET

'============================

if varshow=getdata("show_id") then



response.write "<tr><td>ARTIST ID:"& getdata("id") &"</td></tr>"

response.write "<tr><td>ARTIST:"& getdata("artist_fname") &" "&

getdata("artist_lname") &"<br>"

response.write "MEDIUM:"& getdata("medium") &"</td></tr>"

'====================

'IF THE VARIABLE DOESN'T MATCH THE CURRENT SHOW_ID THEN EXIT THE LOOP AND

START AGAIN

'======================

else

exit do

end if





getdata.movenext



loop

end if

'===================

'CONTINUE CYCLING THROUGH THE RECORDSET

'=====================

getdata.movenext



loop







%>

</table>





======================================









----- Original Message -----

From: Ken Schaefer <ken@a...>

To: Access ASP <access_asp@p...>

Sent: December 16, 2001 11:19 PM

Subject: [access_asp] Re: Exception Occurred Error





> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Spencer Saunders" <ssaunders@i...>

> Subject: [access_asp] Re: Exception Occurred Error

>

>

> : Ken;

> :

> : The problem with the inner join is that it retrieves too many instances

of

> : the same show because there could be 1 show listed in Table 1 with 16

> : corresponding records in table two. So when I output the records I get

> : something that looks like this:

> :

> : show1 ---->artist1

> : show1---->artist2

> : show1----->artist3

> :

> : when I really just want this:

> :

> : show1 --->artist1, artist2, artist3.

> :

> : How would I go about stepping through the records when each instance of

an

> : artist has it's own show attached to it (but they're duplicates). Does

> that

> : make sense? I can't think of another way to explain it.

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

> You could use some logic in your ASP to deal with this.

>

> <%

> If not objRS.EOF then

>

>     lngShowID = objRS("ShowID")

>

>     Do While Not objRS.EOF

>

>         If objRS("ShowID") = lngShowID

>             ' We are still in the same show

>             ' Write out Artist's details

>         Else

>             ' We are up to the next show

>             ' Close the former HTML table, start new HTML table

>             ' update lngShowID

>             ' write out artist's details

>         End If

>

>         objRS.movenext

>

>     Loop

>

> End If

> %>

>

> It really helps if you write out the logic for your page (eg on a piece of

> paper) before you start writing out your code. It makes it 10x easier to

> write code because you have some idea of what needs to happen when...

>

> Cheers

> Ken

>

>

>




$subst('Email.Unsub').

The last call to getdata.movenext (two lines from the bottoms) is causing

your code to bomb. The End If immediately above is matched to:



 '===========================

: ' AS LONG AS THERE ARE STILL RECORDS TO SORT THROUGH.....

: '==============================

: if NOT getdata.eof then



so, if the code has passed that End If, then the Recordset is .EOF, and you

can't call .movenext

If you want to keep the code you have at the moment wrap the last .movenext

inside a conditional:



<%

    End If



    '===================

    'CONTINUE CYCLING THROUGH THE RECORDSET

    '=====================

    If not objRS.EOF then objRS.movenext



Loop

%>



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Spencer Saunders" <ssaunders@i...>

Subject: [access_asp] Re: Exception Occurred Error





: Thanks so much ken,

: the logic works brilliantly. I just have one more question though.- Using

: your logic as a  plan I got the table  generated the way I wanted but as

: soon as I changed the ORDER BY clause in my SQL from ASC to DESC I  get an

: error:

:

: "ADODB.Recordset error '800a0bcd'

: Either BOF or EOF is True, or the current record has been deleted.

Requested

: operation requires a current record"

:

: It's fine when it's ordered ASCENDING.

:

: Why should the code have to be written any differently when the RS is

: ordered another way>?

:

: Thanks for all your help so far.

:

: spence

:

:

: Code as follows:

: =================================

: SQL="SELECT * from shows, artists WHERE shows.show_id = artists.show_id

: ORDER BY shows.show_id DESC"

:

:

: <table border=1 bordercolor="#ACB1BF" cellpadding=2 cellspacing=0

: width=100%>

: <%

:

: Do While not getdata.EOF

:

: %>

:

:

: <tr><td bgcolor='#333333'><font face='arial' size=2 color='white'>

: <%

: response.write"Show name: <b>"& getdata("show_name") &"</b> SHOW ID:"&

: getdata("show_id") %>

:

: </font></td></tr><tr><td bgcolor='#999999'>

: <font face='arial' size=1 color='white'>

: <%RESPONSE.WRITE "SHOW DATES:"& getdata("date_start") &"-"&

: getdata("date_end") &"</font></td></tr>"

:

:

: '===========================

: ' AS LONG AS THERE ARE STILL RECORDS TO SORT THROUGH.....

: '==============================

: if NOT getdata.eof then

:

: varshow=getdata("show_id")

: '=============================

: 'CREATE A LOOP THAT ONLY EXISTS IF THERE ARE RECORDS

: '============================

: do while NOT getdata.eof

:

: '============================

: 'TYPE OUT ARTIST INFORMATION AS LONG AS THE CURRENT RECORD IS = TO THE

: VARIABLE WE SET ABOVE- BUT STILL STEP THROUGH THE RECORDSET

: '============================

: if varshow=getdata("show_id") then

:

: response.write "<tr><td>ARTIST ID:"& getdata("id") &"</td></tr>"

: response.write "<tr><td>ARTIST:"& getdata("artist_fname") &" "&

: getdata("artist_lname") &"<br>"

: response.write "MEDIUM:"& getdata("medium") &"</td></tr>"

: '====================

: 'IF THE VARIABLE DOESN'T MATCH THE CURRENT SHOW_ID THEN EXIT THE LOOP AND

: START AGAIN

: '======================

: else

: exit do

: end if

:

:

: getdata.movenext

:

: loop

: end if

: '===================

: 'CONTINUE CYCLING THROUGH THE RECORDSET

: '=====================

: getdata.movenext

:

: loop

:

:

:

: %>

: </table>



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~