SQL PERVASIVE , INSERT INTO , WITH A KEY BEING identity- type in VB6

I have a table where the index is identity type , thus autoincrement
in vaccess i was used to do a get last record first, then raised this index with 1 , then do a insert with new calculated value
max rexord +1,    in SQL I do not know how a SQL call should look like to get this done
either in VB6 or the pervasive utils    type 2000i  or V10 and V11
BIAPROAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roman GhermanSenior Software EngineerCommented:
Hi,

You can not perform an insert for a identity column in MS SQL, unless you use:

SET IDENTITY INSERT table ON
-- DO INSERT HERE
SET IDENTITY INSERT table OFF

However, why do you need to do that? MS SQL will auto increment that value, just do not include that identity column name in the insert statement.

--------------
You can contact me on: http://extremedev.blogspot.com/
0
BIAPROAuthor Commented:
is this also true for Pervasive ?
0
Roman GhermanSenior Software EngineerCommented:
Hi,
For Pervasive you need to put 0 for identity column:
--------------------
The following example shows what occurs when you use INSERT for IDENTITY columns and columns with default values.
CREATE TABLE t (id IDENTITY, c1 INTEGER DEFAULT 100)
INSERT INTO t (id) VALUES (0)
INSERT INTO t VALUES (0,1)
INSERT INTO t VALUES (10,10)
INSERT INTO t VALUES (0,2)
INSERT INTO t (c1) VALUES (3)
SELECT * FROM t
The SELECT shows the table contains the following rows:
1, 100
2, 1
10, 10
11, 2
12, 3
The first row illustrates that if “0” is specified in the VALUES clause for an IDENTITY column, then the value inserted is “1” if the table is empty.
The first row also illustrates that if no value is specified in the VALUES clause for a column with a default value, then the specified default value is inserted.
The second row illustrates that if “0” is specified in the VALUES clause for an IDENTITY column, then the value inserted is one greater than the largest value in the IDENTITY column.
More details on this site:
--------------------

You can find me on: http://extremedev.blogspot.com/
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

BIAPROAuthor Commented:
ok this is my record tabel
 WEBSHOP(BasketRecord,CustIDyymmdd,OrderNumber,StatusFlag,Customer,ItemNumber,Deliverydate,QTY)  "
These are the  FIELDS        identy key     ,  string              ,string              ,string        , string,        string,           string,          float

this i call
 INSERT INTO WEBSHOP VALUES(0, 'JACK        110316 ', '         ', '0' ,JACK        ', '05.0425       ', '110316',  0)

and give error    -2147217900


0
Roman GhermanSenior Software EngineerCommented:
An apostrophe is missing before JACK'.
Try this

INSERT INTO WEBSHOP VALUES(0, 'JACK 110316', '', '0' ,'JACK', '05.0425', '110316',  0)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BIAPROAuthor Commented:
great !!   working perfect!  thanks a lot!
Regards Jack
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.