Insert into SQL 2k table

[Guest]

I designed an append query in Access to add records to a SQL table. The PK in
the SQL table is an identity column. There are no null values in any column
or record. I get an error message "you tried to assign a null value to a
variable that is not variant type data". I presume this is because the
identity column is not included in my statement. I expected it to
automatically add and increment the identity column (as happens in Access).
How should I handle this? Here is my SQL statement:

INSERT INTO ApptIDConnect ( DB1, AppointmentID1, DB2, AppointmentID2 )
SELECT c.DB1, c.AppointmentID1, c.DB2, c.AppointmentID2
FROM ApptIDConnectLocal c;

Thanks once again!!

 

[Douglas J. Steele]

There is no Variant data type for fields in tables: it's only adata type for
variables. That implies to me that there's something in your code that it's
complaining about, not your SQL statement.

What does the code that uses that SQL statement look like?

 

[Rick Brandt]

I designed an append query in Access to add records to a SQL table. The PK in
the SQL table is an identity column. There are no null values in any column
or record. I get an error message "you tried to assign a null value to a
variable that is not variant type data". I presume this is because the
identity column is not included in my statement. I expected it to
automatically add and increment the identity column (as happens in Access).
How should I handle this? Here is my SQL statement:

INSERT INTO ApptIDConnect ( DB1, AppointmentID1, DB2, AppointmentID2 )
SELECT c.DB1, c.AppointmentID1, c.DB2, c.AppointmentID2
FROM ApptIDConnectLocal c;

Excluding the IDENTITY column from the statement and letting the server
auto-populate it should work. Are you sure there aren't any other columns left
out of the statement that are required?

Are there any triggers on the table?

 

[Rick Brandt]

There is no Variant data type for fields in tables: it's only adata type for
variables. That implies to me that there's something in your code that it's
complaining about, not your SQL statement.

While that is correct there are ODBC errors that will use that description
rather than simply stating "you tried to assign a null value to a required
field". I know that I have seen it on numerous occasions.

I guess there's no law saying that people who write error message descriptions
have to know what they're talking about

 

[Guest]

Sorry for the trouble. I found the problem. There was a single record with a
single column null out of hundreds of records. I thought I'd checked that.