Lookup defined default value for columns from database schema

For many columns in our database we have defined default values, and we would like to lookup those default values to display them in textboxes etc. in DetailsViews in insert mode.

The DataTableReader.GetSchemaTable method should return a datatable with, among other things, a column named 'DefaultValue' (see http://msdn.microsoft.com/en-us/library/system.data.datatablereader.getschematable.aspx).

I've tried that, but the value reported for DefaultValue is always System.DBNull, even if I know for sure that I should get a "real" default value as defined in the database (like 0 or sometimes 99 for numeric fields).

Any ideas how to solve this?

Some of my VB.NET source code:

Public Function GetColumnDefaultValue(ByVal p_sTable As String, ByVal p_sColumn As String)
        Trace.WriteLine("wsDataProvider.GetColumnDefaultValue: " & p_sTable & "." & p_sColumn)
        Dim l_vDefault As Object = Nothing, l_bError As Boolean = False
        If (IsColumn(p_sTable, p_sColumn)) Then
            Dim l_sSql As String
            If (UsesAccessDB()) Then
                l_sSql = "select top 1 `" & p_sColumn & "` from `" & p_sTable & "`"
            Else
                l_sSql = "select `" & p_sColumn & "` from `" & p_sTable & "` limit 1"
            End If
            Try
                Dim l_oDataSet As DataSet = FillDataSet(l_sSql, "DataColumns")
                Dim l_oDataTableReader As DataTableReader = l_oDataSet.CreateDataReader(l_oDataSet.Tables(0))
                Dim l_oSchema As DataTable = l_oDataTableReader.GetSchemaTable()
                l_vDefault = l_oSchema.Rows(0)("DefaultValue")
            Catch ex As Exception
                System.Diagnostics.Debug.WriteLine("wsDataProvider.GetColumnDefaultValue: " & ex.Message)
                '
                l_bError = True
                _oErrorHandler.LogError()
            End Try
        End If
        If l_bError = True Then
            _oErrorHandler.DisplayErrorPage()
        End If
        Return l_vDefault
    End Function

Re: Lookup defined default value for columns from database schema

Yes, I've managed to solve this. I now use the following method on my DataProvider class:

Public Function GetColumnDefaultValue(ByVal p_sTable As String, ByVal p_sColumn As String)
        'wsUtils.Trace("wsDataProvider.GetColumnDefaultValue: " & p_sTable & "." & p_sColumn)
        Dim l_vDefault As Object = Nothing, l_bError As Boolean = False
        Dim l_oErrorHandler As New wsErrorHandler
        If (IsColumn(p_sTable, p_sColumn)) Then
            If UsesMySQL() Then
                Try
                    Dim l_sDBname As String = ConfigurationManager.AppSettings("wsDBname")
                    Dim l_sSql As String = "SHOW COLUMNS FROM " & p_sTable & " FROM " & l_sDBname
                    Dim l_oDTcolumns As DataTable = FillDataTable(l_sSql)
                    If l_oDTcolumns IsNot Nothing AndAlso l_oDTcolumns.Rows.Count > 0 Then
                        ' loop through DB table columns for definition
                        Dim l_sColumnName As String
                        For Each l_oDTcolumn As DataRow In l_oDTcolumns.Rows
                            l_sColumnName = l_oDTcolumn(0)
                            If l_sColumnName.ToLower = p_sColumn.ToLower Then
                                l_vDefault = l_oDTcolumn(4)
                                Exit For
                            End If
                        Next
                        ' clean up
                        l_oDTcolumns.Dispose()
                    End If
                Catch ex As Exception
                    wsUtils.Warn("wsDataProvider.GetColumnDefaultValue: " & ex.Message)
                    l_bError = True
                    l_oErrorHandler.LogError(ex)
                End Try
            Else
                ' Access DB
                Try
                    Dim l_oCatalog As New ADOX.Catalog
                    l_oCatalog.ActiveConnection = GetConnection()
                    Dim l_oColumn As ADOX.Column = l_oCatalog.Tables(p_sTable).Columns(p_sColumn)
                    If l_oColumn IsNot Nothing Then
                        l_vDefault = l_oColumn.Properties("Default").Value
                    End If
                Catch ex As Exception
                    wsUtils.Warn("wsDataProvider.GetColumnDefaultValue: " & ex.Message)
                    l_bError = True
                    l_oErrorHandler.LogError(ex)
                End Try
            End If
        End If
        If l_bError = True Then
            l_oErrorHandler.DisplayErrorPage()
        End If
        Return l_vDefault
    End Function

Maybe this is of some use to others as well.