Re: "View" to a large schema

From: Hypermommy <hypermommy_at_comcast.net>
Date: 14 Jul 2003 10:47:19 -0700
Message-ID: <7ed5e93a.0307140459.13020d51@posting.google.com>

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.1978d39f9c07107f9897ed_at_news.la.sbcglobal.net>...

<snipped for brevity>

> Again, without the code in front of me, I'm just taking a shot in the
> dark. Since it seems to work with a FOR loop, but not a DO-UNTIL loop,
> it's beginning to sound like you are trying to open a connection AFTER
> you've already reached the rs.EOF. Do you see anywhere that you close
> the connection? If the code already detects an EOF when it comes to the
> DO-UNTIL statement, then the DO won't get executed and you'll have an
> empty RS. The FOR would execute, however, because it loops a specific
> number of times.
>
> I'm not an ASP expert (we use the similar JSP), so I hope someone else
> will jump in here.

Hi all,

I'm posting the code, now that I kind of understand what's going on. For a quick recap, I've found that if I put in a for loop instead of the do while not loop then I can iterate up to 1000 times and the page doesn't freeze up. However, if I use the do loop, it just freezes. A couple of things I don't understand that I hope you can help me with.....

1. The OpenSchema method doesn't appear to be working since I get back an empty recordset (as evidenced by the fact that when I check for BOF and EOF they're both true). Any clue why? It does work with a SQL server table... shouldn't it also work with Oracle? I know that the login being used in the DSN has full rights to at least see the tables.
2. If EOF is true, why would the do while cause things to freeze up. I can use a 1000 iteration for loop, but not a do while but if it's EOF then the do while should never come into play, right?

Thanks in advance for any help you can give me.

Function listTablesWeb(nDSN)

	dim objConn, objTableRS, objNoneSupportedTableRS
	dim bFound, aNoneSupportedTablesArray, sTempText
	dim nCounter
	Response.Write "<br>" & nDSN
	
	on error resume next
	Set objConn = Server.CreateObject("ADODB.Connection")
	'objConn.open nDSN
	objConn.Open sDSN, sUsername, sPassword', sDriveOptions

	If Err.Number <> 0 Then 		
		localError = Err.Description
	else
		sTempText = ""
		on error resume next
		set objTableRS = objConn.OpenSchema(adSchemaTables)


objTableRS.movelast

objTableRS.movefirst
' response.write ("<option>There are " & objTableRS.recordcount & " tables.</option>")
if objTables.BOF then

        response.write("<option>We are BOF</option>") end if
if objTables.EOF then

        response.write("<option>We are EOF</option>") end if

'		Do While Not objTableRS.EOF
'			If LCase(objTableRS("TABLE_TYPE").Value) = LCase("TABLE") or
LCase(objTableRS("TABLE_TYPE").value) = LCase("VIEW") Then
'				Response.Write("<option value='" &

trim(objTableRS("Table_Name").Value) & "'>" & trim(objTableRS("Table_Name").Value) & "</option>" & vbCrLf)

'			End If
'			objTableRS.MoveNext
'		Loop
		objTableRS.Close
		objConn.Close 
	end if
	
	Set objTableRS = Nothing
	Set objConn = Nothing

end function Received on Mon Jul 14 2003 - 12:47:19 CDT