Welcome, Guest
Username: Password: Remember me
This forum is the place to discuss issues related to ReportPro, Xs2Ado, Vo2Ado, bBrowser and other 3rd party products
  • Page:
  • 1

TOPIC:

MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado 21 Nov 2022 02:13 #24482

  • JKCanada604
  • JKCanada604's Avatar
  • Topic Author


  • Posts: 29
  • 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!

    Please Log in or Create an account to join the conversation.

    MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado 21 Nov 2022 05:36 #24483

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3198
  • 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

    www.riedmann.it - docs.xsharp.it

    Please Log in or Create an account to join the conversation.

    MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado 21 Nov 2022 06:32 #24484

    • Jamal
    • Jamal's Avatar


  • Posts: 297
  • 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

    Please Log in or Create an account to join the conversation.

    Last edit: by Jamal.

    MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado 21 Nov 2022 09:51 #24492

    • lumberjack
    • lumberjack's Avatar


  • Posts: 714

  • 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:
    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
    ______________________
    Johan Nel
    George, South Africa

    Please Log in or Create an account to join the conversation.

    MSSQLSRV - Auto Increment - Identity - How to handle inserts - Vo2Ado 21 Nov 2022 10:24 #24494

    • JKCanada604
    • JKCanada604's Avatar
    • Topic Author


  • Posts: 29
  • 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

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1