Determine whether a field in recordset is a Identity Field

I don't believe ADO exposes IDENTITY through the recordset object. One
method to identify the IDENTITY column is with a query like the example
below. No rows will be returned if no IDENTITY column exists on the
specified table.

SELECT name
FROM dbo.syscolumns
WHERE
id = OBJECT_ID('dbo.MyTable') AND
COLUMNPROPERTY(id, name, 'IsIdentity') = 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bart Steur" wrote in message

> Hi,
>
> I'm using VB6 and I want to know which Column in the recordset is the
> Identity Column (if any).
>
> I've a SQL Server 2000 table. It has 5 colums, the first one is the
> Identity Column.
> Now I want to recognize this column in VB6 using ADO 2.8. I used the
> recordset fields collection, but it does not contain that information.
>
> The reason I want to know this is because I want to copy a record in that
> table.
>
> I tried : INSERT INTO myTable SELECT * FROM myTable WHERE myID = 822
>
> but then it responds with: An explicit value for the identity column in
> table 'tblAttachments' can only be specified when a column list is used
> and IDENTITY_INSERT is ON.
>
> But I want it to insert the copied record with an new identity. So I want
> to use :
>
> INSERT INTO myTable (fld02, fld03,fld04, fld05) SELECT fld02, fld03,
> fld04, fld05 FROM myTable WHERE myID = 822
>
> Any ideas on both issues?
>
> thx,
>
> Bart
>
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

Bart,

Since the IDENTITY field concept is vendor specific, this is a case where
the INFORMATION_SCHEMA won't help.

I would create this view in the DB in question. Then I could query it to
find which columns in any table in a DB is an IDENTITY field.

CREATE VIEW dbo.vIdentityColumns AS
SELECT
SO.Name AS TableName
,SC.Name AS ColumnName
FROM
sys.columns SC
INNER JOIN sys.OBJECTS SO
ON SC.object_id = SO.object_id
WHERE
SC.is_identity = 1


To answer the second part,

Run this before doing your insert:

SET IDENTITY_INSERT dbo.myTable ON

And make sure you turn it off again immediately afterwards.

I have to ask, are you planning to delete the original record after this
insert? I would be worried if I had rows in a table where the only
differentiator is the synthetic key.

Just my opinion ....

Bob

"Bart Steur" wrote:

> Hi,
>
> I'm using VB6 and I want to know which Column in the recordset is the
> Identity Column (if any).
>
> I've a SQL Server 2000 table. It has 5 colums, the first one is the Identity
> Column.
> Now I want to recognize this column in VB6 using ADO 2.8. I used the
> recordset fields collection, but it does not contain that information.
>
> The reason I want to know this is because I want to copy a record in that
> table.
>
> I tried : INSERT INTO myTable SELECT * FROM myTable WHERE myID = 822
>
> but then it responds with: An explicit value for the identity column in
> table 'tblAttachments' can only be specified when a column list is used and
> IDENTITY_INSERT is ON.
>
> But I want it to insert the copied record with an new identity. So I want to
> use :
>
> INSERT INTO myTable (fld02, fld03,fld04, fld05) SELECT fld02, fld03, fld04,
> fld05 FROM myTable WHERE myID = 822
>
> Any ideas on both issues?
>
> thx,
>
> Bart
>
>
>
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

"Bart Steur" wrote in message


> Now I want to recognize this column in VB6 using ADO 2.8. I used the
> recordset fields collection, but it does not contain that information.

Are you sure? How about checking the Attirbutes property of the Field object
to see if the adFldRowID flag is set. I haven't tried this myself, but it
seems like the best bet.
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

Here is a simple code, hope it should help you out


Private sub FindPrimaryKey()
dim rs as Adodb.Recordset
Dim PrimaryKeyColumnName as string
Dim PrimaryKeyColumnOrdinal as integer

Set rs = dbConnection.OpenSchema(adSchemaPrimaryKeys, Array(Empty,
Empty, "<TableName>"))

