• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Sql Server Identity Fields and Delphi 3

I have a table with a identity field used as primary key.
Using de tDBGrid I try to insert a new record without specifying the identity field, but I get the
error that "I must to specify a value for the field". And if I specify a value for the field, I get the error than I can't to specify a value for a identity field.

TIA.
0
Jaume Gelabert
Asked:
Jaume Gelabert
1 Solution
 
EmmDiehCommented:
Set the Required property of this field to false and
add a generator and a before insert trigger on the table if you haven't already.
0
 
dwwangCommented:
In sql server, generator is not needed, since Auto-inc is supported by SQL Server. SO you only need to set the Required property to False;
0
 
StefaanCommented:
Hi Jaumeg,

The problem here is that the Query needs the value of your Primary Key before it posts the data to the SQL Server.  But SQL Server only assigns a identity field when the record is posted.  That (and the rumour that the Identity field isn't 100% safe, it can get messed up at some time) made me use another method.  I added a table to my SQL Server Database which has 2 fields.  One is the Table Name, the other is the last used Record ID.  What I did next is write a stored procedure which gets the next Record ID for a specified Table and returns that.  In delphi, I call this Stored Procedure each time the user wants to add a record to a certain table (mostly in the OnNewRecord event of a TQuery component), that way I have my Record ID before I post the record to the Server and I'm sure that I always have a good RecordID.  This method is a good alternative to using SQL Server's Identity field.

If you would like more information (code, ...) on this method, feel free to contact me.

Greetings and best regards,


Stefaan Lesage

E-Mail : Stefaan.Lesage@eca.be
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jaume GelabertAuthor Commented:
dwwang:

Where I have to set the Required property to false? in Delphi, in SQL Server?
0
 
Gerhard100198Commented:
I'm assuming that you're using a TQuery to link to the tables.

Double click on the TUpdateSQL component. This will bring up a Query editor.
Click on the SQL tab. You will see three radio boxes (update, insert, delete). Make sure that the field name of your autoinc field is not part of the update and insert query.

What is happeing is the following:
If you do a "select * from..." query then the TUpdateSQL component will update ALL field in your table, including the AutoInc field. Those field you don't actively enter information for will be posted with the original value (in the case of a new record this is Null). By removing the field name from the update/insert query Delphi will not try and post info the the AutoInc field.

If you're using a TTable component you could try the following (I have not tested this myself but because of the underlying way TTable works it should solve your problem):

Double click on the TTable component. The field editor will now be opened.
Now right click on the field editor and select "Add fields"
Select all the fields you would like to be able to access but make sure you do not select the AutoInc field.

In theory this will also stop Delphi from trying to update the AutoInc field since behind the scenes TTable uses SQL to update the tables. Since you specify which fields you would like to access the TTable component should only update those specified fields.

Hope this helps.

Gerhard


0
 
dwwangCommented:
In Delphi, if you defined the field in the fields editor(by right click the query component), just click the field in the field editor, and then press F11. In the property list, you can find the Required property.

But actually, if you defined fields for the query, you can just delete the field in the fields editor, then every thing would be OK.

0
 
Jaume GelabertAuthor Commented:
dwwang:

Where I have to set the Required property to false? in Delphi, in SQL Server?
0
 
dwwangCommented:
Hi, hopefully you should see my comment above(the one just above your last comment), since you post this comment twice.

regards,
Wang
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now