ODBC Error Message when inserting

[cajsoft]

Hi Folks,
I'm trying use a USERCONNECTION in VB 6 to access my SQL database. I'm not using Stored procedure, I just write the SQL code into the "User Defined SQL" part of the userconnection.

The problem is that I'm trying to insert a new record into a table (EventCause) that has 3 fields (CauseID,Faultid,FaultCause), but I keep getting an error from ODBC :-
"Attempting to insert explicit value for indetity column in table "EventCause" when IDENTITY_INSERT is set to OFF"

What does this mean??? How can I set the identity to ON (if thats the case) from my "User-Defined SQL"

User-Defined SQL
----------------

Insert Into Eventcause(CauseID,FaultID,FaultCause) VALUES (?,?,?)

N.B. The '?' are being passed from the calling statement as parameters

Thanks,
Craig.

[Clunietp]

How is that field defined in your database? What database version is it?

Have you tried NOT using a userconnection? Just try it using the ADO objects (use a connection.execute or a command object)

[JHausmann]

You have two choices.

1) you can use the set command

set identity_insert on (or off)

2) you can remove the identity column from your insert statement. You haven't indicated which column is the identity field, so for grins let's pretend it's "CauseId". The following SQL should work:

Insert Into Eventcause(FaultID,FaultCause) VALUES (?,?)

The real question is, why are you trying to insert a record that has a value in a field (identity=autonumber) you want SQL to assign?

[cajsoft]

Thanks, I don't know what I was thinking.. I didn't really understand what a IDENTITY was until now.

I removed the field and viola! , works like a dream..

Cheers
Craig.