Administrator
  •  Admin
  • Advanced Member Topic Starter
2003-10-22T12:52:00Z
Hi

Your product look great and I hope that you can help me with the set up.

I am attempting to log onto an access database – set up is asking for a SQL statement in a box next to the unique ID. I am unfamiliar with SQL – what do I put in the box?
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
  •  Admin
  • Advanced Member Topic Starter
2003-10-22T12:58:00Z
Dear Peter,

you need enter the statement only if you plan store outlook contacts in the database.
(We need to know how to generate an unique value to insert a new record)

Example SQL Statements:
Note: SQL syntax is database dependent; these examples will not work for every database system!

Primary key
For integer fields:

Database Independent:
select max(YOUR_COLUMN_NAME) + 1 from YOUR_TABLE_NAME

MS Access:
select Iif(max(YOUR_COLUMN_NAME) is null, 0,max(YOUR_COLUMN_NAME) + 1)
from YOUR_TABLE_NAME

Last identity Value
SQL Server and MySQL:
select @@IDENTITY

For SQL Server GUID:
select newid()

Mandatory fields
For char fields:
select ‘always this text’
For integer fields
select 6
For current date
select GETDATE()


Whitehead, Roy
2003-11-08T12:39:00Z
Many databases offer the possibility of Auto-Incremtation for unique keys thereby eliminating the need to know a "Unique Value" beforehand.

Is there any way in which this option be turned off in order to eliminate this requirement.

Cordially Roy
Administrator
  •  Admin
  • Advanced Member Topic Starter
2006-03-01T01:52:00Z
We have imlemented support for auto-increment keys.
We will release it in version 3.0.0.5.

Administrator
  •  Admin
  • Advanced Member Topic Starter
2014-06-27T12:57:00Z
SQL Server Identity Concepts



To get the ID of the newly inserted record there are three methods available. Now the question arises what to use hence below is the description of each of them

@@IDENTITY

It returns the ID newly inserted record for any table for the current connection or session, but not the current scope. Means that it will return newly inserted even if it is inserted by a trigger or user defined function. Hence use if only when you do not have triggers or functions that run automatically.

SCOPE_IDENTITY()

It returns the ID of newly inserted for the table in the current scope and current connection or session. Means it will return the newly inserted ID of the record that is done by you using a stored procedure or query and not by automatic process like trigger. Hence many times to be safer one should use SCOPE_IDENTITY()

IDENT_CURRENT(’TableName’)

It returns the ID of the newly inserted record for the table specified. Here there is no constraint of scope and session It will give you the ID of the latest record for that table.