Solved

Sql Server Identity Fields and Delphi 3

Posted on 1998-11-24
8
280 Views
Last Modified: 2010-04-04
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
Comment
Question by:jaumeg
8 Comments
 
LVL 1

Expert Comment

by:EmmDieh
ID: 1347797
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
 
LVL 4

Expert Comment

by:dwwang
ID: 1347798
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
 
LVL 3

Expert Comment

by:Stefaan
ID: 1347799
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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:jaumeg
ID: 1347800
dwwang:

Where I have to set the Required property to false? in Delphi, in SQL Server?
0
 
LVL 1

Accepted Solution

by:
Gerhard100198 earned 200 total points
ID: 1347801
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
 
LVL 4

Expert Comment

by:dwwang
ID: 1347802
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
 

Author Comment

by:jaumeg
ID: 1347803
dwwang:

Where I have to set the Required property to false? in Delphi, in SQL Server?
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1347804
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question