Good day to you all!
I am preparing for a big migration to X# and have decided to move this application from DBServers to MSSQL first.
I picked up Vo2Ado (thank you Robert for accommodating so quickly) and I am fumbling my way through.
I do however need some help with the auto increment fields.
I have a routine that reads and writes - never a problem reading however, when writing what I am supposed to do with this field?
When processing the data everything is fine if the record already exists however, if it is a new record nothing gets written!
Any guidance will be greatly appreciated!
Thank you and,
Cheers, from Mission BC. Canada!
MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado
-
- Posts: 49
- Joined: Wed Aug 11, 2021 11:03 am
MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado
Hi John,
autoincrement fields are not supposed to get written, and they have no special meaning other than being unique values.
Treat them like the recno in DBFs.
For keys you can read and write I would recommend GUIDs.
And please don't undervaluate indexes on often used search fields.
Wolfgang
autoincrement fields are not supposed to get written, and they have no special meaning other than being unique values.
Treat them like the recno in DBFs.
For keys you can read and write I would recommend GUIDs.
And please don't undervaluate indexes on often used search fields.
Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
wolfgang@riedmann.it
https://www.riedmann.it - https://docs.xsharp.it
Meran, South Tyrol, Italy
wolfgang@riedmann.it
https://www.riedmann.it - https://docs.xsharp.it
MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado
Hi John,
I don't use VO2Ado, but I use SQL Server a lot.
Briefly (based on what I know), Auto Increment column is an optional UNIQUE IDENTITY which is designated as a Primary Key that automatically starts with a seed value and is incremented by a value you choose when designing the database table. The default seed and increments value is 1. Normally, it would be simply named ID.
With INSERT command, do not specify (use) this columns in the query string (or parameters). SQL Server updates this columns automatically.
On UPDATE and DELETE commands, you may or may not specify the column in the WHERE condition; It all depends on what your conditions are supposed to be; If SQL Server find the row, it will process it.
Jamal
I don't use VO2Ado, but I use SQL Server a lot.
Briefly (based on what I know), Auto Increment column is an optional UNIQUE IDENTITY which is designated as a Primary Key that automatically starts with a seed value and is incremented by a value you choose when designing the database table. The default seed and increments value is 1. Normally, it would be simply named ID.
With INSERT command, do not specify (use) this columns in the query string (or parameters). SQL Server updates this columns automatically.
On UPDATE and DELETE commands, you may or may not specify the column in the WHERE condition; It all depends on what your conditions are supposed to be; If SQL Server find the row, it will process it.
Jamal
- lumberjack
- Posts: 726
- Joined: Fri Sep 25, 2015 3:11 pm
- Location: South Africa
MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado
Good choice to move away from DbServers... Myself and a couple others would ask why MSSQL though...JKCanada604 post=24482 userid=6470 wrote:
I am preparing for a big migration to X# and have decided to move this application from DBServers to MSSQL first.
PostgreSQL have some nifty extended SQL e.g. RETURNING which I find very useful with auto increments (PG uses [big]serial column type for this.I do however need some help with the auto increment fields.
In PG you can do:
Code: Select all
INSERT INTO <table> (<columns>) VALUES (<valuelist>) RETURNING <autoincCol>
DELETE FROM <table> WHERE <condition> RETURNING <autoincCol>
On top of these type of enhancements it is free...
HTH
______________________
Johan Nel
Boshof, South Africa
Johan Nel
Boshof, South Africa
-
- Posts: 49
- Joined: Wed Aug 11, 2021 11:03 am
MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado
Thank you to everyone!
I am simply not writing to this field and thing are making sense again.
This forum is great - thanks go to all!
Cheers, JK
I am simply not writing to this field and thing are making sense again.
This forum is great - thanks go to all!
Cheers, JK