'Put appropriate check to ensure that recordset is not empty or else
you will encounter an error
if (rs.EOF and rs.BOF) = false then
rs.MoveFirst
PrimarykeyColumnName = rs.Fields("COLUMN_NAME").value
PrimaryKeyColumnOrdinal = rs.Fields("ORDINAL").value
end if

rs.close
set rs = nothing
End Sub


Here <TableName> is the name of table for which you want to find the
primary key. You should replace it with your own table name.

dbConnection is an open ADODB.Connection, you can use any existing
connection declared or create a new one.

If it still doesn't work, or you need some further explaination, feel
free to ask me.
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

Bart Steur wrote:
> You're right, at first it's creating redundant data, but after copying, the
> user has the ability to edit some of those fields.
>

So you are making it impossible to implement any business key
constraints on the table, which means the table WILL fill up with
redundant garbage. You are also incurring the cost of two updates where
one would do. Doesn't make any kind of sense to me. Data integrity
should be your first concern.

What I also don't understand is why you don't KNOW which column will be
an IDENTITY? Why bother trying to determine that at runtime?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

> FROM
> sys.columns SC

The OP is using SQL Server 2000, not SQL Server 2005.

A
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

> Here <TableName> is the name of table for which you want to find the
> primary key. You should replace it with your own table name.

Doesn't this assume that the identity column is the primary key? While
that's common, it's certainly not guaranteed.

I agree with David, in that, you should be able to write code that *knows*
what column is the identity. You shouldn't have to figure that out at
runtime.
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

Bart Steur wrote:
> Let me explain and maybe there is another way to solve this properly,
> because I do understand you wonder why.
>
> We have certain Projects. These are stored in a Table called tblProjects.
> It contains the following Columns:
>
> ProjectID Identity/Primary Key
> ProjectGroupID Owner of the Projects
> ProjectName Description/Name
> ProjectType
> FormatID
> Priority
> AllowExtract
> AllowExport
> MaxBatchSize
> ExportFormatID
> Status
> ServiceLevel
> Mnemonic
>
> A ProjectGroup can have 1 to 10 Projects. When defining a project, all above
> values are set by the user. After that the user copies the created project
> and most of the type only modifies the ProjectName, Mnemonic, FormatID and
> ExportFormatID. The rest stays the same.
> The other fields will/can change while the project is being processed at our
> Production Department. The status will change as the process changes, a
> production controller can change the priority, servicelevel, Allow bits and
> batches. In some cases the Format and Exportformats are the same for certain
> projects.
> So there's the reason why I want to copy certain records and create
> redundant data.
>

Use an INSERT statement in a stored procedure and pass in the new name,
etc as parameters. For example:

INSERT INTO Projects
(ProjectGroupID, ProjectName, ProjectType, FormatID...)
SELECT ProjectGroupID, @NewProjectName, ProjectType, @NewFormatID, ...
FROM Projects
WHERE ProjectID = @OldProjectID ;

Now there need be no redundancy and you can add any keys and other
business rules (unique ProjectName for example?).


> The reason I want to know the Identity is because I want to write a generic
> routine to copy records and want te be able to exclude the identity column.
>

It is always better to avoid dynamic SQL code where you can. Common
practice for UI code is to create a separate insert, update and delete
procedure for each table. With a bit of effort you can make a few
scripts to help you generate those procs automatically from metadata.

By comparison a generic insert proc would have many disadvantages. For
example it would make code harder to maintain because every schema
change would presumably have to be reflected in the way your
application code calls the proc. One of the many advantages of (static)
procs is that you can protect your application from the effect of
certain schema changes.

Hope this helps.


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

You're right, at first it's creating redundant data, but after copying, the
user has the ability to edit some of those fields.

The most important part of the post is to find out which column is the
identity column, so I can exclude it from the fields part. I some cases I
will programmaticly changes some values.


