Developer Forum

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

> 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?

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

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.

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

> FROM

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

A

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.

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.

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?

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://www.yqcomputer.com/ (en-US,SQL.90).aspx

> Here <TableName> is the name of table for which you want to find the

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.

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" < XXXX@XXXXX.COM > schreef in bericht

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


"Jeff Johnson" < XXXX@XXXXX.COM > schreef in bericht

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.

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?).



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.


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://www.yqcomputer.com/ (en-US,SQL.90).aspx

"David Portas" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

Well, I do exactly the same in my VB program, using the ADO Fields
collection. I build the entire SQL statement and exclude the Identity, which
is also the PK.



The reason I write a generic routine is because I use it in
drag/drop/copy/cut/paste operations in my program. By cutting/copying (for
instance via dragging/dropping) from a listview/treeview into a listview or
a treeview. In this case I copy the project and paste it over the
Projectgroup (which is also visualized in the treeview) I want to have it
under.
And Projects aren't the only items I want to handle this way.

Thanks for your input, David.