none
DataColumn Default Value RRS feed

  • Question

  • I create a DataColumn using this command:

    Alter Table [TablesColumnWidths] Add [Column2] REAL DEFAULT -2

    When a row is added using DataTable.NewRow(), the DefaultValue property for the column is null.

    Yet when the table is viewed in Access, its shows -2 as the default for new records.  Why don’t the new rows added with code get the default?

    Monday, September 5, 2016 9:48 PM

Answers

  • What I’ve finally settled on is

    DataTable schemaTable = Connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,new object[] {null,null,tableName,null});

    DataRow[] rows = schemaTable.Select("ORDINAL_POSITION > 0","ORDINAL_POSITION ASC");

    The returned rows contain the default values.  It’s not what I was originally hoping for, but it will work.  Time to move on.

    Friday, September 9, 2016 1:44 PM

All replies

  • This is not really a T-SQL question, but I'll give it a shot.

    First you add a column to the table in T-SQL. That has nothing to do with the DataColumn object as such.

    Later, at some point you have acquired a DataTable. I guess that depending on how it was created it may or may have a default set for the DataColumn for this column. But even if it has not, and it is not set explicitly, the column may not be passed to SQL Server when you add a row, and in such case the default will apply. But I don't really know how you write the row to the database, since you did not show that code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 5, 2016 10:12 PM
  • As Erland mentioned, the default defined in the database Table has nothing to do directly with the default in the DataTable. How those defaults get assigned to your DataTable's DataColumns depends on how you're creating/generating your DataTable (and, you haven't said how you've done that).

    Bottom line, you'd need a statement like this in your DataTable's definition:

    dataTable.Columns["Column2"].DefaultValue = -2;

    If you're using Typed DataSets, then you'd want something like this in the .xsd:

    <xs:element name="Column2" type="xs:int" minOccurs="0" default="-2" />

    Again, I don't know how you're creating/generating the DataSet/DataTable, but I hope this helps somewhat.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, September 6, 2016 3:28 PM
  • Here is the code used to add a row to the table.

    An OleDbCommand command object with a connection to the database is created.

    command.CommandText = “SELECT * From [TablesColumnWidths]"
    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
    DataSet data = new DataSet();
    adapter.Fill(data);
    DataRow newRow = data.Tables[0].NewRow();
    newRow[0] = “XYZ”;
    data.Tables[0].Rows.Add(newRow);
    adapter.Update(data);

    Here are the properties of the column with the default value.  The name and data type are as expected, but nothing is shown for the default value.

    data.Tables[0].Columns[3]
    {Column2}
        base {System.ComponentModel.MarshalByValueComponent}: {Column2}
        AllowDBNull: true
        AutoIncrement: false
        AutoIncrementSeed: 0
        AutoIncrementStep: 1
        Caption: "Column2"
        ColumnMapping: Element
        ColumnName: "Column2"
        DataType: {Name = "Single" FullName = "System.Single"}
        DateTimeMode: UnspecifiedLocal
        DefaultValue: {}
        Expression: ""
        ExtendedProperties: Count = 0
        MaxLength: -1
        Namespace: ""
        Ordinal: 3
        Prefix: ""
        ReadOnly: false
        Table: {Table}
        Unique: false
    Tuesday, September 6, 2016 3:38 PM
  • OK, I think you just need to add a call to FillSchema(), like this (haven't tested it, but I think it'll work):

    DataSet data = new DataSet();
    adapter.FillSchema(data, SchemaType.Source);
    adapter.Fill(data);
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, September 6, 2016 3:49 PM
  • I tried FillSchema but am getting the same result.

    As for using

    dataTable.Columns["Column2"].DefaultValue = -2;

    I'd really like to avoid requiring the code that gets the DataTable and adds rows to know what the defaults are.


    Tuesday, September 6, 2016 5:34 PM
  • Darn, I'm sorry ... I wasn't sure if it would work. I know you're using Access, but I just tried it in SQL Server and it doesn't work there either.

    In SQL Server, there are ways to find defaults in a database by querying the sysobjects database table, but I wouldn't have any idea where to find that information in Access. Maybe an Access person can jump in here, or maybe you can post to an Access Forum (sorry, I don't know where that is off the top of my head).

    Sorry.  =0(


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, September 6, 2016 6:09 PM
  • Hi W-c-v,

    If you want to retrieve default value from database, please use the following SQL statement:

    SELECT 
       TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
    FROM 
       INFORMATION_SCHEMA.COLUMNS
    WHERE 
       TABLE_NAME = 'TablesColumnWidths' 

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 7, 2016 6:08 AM
    Moderator
  • What I’ve finally settled on is

    DataTable schemaTable = Connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,new object[] {null,null,tableName,null});

    DataRow[] rows = schemaTable.Select("ORDINAL_POSITION > 0","ORDINAL_POSITION ASC");

    The returned rows contain the default values.  It’s not what I was originally hoping for, but it will work.  Time to move on.

    Friday, September 9, 2016 1:44 PM
  • Hi M-c-v,

    I am glad to know you solved this problem and thanks for sharing the solution. 

    It will be very beneficial for other community members who have the similar questions. 

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 12, 2016 6:10 AM
    Moderator