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

 

[rsshetty]

Try this

strSQL = "use maindbtest
select distinct table_name
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME"
SET rs = dbconn.execute(strSQL)
Response.write "Lst of Table" & "<br>"
while not rs.eof
response.write(rs("table_name")
response.write "<br>"
rs.movenext
wend

rsshetty.

It's always in the details.

 

[encore0000]

hey i got the following error. I am kinda new into this. your help is greatly appreciated.. thanks alot

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/hireguide/mydbdetails.asp, line 6

strSQL="use maindbtest
------------------------^

 

[encore0000]

did u get my last message

 

[DreXor]

for future reference when you're working with semi-foriegn recordsets, a helpful trick i use to know what fields i ended up with etc is the following :

set RS = Connection.Execute(SQLstr)
If Not RS.EOF Then
' this for/next writes out the field names in the data collection
for each field in rs.fields
response.write field.name & "|"
next
'seperate the field name headers from the data
response.write "<BR>"
'cycle thru the records
do while not rs.eof
'cycle thru each field for each record
for each field in rs.fields
response.write rs(field.name) & "|"
next
rs.movenext
'line break for each record
response.write "<BR>"
loop
End If
Set RS = nothing

 

[DreXor]

ps forgot to mention the "|" is just to seperate them feel free to use table/tr/td formatting instead of "|"/"<BR>"

 

[rsshetty]

I was just wondering if you are connected to the sql server instance which has the databse maindbtest.

rsshetty.

It's always in the details.

 

[encore0000]

no i am not conencted

 

[encore0000]

what difference does it make if its connected or not? I am still getting the following error.
---error-----
Microsoft OLE DB Provider for SQL Server error '80004005'

Could not locate entry in sysdatabases for database 'maindbtestselect'. No entry found with that name. Make sure that the name is entered correctly.

/mydbdetails.asp, line 10
---- WHY DOES IT SAY maindbtestselect. it should say only maindbtest
---Here is the exact code for the script---
strSQL="use maindbtest" &_
"select distinct table_name" &_
"from INFORMATION_SCHEMA.COLUMS" &_
"order by table_name"
set rs=dbconn.execute(strSQL)
If Not rs.eof Then
'this for/next writes out the field names in the data collection for each field in rs.fields

response.write field.name & "|"
next
'seperate the field name headers from the data
response.write "<BR>"
'cycle thru the records
do while not rs.eof
'cycle thru each field for each record
for each field in rs.fields
response.write rs(field.name) & "|"
next
rs.movenext
'line break for each record
response.write "<BR>"
loop
End If
Set RS = nothing
%>

 

[DreXor]

i'm not a big fan of line continuances .. but you have

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

looks like you're missing some spaces and the words are ramming together
like *from
maindbtestgo
schema.columnsorder

 

[encore0000]

hmmm true.. is there any better way around this.

 

[DreXor]

also in that exact code, you're missing that first "FOR" in the for/next cycle

......If Not rs.eof Then
'this for/next writes out the field names in the data collection for each field in rs.fields
FOR
response.write field.name & "|"
next
'seperate the field name headers from the data
respon.....

 

[DreXor]

oops .. forgot that needs to be FOR each field in rs.fields

 

[DreXor]

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

is one option, the other is add some spaces?

 

[encore0000]

yeah it got the for each field ...rs.fields

 

[DreXor]

if you need carraige returns in it for sql to "like" it then

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

 

[encore0000]

ok solved but then got this error

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 2: Incorrect syntax near 'go'.

 

[encore0000]

i removed the GO from the sqlstr.. so in that case i got the error as follow

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/hireguide/mydbdetails.asp, line 6

 

[DreXor]

you can try using connection.schema

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")
Set Rs = dbConn.OpenSchema (adSchemaTables)

' Loop through the list and print the table names
Do While Not Rs.EOF
Response.Write "<BR>" & Rs ("TABLE_NAME")
Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing

dbConn.Close
Set dbConn = Nothing

and you might want to look over this stuff :

http://support.microsoft.com/support/kb/articles/Q186/2/46.asp&NoWebContent=1

 

[DreXor]

in that link there's sub sections in it dedicated to specifically SQL server 6.5 and 7 those should help with a foot up.