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!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OPENSHEMA

Status
Not open for further replies.
gc1234

gc1234

Programmer
Mar 4, 2004
94
GB
Hi folks,

I need to extract data from a table using openshema, so far i have got back table names and colums but i need to loop and extract all the data but for some reason I cannot use EOF or Fields methods on the record set?

any ideas
 
Sort by date Sort by votes
do while not rs.eof does not work, is it possible to use something like

try

catch? or numbers of fields in the table, the table excutes are working using this method

sql = "select * from ["&thistable&"]
 
Upvote 0 Downvote
Here's what i have so far
<html><head>
<TITLE>dbschemas.asp</TITLE>
</head>
<body bgcolor="#FFFFFF">
<!--#INCLUDE file="ADOVBS.INC" -->
<%
myDSN="DSN=me"

Set conntemp=Server.CreateObject("adodb.connection")
conntemp.open myDSN

Set rsSchema = conntemp.OpenSchema(adSchemaColumns)

thistable=""
pad="&nbsp;&nbsp;&nbsp;"
Do UNTIL rsSchema.EOF
i=i+1
prevtable=thistable
thistable=rsSchema("Table_Name")
thiscolumn=rsSchema("COLUMN_NAME")
If thistable<>prevtable Then
i=0
Response.Write "Table=<b>" & thistable & "</b><br>"
set rs = Server.CreateObject("ADODB.Recordset")
sql = "select * from ["&thistable&"]"
rs = conntemp.Execute(sql)
End If

Response.Write rs(i)&"," 'need help here


Dim colschema(27)
colschema(0)="TABLE_CATALOG"
colschema(1)="TABLE_SCHEMA"
colschema(2)="TABLE_NAME"
colschema(3)="COLUMN_NAME"
colschema(4)="COLUMN_GUID"
colschema(5)="COLUMN_PROP_ID"
colschema(6)="ORDINAL_POSITION"
colschema(7)="COLUMN_HASDEFAULT"
colschema(8)="COLUMN_DEFAULT"
colschema(9)="COLUMN_FLAGS"
colschema(10)="IS_NULLABLE"
colschema(11)="DATA_TYPE"
colschema(12)="TYPE_GUID"
colschema(13)="CHARACTER_MAXIMUM_LENGTH"
colschema(14)="CHARACTER_OCTET_LENGTH"
colschema(15)="NUMERIC_PRECISION"
colschema(16)="NUMERIC_SCALE"
colschema(17)="DATETIME_PRECISION"
colschema(18)="CHARACTER_SET_CATALOG"
colschema(19)="CHARACTER_SET_SCHEMA"
colschema(20)="CHARACTER_SET_NAME"
colschema(21)="COLLATION_CATALOG"
colschema(22)="COLLATION_SCHEMA"
colschema(23)="COLLATION_NAME"
colschema(24)="DOMAIN_NAME"
colschema(25)="DOMAIN_CATALOG"
colschema(26)="DOMAIN_SCHEMA"
colschema(27)="DESCRIPTION"

On Error Resume Next
For counter=4 To 27
thisColInfoType=colschema(counter)
thisColInfo=rsSchema(thisColInfoType)
If Err.number<>0 Then
thiscolinfo="-error-"
Err.Clear
End If
If thisColInfo<>"" Then
'Response.Write pad & pad & pad & thiscolinfotype
'Response.Write "=<b>" & thiscolinfo & "</b><br>"
End If
Next
Response.Flush
rsSchema.MoveNext
Loop

rsSchema.Close
Set rsSchema=Nothing

conntemp.close
Set conntemp=Nothing
'Here Is the contents of lib_fieldtypes.asp which Is included To make this example work:
%>
</body></html>
 
Upvote 0 Downvote
Here's what i have so far
<html><head>
<TITLE>dbschemas.asp</TITLE>
</head>
<body bgcolor="#FFFFFF">
<!--#INCLUDE file="ADOVBS.INC" -->
<%
myDSN="DSN=me"

Set conntemp=Server.CreateObject("adodb.connection")
conntemp.open myDSN

Set rsSchema = conntemp.OpenSchema(adSchemaColumns)

thistable=""
pad="&nbsp;&nbsp;&nbsp;"
Do UNTIL rsSchema.EOF
i=i+1
prevtable=thistable
thistable=rsSchema("Table_Name")
thiscolumn=rsSchema("COLUMN_NAME")
If thistable<>prevtable Then
i=0
Response.Write "Table=<b>" & thistable & "</b><br>"
set rs = Server.CreateObject("ADODB.Recordset")
sql = "select * from ["&thistable&"]"
rs = conntemp.Execute(sql)
End If

Response.Write rs(i)&"," 'need help here


