Invalid variant type cast error when inserting a row in mssql server 2012 table with identity column

[xutanou]

Good afeternoon everyone, here goes my question:
I'm connecting to a mssql server 2012 with Zeos 7.2.14 (using ADO) and lazarus 2.2.6 all under win 10 64.
The table that I have to access (and that i can't modify its structure, because it is part of a huge database that is not mine):
column 2 is defined like: Column2 int IDENTITY(1,1) NOT NULL,

As you can see, column2 has an identity value, and here is where I have the problem. When i do an insert like:

when I try to insert some values (not in column 2)
the result is always :

"INVALID VARIANT TYPE CAST" and I have to cancel the programm.
After canceling the programm, if I run it again, the row has been inserted with the proper "nextvalue" on column 2.

IS THERE A WAY TO INSERT ROWS INTO SUCH TABLE WITHOUT HAVING THIS MESSAGE?, what I'm missing?

p.d.: after inserting all my stuff, I've to modify the number located in the identity, for this i created another TZQuery with one line:
DBCC CHECKIDENT('Mydatabase.dbo.Mytable', RESEED,mynewnumber);
I execute it with Tzquery.execsql and the value is updated (after a significant time such 2-3 minutes). If i close and resopen the program is immediate. THIS IS ANOTHER MISTERY.

Any help will be apreciated, thank you in advance

[aehimself]

"INVALID VARIANT TYPE CAST"

I think the issue won't be the identity column, but something else. Try to debug on which column it gives this error? We'll also need a table create script and a small program which the issue occurs on.

I execute it with Tzquery.execsql and the value is updated (after a significant time such 2-3 minutes). If i close and resopen the program is immediate. THIS IS ANOTHER MISTERY.

Transactions? Try commiting after execution.

[marsupilami]

Hello xutanou,

"INVALID VARIANT TYPE CAST"

This usually is a programming error in the user program - including Zeos. So I agree with aehimself: we need an example on how to reproduce the error.

[xutanou]

Good morning, thank you for your fast response

I just was looking at your solutions, and as aehimself suggested I tryed commiting after execution and it works !!!, Thanks.

I can't see any problem with other columns (maybe I'm not able to interpretate the debugger messages )

I did some more tests and always the same invalid variant error when inserting a new line. But the line is inserted....

As you requested, attached i send you the little aplication I use to do the tests.
Also inside the 'insertidentity' folder you will find the creation script for the table.
As you wuill see is a very simple table. There is only one condition that has to be considered, that the contents of one column has to exist in another table, but you can omit this constrain in your tests.

p.s. I did't include the "lib" folder becouse it is to large and when attached i get the message "too big".

waiting for your coments, thank you

[marsupilami]

Hello xutanou,

we did quite some debugging on my machine today to find out what happens. All this is about Zeos finding out the value of the identity column. There may be several things to consider:

1) ADO-ODBC-Bridge
The way you use the ADO driver you always use the ADO-ODBC-Bridge which is something you most probably don't want to do. On my computer we had proplems to always get the autoincrement value using the ADO-ODBC-Bridge. Using the Microsoft SQL Server driver solved this problem. Choose a modern provider if at all possible. On my computer the following connection string worked with Delphi:

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ZeosTest;Data Source=PAULCHEN\SQLEXPRESS2016

2) FPC support for the decimal data type in OleVariant
FPC doesn't support the Decimal data type in OleVariant. We had to implement a workaround for this to work on FPC. Please download the current SVN version of Zeos 7.2 from SVN or Github.

3) Your query in "ftic_cli_cat" may lead to problems. the where clause is 'where idclientecat=938'. SQL server might try to convert the values of idclientecat to integer instead of converting 938 to varchar. Please use quotes around 938 to make sure SQL Server knows that you want it to be trated as a string value.

Best regards,

Jan

[xutanou]

Thank you marsupilami for your response, when I have a little time I will check if I can implement your solutions and I let you know the results

thank you again