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

[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!

[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

[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

[lumberjack]

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]

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