Finding Identity fields with ADO/ADOX 
Author Message
 Finding Identity fields with ADO/ADOX

I'm trying to determine whether a field in my SQLServer 7/SP2 database is an
identity column. I have created a table as follows:

    CREATE TABLE [dbo].[TestTable] (
     [field1] [int] IDENTITY (1, 1) NOT NULL ,
     [field2] [int] NOT NULL
    ) ON [PRIMARY]

I've tried looping through each column in this table using ADO and ADOX, and
have not been able to find any way to identify the Identity column.

The way I thought would work would be as follows:

    Dim adoConnection As New ADODB.Connection
    Dim adoxCat As New ADOX.Catalog

    adoConnection.Open "Provider=SQLOLEDB;" _
            & "Persist Security Info=False;User ID=sa;" _
            & "Initial Catalog=MyDB;Data Source=VICTIM1_MyPC"

    Set adoCat.ActiveConnection = adoConnection

    Msgbox
adoCat.Tables("TestTable").Columns("field1").Properties("AutoIncrement").Val
ue

I expected this to return True or False (it works fine in Access), but I
actually get run-time error 3251 (Object or provider is not capable of
performing requested operation).

The only other thing I've been able to think of is to open a schema
recordset in ADO and examine the COLUMN_FLAGS field:

    Dim rs As ADODB.Recordset
    Set rs = adoConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty,
"TestTable", Empty))
    Do Until rs.EOF
        MsgBox rs("COLUMN_NAME") & " " & rs("COLUMN_FLAGS")
        rs.MoveNext
    Loop

This does actually return a different value for the identity column...
ColumnFlags returns 0x14 for a non-identity column (which evalues to fixed
length and writeable), whereas the identity column returns 0x10 (fixed
length, not writeable). That seems a bit of a flimsy method to use to
determine whether an int field is an identity column or not.

Could someone please let me know if there is a better way to do this? I'm
pulling my hair out trying to get this resolved, and ADO/ADOX aren't helping
me at all...

My thanks in advance,

--

Adam.



Sat, 15 Mar 2003 03:00:00 GMT
 Finding Identity fields with ADO/ADOX

Quote:

> I'm trying to determine whether a field in my SQLServer 7/SP2 database is an
> identity column. I have created a table as follows:

>     CREATE TABLE [dbo].[TestTable] (
>      [field1] [int] IDENTITY (1, 1) NOT NULL ,
>      [field2] [int] NOT NULL
>     ) ON [PRIMARY]

> I've tried looping through each column in this table using ADO and ADOX, and
> have not been able to find any way to identify the Identity column.

The only way to tell is to look at the system tables:

  select tbl_name     = so.name  , -- object name
         col_name     = sc.name  , -- column name
         col_position = sc.colid , -- column ordinal position
  from sysobjects so
  join syscolumns sc on sc.id = so.id
  where so.type = 'U'                  -- user tables only
    and 0x80    = ( sc.status & 0x80 ) -- hi-order bit of status indicates
                                       --   that the column has the
                                       --   identity property

You can probably get at it via the ANSI schema views as well[1], but they're
based on the above. See Books online/T-SQL reference for information on the
system tables' schema.

[1] but I wouldn't bet on it as the identity property is not an ANSI property.

N.
--



Sat, 15 Mar 2003 03:00:00 GMT
 Finding Identity fields with ADO/ADOX

Adam,

Can I suggest a different approach ...

select   COLUMN_NAME
from     INFORMATION_SCHEMA.COLUMNS
where  TABLE_SCHEMA = 'dbo'
and      TABLE_NAME   = 'TestTable'
and      COLUMNPROPERTY(object_id('TestTable'), COLUMN_NAME, 'IsIdentity') =
1

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I'm trying to determine whether a field in my SQLServer 7/SP2 database is
an
> identity column. I have created a table as follows:

>     CREATE TABLE [dbo].[TestTable] (
>      [field1] [int] IDENTITY (1, 1) NOT NULL ,
>      [field2] [int] NOT NULL
>     ) ON [PRIMARY]

> I've tried looping through each column in this table using ADO and ADOX,
and
> have not been able to find any way to identify the Identity column.

> The way I thought would work would be as follows:

>     Dim adoConnection As New ADODB.Connection
>     Dim adoxCat As New ADOX.Catalog

