I have been using parameterized queries (using QueryDef objects against a 2.x database using DAO 3.6) extensively for queries, updates, and deletions. However, I ran into a case where I could not do a parameterized insertion of a zero-length string. For example, the following code does not work (it inserts NULL):
----
Dim oQD as QueryDef
Set oQD = oDB.CreateQueryDef("", "parameters sData string;" & _
" insert into whatever ( fldx ) values ( [sData] )")
oQD.Parameters!sData = ""
oQD.Execute dbFailOnError
----
[oDB is a class level variable of type Database - again, using DAO 3.6 against a Jet 2.x MDB]
While investigating, I added a watch on oQD. The type of the field is dbString, and "AllowZeroLength" is true. At first, "Required" was false, but I tried again after setting it to true as well, but it had no effect.
As you can see, I am explicitly storing a zero length string ("") into the sData parameter, but it stores null in the table. I have even checked the value of "oQD.Parameters!sData" and the debugger tip shows null AFTER storing "" to it. I have checked the type of the parameter and it is a string parameter. Does anyone know how to store a parameterized empty string using a QueryDef? I don't want to store a space or anything stupid like that because the table is going to have an EXTREME record count (2 million+) - I can't afford to waste any space. I have to avoid storing null because other team members code is not handling null correctly and crashing with "Invalid use of null" and it would be counterproductive to demand changes to established code.
I have tried replacing [sData] in the querydef with """" (a pair of double-quotes) and it DOES work - it stores a zero length string. I need to know because I won't always be storing an empty string.
Thanks!
Start Free Trial
