MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado

This forum is the place to discuss issues related to ReportPro, Xs2Ado, Vo2Ado, bBrowser and other 3rd party products
Post Reply
JKCanada604
Posts: 48
Joined: Wed Aug 11, 2021 11:03 am

MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado

Post by JKCanada604 »

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!
User avatar
wriedmann
Posts: 3631
Joined: Mon Nov 02, 2015 5:07 pm
Location: Italy

MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado

Post by wriedmann »

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
Wolfgang Riedmann
Meran, South Tyrol, Italy
wolfgang@riedmann.it
https://www.riedmann.it - https://docs.xsharp.it
Jamal
Posts: 314
Joined: Mon Jul 03, 2017 7:02 pm

MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado

Post by Jamal »

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
User avatar
lumberjack
Posts: 723
Joined: Fri Sep 25, 2015 3:11 pm

MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado

Post by lumberjack »

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.
Good choice to move away from DbServers... Myself and a couple others would ask why MSSQL though...
I do however need some help with the auto increment fields.
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.

In PG you can do:

Code: Select all

INSERT INTO <table> (<columns>) VALUES (<valuelist>) RETURNING <autoincCol>
DELETE FROM <table> WHERE <condition> RETURNING <autoincCol>
Both statements will return the autoincCol for INSERTED and DELETED rows applicable. It is giving you a 20x faster network traffic speed than any other RDBMS....

On top of these type of enhancements it is free...

HTH
JKCanada604
Posts: 48
Joined: Wed Aug 11, 2021 11:03 am

MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado

Post by JKCanada604 »

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
Post Reply