>     adoConnection.Open "Provider=SQLOLEDB;" _
>             & "Persist Security Info=False;User ID=sa;" _
>             & "Initial Catalog=MyDB;Data Source=VICTIM1_MyPC"

>     Set adoCat.ActiveConnection = adoConnection

>     Msgbox

adoCat.Tables("TestTable").Columns("field1").Properties("AutoIncrement").Val

- Show quoted text -

Quote:
> ue

> I expected this to return True or False (it works fine in Access), but I
> actually get run-time error 3251 (Object or provider is not capable of
> performing requested operation).

> The only other thing I've been able to think of is to open a schema
> recordset in ADO and examine the COLUMN_FLAGS field:

>     Dim rs As ADODB.Recordset
>     Set rs = adoConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty,
> "TestTable", Empty))
>     Do Until rs.EOF
>         MsgBox rs("COLUMN_NAME") & " " & rs("COLUMN_FLAGS")
>         rs.MoveNext
>     Loop

> This does actually return a different value for the identity column...
> ColumnFlags returns 0x14 for a non-identity column (which evalues to fixed
> length and writeable), whereas the identity column returns 0x10 (fixed
> length, not writeable). That seems a bit of a flimsy method to use to
> determine whether an int field is an identity column or not.

> Could someone please let me know if there is a better way to do this? I'm
> pulling my hair out trying to get this resolved, and ADO/ADOX aren't
helping
> me at all...

> My thanks in advance,

> --

> Adam.



Sat, 15 Mar 2003 03:00:00 GMT
 Finding Identity fields with ADO/ADOX

Thank you both for your answers!

Best regards,
--

Adam.


Quote:
> I'm trying to determine whether a field in my SQLServer 7/SP2 database is
an
> identity column. I have created a table as follows:

>     CREATE TABLE [dbo].[TestTable] (
>      [field1] [int] IDENTITY (1, 1) NOT NULL ,
>      [field2] [int] NOT NULL
>     ) ON [PRIMARY]

> I've tried looping through each column in this table using ADO and ADOX,
and
> have not been able to find any way to identify the Identity column.

> The way I thought would work would be as follows:

>     Dim adoConnection As New ADODB.Connection
>     Dim adoxCat As New ADOX.Catalog

>     adoConnection.Open "Provider=SQLOLEDB;" _
>             & "Persist Security Info=False;User ID=sa;" _
>             & "Initial Catalog=MyDB;Data Source=VICTIM1_MyPC"

>     Set adoCat.ActiveConnection = adoConnection

>     Msgbox

adoCat.Tables("TestTable").Columns("field1").Properties("AutoIncrement").Val

- Show quoted text -

Quote:
> ue

> I expected this to return True or False (it works fine in Access), but I
> actually get run-time error 3251 (Object or provider is not capable of
> performing requested operation).

> The only other thing I've been able to think of is to open a schema
> recordset in ADO and examine the COLUMN_FLAGS field:

>     Dim rs As ADODB.Recordset
>     Set rs = adoConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty,
> "TestTable", Empty))
>     Do Until rs.EOF
>         MsgBox rs("COLUMN_NAME") & " " & rs("COLUMN_FLAGS")
>         rs.MoveNext
>     Loop

> This does actually return a different value for the identity column...
> ColumnFlags returns 0x14 for a non-identity column (which evalues to fixed
> length and writeable), whereas the identity column returns 0x10 (fixed
> length, not writeable). That seems a bit of a flimsy method to use to
> determine whether an int field is an identity column or not.

> Could someone please let me know if there is a better way to do this? I'm
> pulling my hair out trying to get this resolved, and ADO/ADOX aren't
helping
> me at all...

> My thanks in advance,

> --

> Adam.



Sun, 16 Mar 2003 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Finding Identity fields with ADO/ADOX

2. Finding Identity fields with ADO/ADOX

3. find properties triggers in sqlserver with ADO, ADOX of system tables

4. ADO Finding Highest Number in ADO Recordset field?

5. How to find IDENTITY field in a query?

6. Finding the value of Identity fields after INSERT

7. Wrong Identity field returned from an ADO Recordset quering SQL - Causes

8. Identity Field in SQL Server 2000 and AddNew ADO Method

9. ADO not returning IDENTITY fields

10. ADO.AddNew and SQL Server Identity Field

11. ADO 2.7 mishandling Identity fields

12. how can i filter a identity field from table by ADO


 
Powered by phpBB® Forum Software