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?
 
Roman GhermanConnect With a Mentor Senior Software EngineerCommented:
An apostrophe is missing before JACK'.
Try this

INSERT INTO WEBSHOP VALUES(0, 'JACK 110316', '', '0' ,'JACK', '05.0425', '110316',  0)
0
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
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
 
BIAPROAuthor Commented:
great !!   working perfect!  thanks a lot!
Regards Jack
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.