I have a table in Access, and I want to figure out how I could in VBsc 2

Karl Blessing · Jun 29, 2000

I have a table in Access, and I want to figure out how I could in VBscript recursivly go through the table, steps are checking the HParents and LParents for 0,0 then under the first one, find all children, and for each childeren find under that as well like below should return something like: ID1¦HID¦ID¦Name¦Type ¦Val  ¦Label           ¦HParent¦LParent ------------------------------------------------------------ 7  ¦1  ¦Labor  ¦Check¦Labor¦Labor           ¦0      ¦0 7  ¦2  ¦LFD    ¦Check¦LFD  ¦Labor Force Data¦7      ¦1 7  ¦3  ¦LFDS   ¦Check¦LFDS ¦State           ¦7      ¦2 7  ¦4  ¦LFDN   ¦Check¦LFDN ¦National        ¦7      ¦2 7  ¦5  ¦LCD    ¦Check¦LCD  ¦Labor Cost Data ¦7      ¦1 7  ¦6  ¦LCDW   ¦Check¦LCDW ¦Wages           ¦7      ¦5 7  ¦7  ¦LCDE   ¦Check¦LCDE ¦Employment      ¦7      ¦5 7  ¦8  ¦LCDWR  ¦Check¦LCDWR¦Wage Ratios     ¦7      ¦5 into: ... <input type="check" name="Labor"> Labor </input>     <input type="check" name="LFD"> Labor Force Data </Input>         <input type="check" name="LFDS"> State </input>         <input type="check" name="LFDS"> National </input>     <input type="check" name="LCD"> Labor Cost Data </input>         <input type="check" name="LCDW"> Wages </input>         <input type="check" name="LCDE"> Employment </input>         <input type="check" name="LCDWR"> Wage Ratios </input> ... of cource the actual output, may be in tables, forms, etc to make it into a treelike (or if its IE only, into like a pulldown) but I need something to rescursivly go through , and spit out from top to bottom, at the most it might use 4 recordsets on a stack at a time I would think (rescursive is calling itself right?) any help is appreciated. Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

unicorn11 · Jun 29, 2000

Use sql to sort the table first when you are retriving it and then use its sequence and create the checkboxes Unicorn11 <a href=mailto:webmaster@tripmedia.com>webmaster@tripmedia.com</a> <a href= > </a> Hi there it all likeminded fellows!!!!!!

Karl Blessing · Jun 30, 2000

how in sql am I going to be able to sort the HParent and the LParent , so that it's exactly like a tree, and not have just one sorted, for example, all of the ones with 0,0 dont all go on top, those are the higest points in the tree, then all the other ones tell which parent it's under. Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

unicorn11 · Jun 30, 2000

select * from your table name order by HParent, LParent; the order by statement will help you get the order that you want ! Unicorn11 <a href=mailto:webmaster@tripmedia.com>webmaster@tripmedia.com</a> <a href= > </a> Hi there it all likeminded fellows!!!!!!

Karl Blessing · Jun 30, 2000

I knew about the order by, didnt know you could do two like that, anyway possible to make it do 0,0 children of this 0,0 children of this instead of 0,0 0,0 0,0 then all the children. Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

palbano · Jun 30, 2000

Dear Karl, Not with the schema you desribed. It can be done by using a MIB style UID in a column but you don't have one. It would need to be supplied by programming logic at the row creation time knowing the relationship to the other rows. What you could do is create a 'Node' class in your script (yes... object oriented script code) that would be searchable by Node.id the id could be synthesized using HParent.LParent.HID, if I understand your schema correctly. Then as each Node instance is created from a row it's parent could be located in memory and the new Node added to it's children collection. By ordering the recordset as Unicorn11 states and Adding HID as a final column, i.e., order by HParent, LParent, HID the parent nodes would be guaranteed to exist in memory prior to any of the children's rows being read from the recordset. One time through the recordset would build the entire tree structure in memory. The in memory Nodes could then be walked to build your HTML in any fashion you like. -pete

macr0 · Jul 3, 2000

