Solved

Sql Server Identity Fields and Delphi 3

Posted on 1998-11-24
8
286 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
Independent Software Vendors: 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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

726 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