"Aaron Bertrand [SQL Server MVP]" wrote in message

>> But I want it to insert the copied record with an new identity.
>
> WHY? Do you really want to fill a table with redundant data?
>
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

Let me explain and maybe there is another way to solve this properly,
because I do understand you wonder why.

We have certain Projects. These are stored in a Table called tblProjects.
It contains the following Columns:

ProjectID Identity/Primary Key
ProjectGroupID Owner of the Projects
ProjectName Description/Name
ProjectType
FormatID
Priority
AllowExtract
AllowExport
MaxBatchSize
ExportFormatID
Status
ServiceLevel
Mnemonic

A ProjectGroup can have 1 to 10 Projects. When defining a project, all above
values are set by the user. After that the user copies the created project
and most of the type only modifies the ProjectName, Mnemonic, FormatID and
ExportFormatID. The rest stays the same.
The other fields will/can change while the project is being processed at our
Production Department. The status will change as the process changes, a
production controller can change the priority, servicelevel, Allow bits and
batches. In some cases the Format and Exportformats are the same for certain
projects.
So there's the reason why I want to copy certain records and create
redundant data.

The reason I want to know the Identity is because I want to write a generic
routine to copy records and want te be able to exclude the identity column.

Any suggestion to do this another (maybe better or more efficient) way,
please let me know.

Bart.

PS. There are more tables/views that depend on tblProjects and tblProjects
depends multiple tables.


"David Portas" schreef in bericht

> Bart Steur wrote:
>> You're right, at first it's creating redundant data, but after copying,
>> the
>> user has the ability to edit some of those fields.
>>
>
> So you are making it impossible to implement any business key
> constraints on the table, which means the table WILL fill up with
> redundant garbage. You are also incurring the cost of two updates where
> one would do. Doesn't make any kind of sense to me. Data integrity
> should be your first concern.
>
> What I also don't understand is why you don't KNOW which column will be
> an IDENTITY? Why bother trying to determine that at runtime?
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

I checked the attributes property, but wasn't able to determine whether it
was an Identity column or not.


"Jeff Johnson" schreef in bericht

> "Bart Steur" wrote in message
>
>
>> Now I want to recognize this column in VB6 using ADO 2.8. I used the
>> recordset fields collection, but it does not contain that information.
>
> Are you sure? How about checking the Attirbutes property of the Field
> object to see if the adFldRowID flag is set. I haven't tried this myself,
> but it seems like the best bet.
>
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

"Bart Steur" wrote in message

>I checked the attributes property, but wasn't able to determine whether it
>was an Identity column or not.
>
>
> "Jeff Johnson" schreef in bericht
>
>> "Bart Steur" wrote in message
>>
>>
>>> Now I want to recognize this column in VB6 using ADO 2.8. I used the
>>> recordset fields collection, but it does not contain that information.
>>
>> Are you sure? How about checking the Attirbutes property of the Field
>> object to see if the adFldRowID flag is set. I haven't tried this myself,
>> but it seems like the best bet.
>>
>
>

Try ISAUTOINCREMENT property of Field object, like here:

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Initial
Catalog=Northwind;Data Source=(local);Integrated Security=SSPI;"
cn.Open
Set rst = cn.Execute("Select * From employees", , adCmdText)
For Each fld In rst.Fields
If fld.Properties("ISAUTOINCREMENT") Then
Debug.Print "Identity column: " & fld.Name
Exit For
End If
Next fld

End Sub

Dmitriy.
 >> Stay informed about: Determine whether a field in recordset is a Identity Field 

I think you should carry out a little research on OpenSchema method of
ADO connection. It offers a good deal of information from which you can
find out somehting of your interest.

In my previous solution, I have constrained the results of the
OpenSchema results by applying the filter which I thought, will be
useful to you. But if you are looking some other information about
schema, you should go through the implementation of OpenSchema method
which I hope will certainly help you.
 >> Stay informed about: Determine whether a field in recordset is a Identity Field