Board index » delphi » How to insert in a row with an Identity column in SQL Server

Fidel Lagos

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

How to insert in a row with an Identity column in SQL Server

If I have a grid in delphi with an Identity column in SQL Server , when i
try to execute a Post, I obtain the folowing message

If I don't define a value, I obtain the folowing message "Field Must Have a
value"
If I  define a value, I obtain the folowing message "key violation"

thanks for any help

Fidel Lagos

 
Ron Brow

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How to insert in a row with an Identity column in SQL Server


First, ensure your identity column has a value for the identity seed and
identity increment.  Don't use tables, use queries and for convenience,
ensure that your identity field occupies the last position in the table so
that you can create clean queries without offering up a value.
Regards,
r..
Quote
Fidel Lagos <fla...@axisgroup.cl> wrote in message

news:874718$1km13@bornews.borland.com...
Quote
> If I have a grid in delphi with an Identity column in SQL Server , when i
> try to execute a Post, I obtain the folowing message

> If I don't define a value, I obtain the folowing message "Field Must Have
a
> value"
> If I  define a value, I obtain the folowing message "key violation"

> thanks for any help

> Fidel Lagos

Richard Proudfoo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How to insert in a row with an Identity column in SQL Server


Fidel,

Because in SQLServer the Identity field may not be null, the BDE interprets
this as being a 'required' field and will not allow you to post an insert
without a value. The BDE does not know that the value will be supplied by
SQL Server. The fix is simple, you need to reset the 'Required' property of
the field in the dataset to 'False'. Then you can post  your inserts without
a problem.

Regards,
Richard

Matt Taylo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How to insert in a row with an Identity column in SQL Server


When inserting into SQLServer tables with identity column you must first turn
off the identity mechanism before insert the row.  This can be done by sending
the SQL instruction "set identity_insert <tablename> on" prior to commencing
your inserts.  After completing the inserts you should then run "set
identity_insert <tablename> off" to restore the identity mechanism on the table.
Quote
Richard Proudfoot wrote:
> Fidel,

> Because in SQLServer the Identity field may not be null, the BDE interprets
> this as being a 'required' field and will not allow you to post an insert
> without a value. The BDE does not know that the value will be supplied by
> SQL Server. The fix is simple, you need to reset the 'Required' property of
> the field in the dataset to 'False'. Then you can post  your inserts without
> a problem.

> Regards,
> Richard

Richard Proudfoo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How to insert in a row with an Identity column in SQL Server


Matt,

This is news to me!!

I have many inserts into many SQL Server tables with identity fields, all of
which work without turning off the identity mechanism.

Can you explain why you need to do this?

Regards,
Richard

Quote
Matt Taylor wrote in message <38A127E0.E619C...@clear.net.nz>...
>When inserting into SQLServer tables with identity column you must first
turn
>off the identity mechanism before insert the row.  This can be done by
sending
>the SQL instruction "set identity_insert <tablename> on" prior to
commencing
>your inserts.  After completing the inserts you should then run "set
>identity_insert <tablename> off" to restore the identity mechanism on the
table.

>Richard Proudfoot wrote:

>> Fidel,

>> Because in SQLServer the Identity field may not be null, the BDE
interprets
>> this as being a 'required' field and will not allow you to post an insert
>> without a value. The BDE does not know that the value will be supplied by
>> SQL Server. The fix is simple, you need to reset the 'Required' property
of
>> the field in the dataset to 'False'. Then you can post  your inserts
without
>> a problem.

>> Regards,
>> Richard

Other Threads

1. MSSQL Identity columns, and Insert SQL

2. SQL Server Column IDENTITY Property with Delphi 2

3. Sql Server 6 & Identity columns

4. SQL Server Identity columns problem in Delphi 6.

5. SQL Server: Identity column problem

6. Identity columns on SQL Server 6.5

7. Ms SQL server 7.0 and Identity columns

8. Insert record w/SQL Server identity field

9. MS SQL Server Insert a record with a IDENTITY field

10. using ttable/tdataset to insert rows into sql server