Solved

Sql Server Identity Fields and Delphi 3

Posted on 1998-11-24
8
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Suggested Solutions

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…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

735 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