MS SQL auto increment problem

[abbath8]

Hi,

I have a Delphi7 app which uses dbexpress and an MS SQL2000 database. I want to handle a table's data, so I have an SQLQuery-provider-Clientdataset. I do a 'Select * from table1', and ok there is the data in the clientdataset. But if I want to insert to the table the id field (which is the primary key and auto increment in the database) is required. If I don't select the id field it's ok, but I need the id field value plenty of times.

So what is the solution? How to insert a clientdataset having auto increment fields? I always need the value of the id...so it must be selected...

 

[lespaul]

You're problem isn't very clear.

You always need the value of the id of the information you are selecting, but you need to insert some new data with the same number, but you need to have the auto-incrementing number.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[abbath8]

Ok, meanwhile I discovered that if I set the AutoGenerateValue of the sqlquery and the Clientdataset to arAutoinc then I can select all fields, and I can left the id field blank and it works, but I must refresh the dataset to get the id field value and to do this I must apply updates before. But applying updates after every record insert (to get the current record's id value) is not right, because it is an additional connection/database usage...

 

[bledazemi]

Please note the I have never used TClientDataset, TDatasetProvider myself. However I understand your problem. I had similar issue with a different database engine.

Auto-increment fields are auto-incremented by the database engine and the client application can not be sure what the Auto_increment value will be (until it applies changes) because while the client was dealing with its locally cached inserted record in its dataset, other clients may be adding who knows how many records.

So if you really really want to see the value of a newly inserted record in the dataset than i think you dont have any choice but to apply the insert before you can use the Auto_incremented field. Of course this will mean an extra database operation for every insert record just to get the Auto-increment field value. You may think of it as the price you pay to get it.

I am curious though why do you need to know the value of Auto-incremented field before it is actually saved? - Maybe what you should do is leave all places where you auto_incremented field value required in a pending state, until you are ready to save the newly added record to the database. Just after you save the new record to the database, you can then take the auto_incremented field value of it and use in those places that you left pending.

I hope this will help a little

Good luck