Webmaster Forum / ASP / Database Access / January 2005

Lex Luthor wrote on 25 jan 2005 in microsoft.public.inetserver.asp.db:

[please do not toppost on usenet]

Sorry, but that makes no sense to me, Lex.

What is a default value of a field if it is not set that way?

?
Everything you need to know is covered in these articles, especially if you
supplement the SQL Server examples with some reading in SQL Books Online.
For example, for SQL Server, here's a copy-paste from the aspfaq article
(with a slight modification to eliminate the dynamic sql, and another
modification to show all the field values of the  returned recordset):

<%
   dbname = "databasename"
   tablename = "tablename"

   ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _
       "Data Source=<x.x.x.x>;" & _
       "User Id=<uid>; Password=<pwd>;" & _
       "Initial Catalog=" & dbname

   set adodbConn = CreateObject("ADODB.Connection")
   adodbConn.Open ConnStr
   set rs = CreateObject("ADODB.Recordset")
   rs.cursorlocation = 3    'adUseClient
   adodbConn.sp_columns tablename,rs
   set rs.activeconnection = nothing
   adodbConn.Close: set adodbConn = nothing
   Response.Write "<TABLE><TR>"
   for each fld in rs.Fields
       Response.Write "<TD>" & fld.name & "</TD>"
   next
   Response.Write "</TR><TD>"
   str = rs.GetString(2,,"</TD><TD>","</TD></TR><TR><TD>","&nbsp;")
   rs.close: set rs = nothing
   str=left(str,len(str)-8)
   response.write str
   response.write "</TABLE>"
%>

Substitute your specific information, run this code and look at the
COLUMN_DEF field.

For Access, it is a little harder.

Actually, come to think of it, you may be able to use OpenSchema for all of
your databases. Try this:
<%
   dbname = "databasename"
   tablename = "tablename"

   ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _
       "Data Source=<x.x.x.x>;" & _
       "User Id=<uid>; Password=<pwd>;" & _
       "Initial Catalog=" & dbname

   set adodbConn = CreateObject("ADODB.Connection")
   adodbConn.Open ConnStr
   set rs = adodbConn.OpenSchema(adSchemaColumns, _
   Array(Empty,Empty,tablename))
   Response.Write "<TABLE border=1><TR>"
   for each fld in rs.Fields
       Response.Write "<TD>" & fld.name & "</TD>"
   next
   Response.Write "</TR><TD>"
   str = rs.GetString(2,,"</TD><TD>","</TD></TR><TR><TD>","&nbsp;")
   rs.close: set rs = nothing
   adodbConn.Close: set adodbConn = nothing

   str=left(str,len(str)-8)
   response.write str
   response.write "</TABLE>"
%>

You can make this return information about a specific column by changing
this:
   set rs = adodbConn.OpenSchema(adSchemaColumns, _
   Array(Empty,Empty,tablename))

to this:
   col = "SomeColumn"
   set rs = adodbConn.OpenSchema(adSchemaColumns, _
   Array(Empty,Empty,tablename, col))

This workd with both SQL Server and Jet. I don't know if it will work with
MySQL, but i suspect that it will.

Bob Barrows