how to access sql server meta data using asp?

method · March 3rd, 2005, 01:57 PM

hi guyes i have a database in sql server 2000 and i want to write an asp script that using ado connection to that database and uses the metadata to display all the colums of a perticuler table.(no information about tables ,colums,datatypes, primary keys and foreign keys should be hardcoded). Could any one help me how i do this. Thanks

i want it to be quivelet to this one but instead of access sql server db:

<%@LANGUAGE="vbscript" CODEPAGE="1252"%>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<%

    sql="select * from players"

    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open(Server.Mappath("/db/TENNIS DATABASE.mdb"))
    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open sql, conn

%>
</head>

<body>

<table width="200" border="1">
<tr>
    <td>Player ID</td>
    <td>Name</td>
    <td>Intials</td>
</tr>
<% while not rs.eof %>
<tr>
    <td><a href = "playerprofile.asp?person_id=<%=rs("playerno")%>"> <%=rs("playerno")%></a></td>
    <td><%=rs("name")%></td>
    <td><%=rs("initials")%></td>
</tr>
<% rs.movenext
wend
%>
</table>

</body>
<%
    rs.close
    set rs=nothing

%>
</html>

poojafalor · March 4th, 2005, 12:39 AM

Hi,

Please have the required ASP script below:

<%@LANGUAGE="vbscript" CODEPAGE="1252"%>
<%
    Dim sql,conn,rs,intNoOFields
    sql="select * from players"
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Open("Provider=SQLOLEDB.1;User ID=sa;password=mypassword;Initial Catalog=MyDatabase;Data Source = MySQLServer;")
    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open sql, conn
    intNoOFields = rs.Fields.Count
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<%If Not (rs.EOF And rs.BOF) Then%>
<table width="200" border="1">
  <tr>
  <% For i = 0 To intNoOFields - 1%>
        <th><%=rs(i).Name%></th>
  <% Next%>
  </tr>
  <% while not rs.eof %>
  <tr>
     <% For i = 0 To intNoOFields - 1%>
         <td><%=rs(i).Value%> </td>
     <% Next%>
  </tr>
  <% rs.movenext
  wend
  %>
</table>
<%end if%>
</body>
<%
    rs.close
    set rs=nothing
    conn.close
    set conn = nothing
%>
</html>

Cheers,
Pooja Falor

method · March 7th, 2005, 08:12 AM

Thanks for u reply. I want to run this code in xp pro sp2 computer but i get connection errors.

i do not know what should i put for its parameters based on my installation method!!
i tried all sort of paramaters and non worked .

conn.Open("Provider=SQLOLEDB.1;User ID=sa;password=mypassword;Initial Catalog=MyDatabase;Data Source = MySQLServer;")

the way i log to sql server :
using sql server service manager is by typing in LAPTOP in the server edit box and and pressing start!(no password)

The way i connect to query analyzer i type LAPTOP in sql server edit box and user windows authenticatin and login without any
password.

i be happy if some one help me run this on my system.thanks