Dim colschema(27)
colschema(0)="TABLE_CATALOG"
colschema(1)="TABLE_SCHEMA"
colschema(2)="TABLE_NAME"
colschema(3)="COLUMN_NAME"
colschema(4)="COLUMN_GUID"
colschema(5)="COLUMN_PROP_ID"
colschema(6)="ORDINAL_POSITION"
colschema(7)="COLUMN_HASDEFAULT"
colschema(8)="COLUMN_DEFAULT"
colschema(9)="COLUMN_FLAGS"
colschema(10)="IS_NULLABLE"
colschema(11)="DATA_TYPE"
colschema(12)="TYPE_GUID"
colschema(13)="CHARACTER_MAXIMUM_LENGTH"
colschema(14)="CHARACTER_OCTET_LENGTH"
colschema(15)="NUMERIC_PRECISION"
colschema(16)="NUMERIC_SCALE"
colschema(17)="DATETIME_PRECISION"
colschema(18)="CHARACTER_SET_CATALOG"
colschema(19)="CHARACTER_SET_SCHEMA"
colschema(20)="CHARACTER_SET_NAME"
colschema(21)="COLLATION_CATALOG"
colschema(22)="COLLATION_SCHEMA"
colschema(23)="COLLATION_NAME"
colschema(24)="DOMAIN_NAME"
colschema(25)="DOMAIN_CATALOG"
colschema(26)="DOMAIN_SCHEMA"
colschema(27)="DESCRIPTION"

On Error Resume Next
For counter=4 To 27
thisColInfoType=colschema(counter)
thisColInfo=rsSchema(thisColInfoType)
If Err.number<>0 Then
thiscolinfo="-error-"
Err.Clear
End If
If thisColInfo<>"" Then
'Response.Write pad & pad & pad & thiscolinfotype
'Response.Write "=<b>" & thiscolinfo & "</b><br>"
End If
Next
Response.Flush
rsSchema.MoveNext
Loop

rsSchema.Close
Set rsSchema=Nothing

conntemp.close
Set conntemp=Nothing
%>
</body></html>
 
Upvote 0 Downvote
why are you using OpenSchema to query data?

sharply sounds like school work as this is out of form and a challenge sounding task

___________________________________________________________________

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
 
Upvote 0 Downvote
Well i cannot view the db, so I have a connection via DSN and schema the db for all its tables, dont ask why i cant view it, its just that way...

whole code update

<html><head>
<TITLE>dbschemas.asp</TITLE>
</head>
<body bgcolor="#FFFFFF">
<!--#INCLUDE file="ADOVBS.INC" -->
<%
myDSN="DSN=me"

Set conntemp=Server.CreateObject("adodb.connection")
conntemp.open myDSN

Set rsSchema = conntemp.OpenSchema(adSchemaColumns)

thistable=""
pad="&nbsp;&nbsp;&nbsp;"
Do UNTIL rsSchema.EOF
i=i+1
prevtable=thistable
thistable=rsSchema("Table_Name")
thiscolumn=rsSchema("COLUMN_NAME")
If thistable<>prevtable Then
i=0
Response.Write "Table=<b>" & thistable & "</b><br>"
set rs = Server.CreateObject("ADODB.Recordset")
sql = "select * from ["&thistable&"]"
rs = conntemp.Execute(sql)
End If
'ar = rs.MaxRecords
Response.Write rs(i)&"," 'need help here

On Error Resume Next

Response.Flush
rsSchema.MoveNext
Loop

rsSchema.Close
Set rsSchema=Nothing

conntemp.close
Set conntemp=Nothing
%>
</body></html>
 
Upvote 0 Downvote
Is there anyway of getting the total rows of each table using this method pls
 
Upvote 0 Downvote
Code: 
Response.Write "<table width=""100%"">"

Do UNTIL rsSchema.EOF
    i=i+1
    prevtable=thistable
    thistable=rsSchema("Table_Name")
    thiscolumn=rsSchema("COLUMN_NAME")

    If thistable<>prevtable Then
        i=0
        Response.Write "<tr><td>Table= " & thistable & " </td></tr>"

        sql = "select * from ["&thistable&"]"

        Set rs = conntemp.Execute(sql)
        	Response.Write "<tr><td>"
				Response.Write "<table><tr>"
					Do While NOT rs.EOF
						Response.Write "<td>" & rs(i) & "</td>"
					rs.MoveNext
					Loop
				Response.Write "</tr></table>"
        	Response.Write "</td></tr>"
    End If

rsSchema.MoveNext
Loop

Response.Write "</table>"


Just so you know, this will query more then just tables, but everything. in saying that you will get queries back also.

getting the COUNT(*) of the rows is jsut another SQL statement

e.g.
SELECT COUNT(*) FROM table

___________________________________________________________________

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
 
Upvote 0 Downvote
This will not work...

Do While NOT rs.EOF

Even MoveNext does not work, its has if the recordset is not available, it will only give me the first row


 
Upvote 0 Downvote
I can get the count but i cant move through the records

sql = "select count(*) as tot from ["&thistable&"]"
rs1 = conntemp.Execute(sql)
f = rs1(0)
Response.Write "<bR>"&f
 
Upvote 0 Downvote
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top