MBS; SQLCommandMBS; setAsNull; MS SQL Server 2019 10.0 64;Win10; Xojo2023r1.1;

Add-Ons
1

Hello,

I have the following SQL:
insert into Test(Nr, Description, DT) values(1, 'test 1', null)

DT is of type (binary(4), null).
I check the parameters for SQLCommandMBS by VarType which gives Nil for DT and set parameter with setAsNull().

Executing the Cmd I get the following error:
42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Commands could not be prepared.
42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The implicit conversion from varchar data type into binary is not allowed. Use the CONVERT function to execute this query.

How can I insert null into a column of type (binary(4), null)?
I can not see where the type varchar comes from.

To reproduce the error a simple insert null into a (binary(4), null) column should work.

2

The same line works in other tools?

You could try with

Convert(varbinary(max), NULL)

but it sounds strange to have a typed NULL.

3

Of course it works; simply the column is not given in an insert.
Value inserted would then be null.
But when I call an explicit setAsNull() in SQLParamMBS, the error appears.

As a workaround, I have to split the SQL for null and non null values and omit value if it is null.

Anyway, using binary/varbinary seems not to be a good idea.

Thanks a lot for the example.