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")
%>

 

[encore0000]

THANKS ALOT FOR ALL THE HELP
i got the error using that


ADODB.Connection error '800a0cb3'

Object or provider is not capable of performing requested operation.

 

[rsshetty]

strsql = "use maindbtest select distinct table_name table_name from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME"
set rs = fairdb.execute(strsql)
while not rs.eof
response.Write(rs("table_name"))
response.Write "<br>"
rs.movenext
wend

this worked perfectly for me.
rsshetty.

It's always in the details.

 

[DreXor]

 

[encore0000]

hey guys thanks alot for all the help the code still gives me the followiung error what could cause that..?

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/hireguide/admin/hsdb-info1.asp, line 8

 

[encore0000]

??

 

[DreXor]

can u post the code you're using currently?

 

[williamsba]

Your dataconnection is closed.

Brad Williams
Webmaster

 

[encore0000]

Heres the code im using..

<%
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 cmd = dbconn.execute(strsql, , adCmdText)
while not rs.eof
response.Write(rs("table_name"))
response.Write "<br>"
rs.movenext
wend
rs.close()
set rs=nothing
dbconn.close()
set dbconn=nothing
%>

 

[DreXor]

strsql = "use maindbtest select * from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME"
set cmd = dbconn.execute(strsql, , adCmdText)
while not rs.eof


you're using different objects, rename all RS's to CMD OR rename CMD to RS

 

[encore0000]

ok fixed that..but i cant freaking figure out why i am getting the operation is not allowed when the object is closed.

 

[DreXor]

you probably have a RS object somewhere else in the page (prior) that's closed then you're trying to loop the RS recordset which is closed.

 

[encore0000]

i dont have anything else on the page. just the asp script.as paste to you.how do i open the RS. im acutally using CMD consistently on the scrip right now- thanks

 

[DreXor]

i guess got me there, one of the objects was being unhappy about something, whichever one it was, and i dont know what is/was line 8

 

[encore0000]

while not rs.eof is on crack i think what should i do

 

[DreXor]

 

[encore0000]

any ideas i have wasted my entire freakin day on this thing..

 

[DreXor]

you're not wasting your time, try putting this code into an independant ASP page, make sure it works, then put it back, then you only have to debug where CMD is closed/open and or dbconn is closed/open and make sure the flow is still there

NOW if dbconn is created earlier, then set dbconn=server.createobject("adodb.connection") will throw an error especially if you created it, used it, closed it, and didn't set it =nothing before trying to re-create it

 

[encore0000]

where are you at man?

 

[DreXor]

forum prohibits contact outside of the forum so i'm technically not allowed to tell you that, but i'm in kansas, in my chair, in front of my computer.

i'm set up on email notification, and i'll be checking my email when i go to work here suddenly, i'll try to help as much as i can throughout the course of the day.

 

[encore0000]

so u are telling me to create a dbconn to be where?i dont quite understand what u said