How to determine default values of a field during runtime

25 Nov 2005 10:43 AM

pol_c

Hi, i am using the function below (visual basic 6) to determine several

properties of
each field in a recordset (SQL server) and all is working well. My
problem is that I
dont know how to find the default value of a field from the table. Does

anyone have
any code suggestions that would get me the default value

For Each F In rstProgressData.Fields
    If F.Type <> adChapter Then
      If F.Name <> "upsize_ts" Then
        rstDest.AddNew
        rstDest!Progress = F.Name

        Select Case F.Type
          Case adChar, adVarWChar, adVarChar
             rstDest!ProgressFieldType = "String"
             rstDest!ProgressFieldSize = F.DefinedSize
          Case adBoolean
            rstDest!ProgressFieldType = "Boolean"
          Case adSmallInt, adUnsignedTinyInt, adInteger
             rstDest!ProgressFieldType = "Integer"
          Case adDecimal, adNumeric
            rstDest!ProgressFieldType = "Decimal"
          Case adDBTimeStamp
            rstDest!ProgressFieldType = "DateTime"
          Case 203
            rstDest!ProgressFieldType = "Memo"
        End Select

        If (F.Attributes And adFldIsNullable) = adFldIsNullable Then
          rstDest!ProgressFieldNullable = True
        End If

        rstDest.Update
      End If
    End If
Next F

25 Nov 2005 11:01 AM

Jens

Thats hard to see where your recordset is based on if you don´t send
the query with you, but otherwise the information can be queried
through the INFORMATION_SCHEMA Views:

Select Column_default from INFORMATION_SCHEMA.COLUMNS
Where table_name = '<SomeTable>'

HTH, Jens Suessmeyer.

25 Nov 2005 11:55 AM

pol_c

The code has to be generic so that it can deal with any sql statement.
As above for any query I can get the field size, type, name & if its
nullable, I just need to find out what syntax to use to get the default
value e.g F.Type gives me type. F has been defined as an ADODB.Field.