Display question 6

[encore0000]

I have the following ASP code which basically goes into the main database and lists all the databases tables. However i dont know how to output those results using the Response.write command. this does not work right now. How would I have to go about that. Thanks in advance

-CODE- mydbdetails.asp
<%
Set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.open("Provider=SQLOLEDB; Data Source=sq111.crystech.com; Initial Catalog=mydbtest; User ID=mydbtest; Password=dotheftp; network=dbmcn")

strSQL="use maindbtest" &_
"go" &_
"select *" &_
"from INFORMATION_SCHEMA.COLUMNS" &_
"order by TABLE_NAME"

Response.Write("Done")
%>

 

[DreXor]

from what i understand you're doing more than just this loop in the page, either can you post the entirety BEFORE this portion ( the error part ) or verify that dbconn hasn't been closed before it's re-created in the line set dbconn = server.createobj.... etc

 

[encore0000]

here is the ASP file as exact i have it .i am simply running it..

<%
Set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.open("Provider=SQLOLEDB; Data Source=sq111.crystech.com; Initial Catalog=mydbtest; User ID=mydbtest; Password=dotheftp; network=dbmcn")

strsql = "use maindbtest select * from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME"
set rs = dbconn.execute(strsql, , adCmdText)
while not rs.eof ->> ERORR HERE I THINK
response.write(rs("TABLE_NAME"))
response.Write "<br>"
rs.movenext
wend
rs.close()
set rs=nothing
dbconn.close()
set dbconn=nothing
%>

 

[DreXor]

lets try this update to the script

<%
Set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.open("Provider=SQLOLEDB; Data Source=sq111.crystech.com; Initial Catalog=mydbtest; User ID=mydbtest; Password=dotheftp; network=dbmcn")

strsql = "use maindbtest select * from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME"
set rs = dbconn.execute(strsql, , adCmdText)
If Not Rs.EOF Then
while not rs.eof
response.write(rs("TABLE_NAME"))
response.Write "<br>"
rs.movenext
wend
else
Response.Write "Empty Recordset"
End If
rs.close()
set rs=nothing
dbconn.close()
set dbconn=nothing
%>

 

[DreXor]

ps .. side note, when this is all over with you may want to change the user/passes on your sql server, because most anyone has access right now, since it's a remote connection

 

[encore0000]

its not real i have bogus names thanks for leting me know ill work on this stuff tommorow but yeah thanks a bunch for all your help..appreciate it bro..

 

[DreXor]

no problem, i'll check in tomorrow as well to make sure i can do what i can if anything else arises

 

[encore0000]

so i was getting the same error so i commented out
the if not and while stuff to see if it works anyways. so now i am getting the following error!

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.


Here is the code I am using
strsql = "use maindbtest select * from INFORMATION_SCHEMA.COLUMNS order by table_name"
set rs = dbconn.execute(strsql, , adCmdText)
'If Not rs.EOF Then
' while not rs.eof
response.write(rs("table_name"))
response.Write "<br>"
rs.movenext
' wend
'else
Response.Write "Empty Recordset"
'End If
rs.close()
set rs=nothing
dbconn.close()
set dbconn=nothing
%>

 

[DreXor]

try putting that SQL statement into SQL and see if you get any errors, the only other thing i can possible see as an issue in there is perhaps Table_Name needs to be [Table_Name] because SQL sees underscores as escape characters ( if i remember right )

ps as for a prior question, some people put information/websites under their profiles on here.

 

[encore0000]

The SQL runs fine. I am still getting the same error. Actually the [Table_name] worked as far as the SQL was concerned because i ran it on the reports website. The error is.

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

strsql = "use maintestdb select * from INFORMATION_SCHEMA.COLUMNS order by [Table_Name]"
set rs = dbconn.execute(strsql, , adCmdText)
If Not rs.EOF Then
while not rs.eof
response.write(rs("[Table_Name]"))
response.Write "<br>"
rs.movenext
wend
else
Response.Write "Empty Recordset"
End If
rs.close()
set rs=nothing
dbconn.close()
set dbconn=nothing
%>

 

[DreXor]

line number?

 

[encore0000]

line 7

 

[DreXor]

this is getting really goofy, try removing the ,adCmdText in the Execute() args, see if it'll fire on SQL only

 

[encore0000]

its actually

If Not rs.EOF Then
i have removed the password verification code
thanks

 

[DreXor]

rs.eof shouldn't throw the error unless the RS isn't getting created properly.

is the page operating now?

 

[encore0000]

there is sometihn wrong with

response.write(rs("[table_name]"))

i am getting teh error
Item cannot be found in the collection corresponding to the requested name or ordinal.

 

[DreXor]

remove the brackets in the RS request, you only need the brackets in the SQL statement

 

[encore0000]

i have it like this now
response.write(rs("table_name"))

but same freaking error
Operation is not allowed when the object is closed.
line 7
If Not rs.EOF Then -?>>>>

 

[onpnt]

I don't think this is syntax wise correct
set rs = dbconn.execute(strsql, , adCmdText)

try

set rs = dbconn.execute(strsql)

it may still cause the error to point to the first reference of the rs even knowing the error is further back

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811

 

[onpnt]

just notice DreXor has already stated that but I concur with it

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811

 

[DreXor]

no worries, it's a gawdawful long thread