none
GetSchema COLUMN_FLAGS

    Question

  • Similar Request

    I made a similar request earlier for the DATA_TYPE property that is returned for the GetSchema method. My question now is what do the different COLUMN_FLAGS integer values represent?

    Cheers,
    Ryan

    Monday, April 23, 2007 8:14 PM

All replies

  • This might be my ignorance, but as far as I am aware your best bet is to dig around in the GetSchema help files on MSDN.  When I have had to work with it, I recollect having to dig through numerous MSDN pages to find what values mean what.

    Wish I could help more.

    AAron
    Monday, April 23, 2007 8:29 PM
  • Yeah I've had a bit of a time trying to figure out all this Schema stuff.  Can you at least point me to the MSDN articles that you looked at to retrieve some info.  Perhaps that will point me in the right direction.

    Thanks,
    Ryan
    Monday, April 23, 2007 8:55 PM

  • I believe that the values returned in the COLUMN_FLAGS column are OLEDB provider specific. It's used in conjunction with the DATA_TYPE column to identify the exact data type. For example, you could have a DATA_TYPE value that identifies a column data type of INTEGER and a COLUMN_FLAG which further identifies the column as an IDENTITY or AUTOINCREMENT field. 

    T
    he following article documents the mapping of SQL Server data types and the corresponding values of these two columns:

    Data Type Mapping with Distributed Queries 

    The following may help as well if you're working with an Excel file and the Jet OLEDB Provider:

    HOW TO: Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in Visual Basic .NET

     

    Tuesday, April 24, 2007 12:24 PM
  • The basic sql connection GetSchema page is here:

    http://msdn2.microsoft.com/en-us/library/ms136365.aspx


    Look at the bottom of the page under "Other Resources".  This is a good place to start digging.

    I would also check out this article and links within the text on this page:

    http://www.davidhayden.com/blog/dave/archive/2006/01/15/2734.aspx

    If all else fails you might have to discern it through debugging and trial and error.


    Tuesday, April 24, 2007 12:59 PM
  • Well I have not really gotten anywhere.  But since I'm running Access 2003 I'll post what little data I've gleaned through trial and error.

    If your DATA_TYPE is 3 and your COLUMN_FLAGS is 90 then you have an auto-increment.  The next one I want to figure out is if it is a unique column.
    Friday, May 04, 2007 6:44 PM
  • I realise this is an old thread but as i've had probems finding info i thought i'd share what i've found.

     

    The OleDb.OleDbType enum is your best bet at converting the Type codes into .NET data types.

    Secondly for unique and primary key information are all obtained from  GetSchema("Indexes"...)

     

    In Access 2003 it seems that you are correct in 90 being the flag for AutoNumbers.

    Thursday, November 27, 2008 8:37 AM
  • According to this http://msdn.microsoft.com/en-us/library/ms175970.aspx

    "For the COLUMNS schema rowset, the DATA_TYPE and COLUMN_FLAGS columns represent the DBTYPE and DBCOLUMNFLAGS values. For the IColumnsInfo::GetColumnInfo interface, the wType and dwFlags members of the DBCOLUMNINFO structure represent these values."

    Which leads me to find DBCOLUMNFLAGS Enumerated Type on this page

    http://msdn.microsoft.com/en-us/library/ms722704(VS.85).aspx

    little hunting and I find the enums listed here

    http://msdn.microsoft.com/en-us/library/ms716934(VS.85).aspx

    typedef DWORD DBCOLUMNFLAGS;
    enum DBCOLUMNFLAGSENUM {
         DBCOLUMNFLAGS_ISBOOKMARK,
         DBCOLUMNFLAGS_MAYDEFER,
         DBCOLUMNFLAGS_WRITE,
         DBCOLUMNFLAGS_WRITEUNKNOWN,
         DBCOLUMNFLAGS_ISFIXEDLENGTH,
         DBCOLUMNFLAGS_ISNULLABLE,
         DBCOLUMNFLAGS_MAYBENULL,
         DBCOLUMNFLAGS_ISLONG,
         DBCOLUMNFLAGS_ISROWID,
         DBCOLUMNFLAGS_ISROWVER,
         DBCOLUMNFLAGS_CACHEDEFERRED,
         DBCOLUMNFLAGS_SCALEISNEGATIVE,
         DBCOLUMNFLAGS_RESERVED,
         DBCOLUMNFLAGS_ISROWURL,
         DBCOLUMNFLAGS_ISDEFAULTSTREAM,
         DBCOLUMNFLAGS_ISCOLLECTION,
         DBCOLUMNFLAGS_ISSTREAM,
         DBCOLUMNFLAGS_ISROWSET,
         DBCOLUMNFLAGS_ISROW,
         DBCOLUMNFLAGS_ROWSPECIFICCOLUMN
    };

    now for the integer value it will be formed by a bit mask with the enums each representing another bit of the number. Easiest is not to mess with that, but rather check the value returned against your own bit mask formed by the enum above. If you don't know how to do that... well... you might want to post on a programming forum
    • Proposed as answer by jZe Monday, April 13, 2009 7:15 PM
    Monday, April 13, 2009 7:02 PM
  • using that enum your mask is just the sum over 2 ^ DBCOLUMNFLAGS_? 

    Wednesday, February 09, 2011 7:21 PM
  • Have some similar problems. I can't identify AutoNumber field in my Access Table.

    It does seem that 90 being the flag for AutoNumbers. But it's not usable. I mean AutoNumber does have COLUMN_FLAGS 90 and DATA_TYPE 3, but so do other fields.

    Has anyone solved this issue?

    Friday, June 29, 2012 10:34 AM
  • Found the way. Feel stupid now).

    If U use adapters FillSchema() method it will do it for U. I actually don't understant why it doesn't fill information about schema in DataTable when you "really" fill it with Fill() method. wasted a lot of time trying to detect autonumbers in access. anyhow here method i wrote, hope it'll save somebody time:

    public string GetAutonumberByTableName(string mdbTableName)
            {
                if (String.IsNullOrWhiteSpace(mdbTableName))
                    return null;
                
                if (this.dbConnection.State == ConnectionState.Closed)
                    this.dbConnection.Open();
                string res = null;
                try
                {
                    string statement = string.Format("SELECT * FROM [{0}]", mdbTableName);                
                    OleDbCommand command = new OleDbCommand(statement, this.dbConnection);
                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
    
                    DataTable schema = new DataTable();
                    adapter.FillSchema(schema, SchemaType.Source);
    
                    int found = 0; // the counter of autonumber columns
                    foreach (DataColumn col in schema.Columns)
                    {
                        if (col.AutoIncrement)
                        {
                            if ((++found) > 1)
                                throw new Exception("Cannot detect correctly autonumber column in "+mdbTableName+" table");
                            res = col.ColumnName;                        
                        }
                    }                
                    adapter.Dispose();
                    command.Dispose();
                    schema.Dispose();
                }
                catch (Exception e)
                {
                    // do some work with your Exception. I generated an event                  
                }
                finally
                {
                    this.dbConnection.Close();
                }
                return res;
            }


    Anthony

    Wednesday, July 18, 2012 3:17 PM
  • Very nice, Anthony. Even AccessDataSource does not correctly reflect AutoNumber/Unique (PrimaryKey) on Access 2003 mdb. You have saved me many hours of frustration with this. Very strange behavior, in my opinion...but now that I know how to get the correct column attributes, it will help me down the road. Thanks again. Here is my VB.NET version of your nice code block....

        Private Function GetAccessDataTableSchema(ByVal theConnectionString As String, _
                                                  ByVal theTable As String)
            Try
    
                Dim theConnection As New OleDbConnection(theConnectionString)
    
                theConnection.Open()
    
                Dim theSelect As String = String.Format("SELECT * FROM [{0}]", theTableName)
                Dim theOleDBCommand As New OleDbCommand(theSelect, theConnection)
                Dim theAdapter As New OleDbDataAdapter(theOleDBCommand)
    
                theSchemaTable = New DataTable
                theAdapter.FillSchema(theSchemaTable, SchemaType.Source)
    
    
                With DataGridView1
                    .DataSource = theSchemaTable
                    .Visible = True
                End With
    
                theAdapter.Dispose()
                theOleDBCommand.Dispose()
    
                Return True
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, _
                                "jinzai studio CSV Importer", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Exclamation)
            End Try
    
            Return False
        End Function
    

    • Edited by jinzai_studio Friday, September 21, 2012 5:21 AM add code block
    Friday, September 21, 2012 5:19 AM