I wrote a message board that used the same kind of technique your describing. in a Hierarchal structure display. basically what you can do is put your recordset into a 2 dimensional array using objRs.getrows for speed. I used two functions, both of them looped through the array. the first function deals with all the parents If(data(Hparent, i) = 0 and data(Lparent, i) = 0) then   do stuff for parent. basicly, you check if the parent has children, if they do you pass the parents' ID's, and any other data to the second function, which writes whatever you want and recurses based on whatever (like if it has siblings or children of its own). Note that it is substantially faster to use the array instead of recordsets when you're applying a lot of logic in your ASP pages. (like in my case, I usually had to loop the array within loops and such). Hope this helped J Lowery

Karl Blessing · Jul 3, 2000

would you perhaps know, or have an algorithm, that can stuff all the rows into an array? Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Karl Blessing · Jul 3, 2000

this is what I am trying to use so far, but I keep getting this error and I cant figure it out: Labor,Check Select * from Tree where HParent = 7 and LP = 1 Microsoft OLE DB Provider for ODBC Drivers error '80040e10' [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. /wsDianostic/Tree.asp, line 15 The Code: <%@ Language=VBScript %> <% Response.Buffer = True %> <HTML> <HEAD> </HEAD> <BODY> <% sub showkids(Hp, Lp)     Dim RSKid     Set RSKid = Server.CreateObject("ADODB.Recordset&quot

KidSql = "Select * from Tree where HParent = " & Hp & " and LP = " & Lp     Response.Write KidSql & vbcrlf     RSKid.Open KidSql,Conn, 3, 1     if RSKid.EOF then         RSKid.MoveFirst         while not RSKid.EOF             Response.Write vbtab & "    " & RSKid("Name&quot

& "," & RSKid("Type&quot

& " " & vbcrlf             RSKid.MoveNext         wend         RSKid.Close     End if     Set RSKid = nothing end sub Dim RS Dim Conn Set Conn = Server.CreateObject("ADODB.Connection&quot

Set RS = Server.CreateObject("ADODB.Recordset&quot

Conn.Open "Tree", "sa", "" RS.Open "Select * from Tree order by HParent, LParent",Conn, 3,1 If not RS.EOF then     RS.MoveFirst     while not RS.EOF         if( RS("HParent&quot

= 0 and RS("LParent&quot

= 0) then             Response.Write RS("Name&quot

& "," & RS("Type&quot

& " " & vbcrlf             Response.Write "<!-- (" & RS("HID&quot

& "," & RS("ID&quot

& &quot

-->" & vbcrlf             showkids RS("HID&quot

, RS("ID&quot

end if         RS.MoveNext     wend     RS.Close     Set RS = nothing end if Conn.Close Set Conn = nothing Response.Flush %> </BODY> </HTML> right now I'm just trying to figure out how I'm going to read and organize this, but that errors been buggin me. Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

palbano · Jul 3, 2000

Karl, In your sub routine named 'showkids' you are using a variable named 'Conn' as a parameter to the recordset Open method. Within the scope of the subroutine there is no variable named 'Conn' that is initialized to anything therefore the equivalent of NULL is being passed as a parameter. On the issue of design if you use the approach I delineated in my earlier post it will be much more efficient than multiply querying the table. Also I saw your related post in the SQL forum and again, as I posted earlier you cannot order the rows in the 'tree' node relationship you desire with the given schema. As I stated if the row creation routine can be altered to provide a MIB ID style column value based on the relationships then that column alone would provide the tree node relationship sort order ability. I have done this before when I authored an ASP forum application. It is actually quite simple, however it DOES require code at row creation time to generate the MIB id's. "But, that's just my opinion... I could be wrong". -pete

Karl Blessing · Jul 4, 2000

I would if I knew how in Vbscript. Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

macr0 · Jul 4, 2000

As palbano said, your Conn variable is null unless you have a definition for it elsewhere. It's the connection string that's either a DSN, Driver UID Pwd, or Provider UID Pwd. Another small problem I see comes right after the Open Method call. if RSKid.EOF then         RSKid.MoveFirst IF RSKid.EOF is true Right after opening the Recordset, then the recordset is empty. if this is true, none of your future calculations would work pretaining to the record set. something like if RSKid.EOF then         Response.Write "There are no Records."         Exit Sub End If Would be a better choice. Also, there is no algorthim to put your rows in an array. you just use the GetRows Method of the recordset. Dim aryData aryDate = RSKid.GetRows For the parent relationship, you could write a little algorithim to sort through the array and do what you want. aryDate is now a two dimensional array storing all of the values of your recordset. This method is good if you don't need to  do any changes to the recordset at the time of displaying it ( adOpenForwardOnly, adLockReadOnly). Hope This Helps J Lowery

Karl Blessing · Jul 4, 2000

I meant to say If not RSKid.Eof then on that one. Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Karl Blessing · Jul 5, 2000

I'm trying out the getrow thing, and I have something like: AryData = RS.GetRows Response.Write UBound(AryData, 2) for j = 0 to UBound(AryData, 2)     Response.Write AryData(j,1) & " " & vbcrlf     Response.Write AryData(j,2) & " " & vbcrlf next and in that section, I keep getting a Subscript out of range when it has reached a number, that doesnt have a field attatch(trys to goto the end of the row) when I had it tell me the UBound, I get 9821. am I going about this wrong(at the moment just trying to work with getrow, then see if i can put it to some more productive use) Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Karl Blessing · Jul 5, 2000

I think I figured out a way just to look at the data AryData = RS.GetRows Response.Write UBound(AryData,2) for j = 0 to UBound(AryData, 2)     for i = 0 to 8 'number of fields in my table         Response.Write AryData(i,j) & " "     next     Response.Write " " & vbcrlf next RS.Close Set RS = nothing end if Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

macr0 · Jul 5, 2000

the reason you get the subscript out of range error is because you have your subscripts out of order. I personal use constants for the column names so I don't have to remember numbers and such. const strcolumnname = 1 For rowcounter = 0 to Ubound(AryData,2) Resoponse.Write AryData(strcolumnname, rowcounter) Next

Karl Blessing · Jul 5, 2000

I already figured that out in the post right before yours, now trying to figure out how to pass the array data to a subroutine, for example sub showkids(byref theObj, Hp, Lp)     Response.Write "<Table Border=1 align=center>" & vbcrlf     for j = 0 to UBound(TheObj, 2)         If theObj(7,j) = Hp and theObj(8,j) = Lp then             Response.Write vbtab & "<TR>" & vbcrlf             for i = 1 to 8                 Response.Write vbtab & vbtab & "<TD>" & TheObj(i,j) & "</TD>" & vbcrlf             next             Response.Write vbtab & "</TR>" & vbcrlf         end if     next end sub sub showparents(byref theObj)     Response.Write "<Table Border=1 align=center>" & vbcrlf     for j = 0 to UBound(thObj, 2)         If theObj(7,j) = 0 and theObj(8,j) = 0 then             Response.Write vbtab & "<TR>" & vbcrlf             for i = 1 to 8                 Response.Write vbtab & vbtab & "<TD>" & TheObj(i,j) & "</TD>" & vbcrlf             next             Response.Write vbtab & "</TR>" & vbcrlf         end if     next end sub Microsoft VBScript runtime error '800a000d' Type mismatch: 'UBound' /wsDianostic/Tree.asp, line 27 Rest of code being used (ie main code) Dim RS Dim Conn Dim AryData Set Conn = Server.CreateObject("ADODB.Connection&quot

Set RS = Server.CreateObject("ADODB.Recordset&quot

Conn.Open "Tree", "sa", "" RS.Open "Select * from Tree order by HParent, LParent",Conn, 3,1 If not RS.EOF then     RS.MoveFirst     AryData = RS.GetRows     RS.Close     Set RS = nothing end if Conn.Close Set Conn = nothing showparents(AryData) ' if RS was EOF, I would get an erorr I'm aware of this. set AryData = nothing Response.Flush Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Karl Blessing · Jul 5, 2000

I got it to work for the UBound error, apparently , it will work if I put the Ubound into a variable first, then use it in the for loop. sub showkids(byref theObj, HpLoc, LpLoc)     Response.Write "<Table Border=1 align=center>" & vbcrlf     Dim KidRow     KidRow = UBound(theObj, 2)     for j = 0 to KidRow         If theObj(HP,j) = HpLoc and theObj(LP,j) = LpLoc then             Response.Write vbtab & "<TR>" & vbcrlf             for i = 1 to 8                 Response.Write vbtab & vbtab & "<TD>" & TheObj(i,j) & "</TD>" & vbcrlf             next             Response.Write vbtab & "</TR>" & vbcrlf         end if     next end sub sub showparents(byref theObj)     Response.Write "<Table Border=1 align=center>" & vbcrlf     Dim ParentRow     ParentRow = UBound(theObj, 2)     for j = 0 to ParentRow         If theObj(HP,j) = 0 and theObj(LP,j) = 0 then             Response.Write vbtab & "<TR>" & vbcrlf             for i = 1 to 8                 Response.Write vbtab & vbtab & "<TD>" & TheObj(i,j) & "</TD>" & vbcrlf             next             Response.Write vbtab & "</TR>" & vbcrlf         end if     next end sub Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Karl Blessing · Jul 5, 2000

I think I may have managed to create a subroutine to work it, (man rescursion makes things small) <%@ Language=VBScript %> <% Response.Buffer = True %> <HTML> <HEAD> </HEAD> <BODY> <% Const HID   = 1 Const LID   = 2 Const Name  = 3 Const RType = 4 Const Value = 5 Const Label = 6 Const HP    = 7 Const LP    = 8 sub showtreeat(byref theObj, HpLoc, LpLoc)     Response.Write "<Table Border=1 align=center>" & vbcrlf     Dim ParentRow     ParentRow = UBound(theObj, 2)     for j = 0 to ParentRow         If theObj(HP,j) = HpLoc and theObj(LP,j) = LpLoc then             Response.Write vbtab & "<TR>" & vbcrlf                 for i = 1 to 8                     Response.Write vbtab & vbtab & "<TD>" & TheObj(i,j) & "</TD>" & vbcrlf                 next             showtreeat theObj, theObj(HID, j), theObj(LID, j)             Response.Write vbtab & "</TR>" & vbcrlf         end if     next end sub Dim RS Dim Conn Dim AryData Set Conn = Server.CreateObject("ADODB.Connection&quot

Set RS = Server.CreateObject("ADODB.Recordset&quot

Conn.Open "Tree", "sa", "" RS.Open "Select * from Tree order by HID, ID",Conn, 3,1 If not RS.EOF then     RS.MoveFirst     AryData = RS.GetRows     RS.Close     Set RS = nothing end if Conn.Close Set Conn = nothing showtreeat AryData, 0, 0 set AryData = nothing Response.Flush %> </BODY> </HTML> I will modify this to suit my needs, thanks for your help people. Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Karl Blessing · Jul 5, 2000

just if any of you are curious, this is my new showtreeat sub sub showtreeat(byref theObj, HpLoc, LpLoc)     Dim ParentRow     ParentRow = UBound(theObj, 2)     for j = 0 to ParentRow         If theObj(HP,j) = HpLoc and theObj(LP,j) = LpLoc then             for k = 0 to Spaces                 Response.Write "     "             next             Response.Write vbcrlf & "<input type=" & TheObj(RType, j) & " name=" & TheObj(Name, j) & " value=" & TheObj(Value,j) & ">"             Response.Write TheObj(Label, j) & " " & vbcrlf             Spaces = Spaces + 1             showtreeat theObj, theObj(HID, j), theObj(LID, j)             Spaces = Spaces - 1         end if     next end sub Spaces is a global scope variable that is set at 0 on page load, this gives the ouput a treelike view Karl <a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a> <a href=

http://kb244.8m.com>

</a> Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

I have a table in Access, and I want to figure out how I could in VBsc 2

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor