nvarchar empty string: " " not ""

[intersan]

I am writing a C++ application using ADO to manage an SQL Server dbase.
The database is set to compatibility version 80.
If I set an nvarchar field to an empty string ""; it is stored in the dbase as a
single space string: " ". I need it to be an empty string in the dbase so I can distinguish between an empty string and a single space string.

I am using the SQLOLEDB connector. According to the help that came with SQL Enterprise manager, if the dbase is set to compatibility level 70 or 80 then
"The empty string literal (' ') is interpreted as an empty string."
But it does not to make a difference.

Thanks,

 

[jbenson001]

Why not try using NULLS, this way there is no compatibily issue.

 

[intersan]

I am not sure how to set a value to null. I am using code like this:

cmdPtr->Parameters->Append(cmdPtr->CreateParameter(bstr_t(m_name), ADODB::adBSTR, ADODB::adParamInput, m_length, parameter->m_variant));

How do I add a NULL parameter?

intersan

 

[jbenson001]

Check the value you want and try this

<parameter name>.value = IIf(<some string> = "", Convert.DBNull, <some string>)

If the value of the string is "" it will be assigned NULL, other wise it will be set with the strings value.

 

[intersan]

I figured it out. Thanks for the assistance. A helpful link
is

http://support.microsoft.com/?kbid=229088&sd=msdn

I did the following which works:

m_variant.ChangeType(VT_NULL);
cmdPtr->Parameters->Append(cmdPtr->CreateParameter(bstr_t(m_name), ADODB::adBSTR, ADODB::adParamInput, m_length, m_variant));