ASP & SQL recordset probelm

[gazzer]

Good morning guys,

before I start, I am not an expert programmer and I do understand that what I am trying to do could be done another way, but here goes.

I have an asp page which is based upon three recordsets as below.

Level1
Level2
Level3
Level2
Level3
Level3
Level3
Level2
Level3
ETC.

Level1 has one record only per session, and is based on a session variable.
===== CODE level1. ======
SELECT LogMain.LogID, LogMain.IncidentTitle FROM LogMain WHERE LogID = '"& SESSION("IDNUM") &"'

Set rstlog = Server.CreateObject("ADODB.Recordset")
rstlog.Open SQLlog, Connstring,3,3
===== END CODE =====

Level2 can have many records and relates to Level1 ID
===== CODE level2. ======
SELECT * FROM LogDetails WHERE LogMainID = '"& SESSION("IDNUM") &"' ORDER BY LogDetailsID desc

Set rstdetails = Server.CreateObject("ADODB.Recordset")
rstdetails.Open SQLdetails, Connstring,3,3
===== END CODE =====

Level3 can have many records and is based on Level2 ID
===== CODE Level3. ======
SELECT * FROM LogDetailsSub WHERE LogDetailsID = '"& rstdetails.fields("LogDetailsID") &"'

Set rstdetailsSUB = Server.CreateObject("ADODB.Recordset")
rstdetailsSUB.Open SQLdetailsSUB, Connstring, 3, 3
===== END CODE =====

Everything is fine up until record 150 of Level2, then for some reason Level3 records do not appear on the web page at all. I am sure there must be a reasonable explanation for this, but I cannot find out why it happens.

I have tried to duplicate the same process in Access 97 and all the records appear as per normal.

It is always at the same number of records of Level2.

Please help me, as I am totally stumped with this problem.

Gazzer

[waj_muller]

[B]Does the page produce an error or just blank.
When you do the leval3 recordset make sure that there is an assigned record to select by.

If there are more than one record in leval2 then in leval3 select statement try moving it to the forst record of leval2 before doing the select statement,else you could be confusing the living daylights out of it.

Try doing leval one and two first,look at the results then.Try forcing an error that will give you an idea where thengs are stopping,having said that,calculated errors at certain points....

[gazzer]

Cheers for your quick reply.

Ok, here goes.

The level1 and level2 recordsets are complete, they all appear on the web page as I would expect.

But the level3 recordset ends at Level2 150 number, so that from 150 onwards the assigned level3 records just do not appear.

Every time I add another level2 record, a level3 sub record is removed from the web page.

I tried using the movefirst, but got an error message.

This is most annoying and has stumped me.

Gazzer.

[dominicall]

Hi gazzer

If I've got this right you're looking to create something that looks like... (I know that it's not code below but was only way I knew how to show the list with the indents)

* Category 1
* Sub Cat 1
- Item 1a
- Item 1b
* Sub Cat 2
- Item 2a
- Item 2b
- Item 2c
- Item 2d
* Category 2
* Sub Cat 1
- Item 1a
- Item 1b
* Sub Cat 2
- Item 2a
- Item 2b

... and you're using SQL Server???

Assuming you are using SQL server the best, quickest and most effiecient way to do this is a process called data shaping. It will only work with SQL Server but it's a great tool for producing categorised lists such as this.

Best place to look is http://www.4guysfromrolla.com/webtech/092599-1.shtml

Has a good explanation of the principles of data shaping and tutorial on how to use it. Once you've used it once you'll never look back when creating categorised lists. I've used it several times on my site with great effect.

Hope this helps

Dominic :D

[gazzer]

That is exactly what I am trying to do, thanks very much to all who have helped me.

I do hope it works.

Gazzer

[dominicall]

No problem gazzer - my pleasure.

The tutorial on 4guys is a good one, but if you have any problems implementing it post the code up and I'll have a look.

Dominic :thumbsup:

[gazzer]

Had a look at the code and it relates to two tables only, I suppose it will be as applicable to three or even four tables as well (as in my case).

Cheers Gazzer.

[dominicall]

Yep - that's what makes it so cool...

The basic data shaping tutorial on 4guys deals with just 2 tables... once you've read through that look at the Advanced Data Shaping tutorial... there's a link to it at the bottom of page 3 of the base tutorial.

The great thing about datashaping is that you can use as many tables as you like (although the most I've used is 4) and create relationships such as child, grandchild, great-grandchild, grea-great-grandchild, etc....

Best thing is to play with it to start with and then use it in the production environment.

Dominic :D

[gazzer]

Yo dominicall

Could you just take a quick look at my SQL code.

It work great, but I can't seem to get rid of duplicate level2 records.

Here it is, sorry for the length of it.

==========SQL CODE ==========

SELECT LogMain.LogID, LogMain.IncidentTitle, LogDetails.LogDetailsID, LogDetails.Time, LogDetails.Message, LogDetailsSub.LogDetailsSubID, LogDetailsSub.ManagerTask, LogDetailsSub.Tasked, LogDetailsSub.Actioned, LogDetailsSub.ProgAreaResponse FROM LogMain INNER JOIN (LogDetails INNER JOIN LogDetailsSub ON LogDetails.LogDetailsID = LogDetailsSub.LogDetailsID) ON LogMain.LogID = LogDetails.LogMainID ORDER BY LogDetails.LogDetailsID DESC

==========END CODE ==========

I tried ORDER BY, but not sure where to put it and which filed to Order on.

Thanks aian.

Gazzer

[waj_muller]

I am sure going to try this,will save a lot of time in coding for me...

Sorry gazzer i am as new to this one as you are,will have a look at your problem though...

[dominicall]

Hmmm.... the select code you'll need to use for the data shaping should be...


SHAPE
{SELECT LogID, IncidentTitle FROM LogMain}
APPEND
({SELECT LogDetailsID, Time, Message FROM LogDetails} AS Details
RELATE LogID = LogMainID),
({SELECT LogDetailsSubID, ManagerTask, Tasked, Actioned, ProgAreaResponse FROM LogDetailsSub} AS SubDetails
RELATE LogDetailsID = LogDetailsID)


Make sure you follow the constructs for the different recordsets in the 4guys tutorial... it can be a bit confusing to start with - the whole concept of recordsets stored within recordsets but it's well worth it.

Keep going - you'll be well pleased when you get it sorted - I was.

Dominic :D

[dominicall]

BTW - the likely cause of the multiple occurences of level 2 categories was the use of an inner join...

The easiest way construct the SQL statements is to use the query build tool is SQL Enterprise Manager.

Then make use of LEFT or RIGHT OUTER JOINS to select the data.

Dominic :D

[gazzer]

I appreciate all your help on this matter very much, and maybe with a litle help I too can sort other peoples problems out.

Gazzer.

:D :thumbsup:

[dominicall]

Like I say - no problem.

Let me know how you get on with it.... if you're still haveing problems zip up the code and post it up here - I'll have a look for you.

Dominic ;)