Access2K Oledb DataView: DefaultValue Unique AllowDBNull properties

[AndreaJ]

When an OleDb dataview is instantiated using (Select * from
MyAccess2000Table), why aren't the following datacolumn properties
accurately exposed:

Unique
AllowDBNull
DefaultValue

Dim Col As DataColumn
For Each Col In MyDataView.Table.Columns
Col.Unique
Col.AllowDBNull
Col.DefaultValue
Next

When I test these values at runtime, they do not reflect the state of my
Access table. Doesn't the JET4.0 Provider surface them to the DataView?

TIA

 

[Joe Fallon]

I hard coded the default value in my table building code.
(Don't know about anyone else.)

I use code like this to build my datatables.

Public Function GenMissingItemsTable() As DataTable
Dim dt As New DataTable("dtMissingItems")
Dim col As DataColumn
With dt.Columns
col = .Add("itemno", GetType(String))
col.MaxLength = 25
col.AllowDBNull = False
col = .Add("descr1", GetType(String))
col.MaxLength = 60
col.AllowDBNull = False
col = .Add("uom", GetType(String))
col.MaxLength = 3
col.AllowDBNull = False
End With
dt.PrimaryKey = New DataColumn() {dt.Columns("itemno")}

Return dt
End Function

--
Joe Fallon

Thanks for the code snippet, Joe. But I'm not out of the woods yet. The
FillSchema() method doesn't configure the DefaultValue property.
So how do I get the important default value? Or even find out if there
is one?

I must allow the record to be written to the row with a blank column
value, even if that column has AllowDBNull = False, when the column has
a default value. But I cannot find out by examining the schema if there
is a default value set for the column.

Is everyone hard-coding default values into the front-end? That would be
a maintenance problem if the default-value should change.

Andi

Unique
AllowDBNull
DefaultValue

Dim Col As DataColumn
For Each Col In MyDataView.Table.Columns
Col.Unique
Col.AllowDBNull
Col.DefaultValue
Next

[snip important stuff]
Public Sub GetSchema(ByRef dt As DataTable, _
ByVal strSQL As String, _
ByVal DBtype As String)
'retrieve structure information into a datatable using the FillSchema
' method of a DataAdapter object
...<snip>
ElseIf DBtype = "OLEDB" Then
Dim cnn As New OleDbConnection(mConnStr)
Try
cnn.Open()
'KB309488 - the Object array is for filtering the returned data
(only TABLEs are returned not Views, etc.)
dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object()
{Nothing, Nothing, Nothing, "TABLE"})
Finally
cnn.Close()
End Try
End If
End Sub