Solved

Sql Server Identity Fields and Delphi 3

Posted on 1998-11-24
8
283 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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