Can't write a zero-length string to a database using a parameterized query.

[cpuwzd]

Using the Database Connectivity Toolset, it is sometimes desirable to write high-precision numeric values to a database. In order to avoid conversion to text form, with its associated loss of precision, I have been using parameterized queries. However, I just ran into a problem while writing a mixture of variable types using this method. Specifically, if you pass a string of length zero, you get an error code of -2146824580: ("Exception occured in ADODB.Recordset, Parameter object is improperly defined. Inconsistent or incomplete information was provided.") I suspect that this is an error in ADO, although it is possible that this is a LabVIEW problem. I'm using LabVIEW 6.1 under Windows 200
0. I've attached files illustrating the problem and my suggested workaround. The front panels of each of the VI's explain how to reproduce the problem and demonstrate the solution.

[Cyril]

cpuwzd,

By definiton of the ADO layer, when doing Parameterized Queries, you must specify all parameter values.If you leave them empty, it results in ADO trapping errors rather than using a default (or NULL) value. Since this is defined by ADO as an error, there is nothing that can be done from the LabVIEW side. To avoid that:
1) Use SQL statements rather than parameterized queries if empty values have to be sent.
2) Check the string length to make sure none of the entries are null, or append a know character like a space or tab to all string inputs.

Regards,

Cyril Bouton
Applications Engineer
National Instruments

[cpuwzd]

Cyril,

It is easy to distinguish a null variant from an empty string. An empty string is no more a null value than zero is a null value when considering a numeric field. If this feature is truly by design, it is a sad decision.

Ron Martin (cpuwzd)