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!

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

Help w/ sql DESCRIBE using asp 1

Status
Not open for further replies.

twofive

Programmer
Jan 9, 2007
27
US
Please help me with this simple issue. I simply want to list the structure of a table w/ the DESCRIBE command, but I'm not sure how to print this using Response.Write or similar. Here's my VB:

<%
on error resume next
dim Conn, RS

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL Server}; Server=w; database=x; uid=y; pwd=z; option=0;"

Set RS = Conn.Execute("DESCRIBE product")
Response.Write ...

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
%>

What do I use in place of ..., above?

I'm completely new to MS products, including SQL Server 2k, VB, etc -- Background in MySQL, PHP, mysql command prompt & phpMyAdmin.. I'm looking forward to expanding my MS knowledge--more buzzwords in resume ;)
 
use sp_help instead of describe.

- Paul
- If at first you don't succeed, find out if the loser gets anything.
 
There is no such command [DESCRIBE] in SQL Server. I am not sure what result you want but you could try:
(also your connection string is not right, this is a connection string for ODBC not for ADO)
Code:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open
"Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

Set RS = Conn.Execute("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'product'")
Response.Write ...

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
SQL Server does not have a DESCRIBE command. A quick google search indicates that this command will return a list of columns in a table. You can do this with SQL Server, but not with DESCRIBE. Here's how....

Code:
<%
on error resume next
dim Conn, RS

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL Server}; Server=w; database=x; uid=y; pwd=z; option=0;"

Set RS = Conn.Execute("Select * From Information_Schema.Columns Where Table_Name='product'")

While Not RS.Eof
  For i = 0 To RS.Fields.Count - 1
    Response.Write(RS(i).Value & vbTab)
  Next
  Response.Write("<br />")

  RS.MoveNext
Wend

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
%>

The syntax may not be perfect, but this should be a good start for you. Hope it helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Problem solved!

Thanks to everyone who responded. What a great resource! I started working on what bborissov had provided, then when I was puzzled on how to write the output, gmmastros provided the missing link. Thanks also to ptheriault; I attempted using sp_help, but to no avail due to my feeble handle of vb I couldn't figure out how to print output.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top