CODEWITHSUNDEEP
c++sql-serverstored-proceduresado
n4meless0nly
n4meless0nly

Reputation: 194

How to pass null to stored procedure input parameter with ADO and C++

Technologies: Microsoft SQL Server 2008, ADO, C++.

Code:

pCommand->CommandType = ADODB::adCmdStoredProc;
pCommand->CommandText = "MyProc";

...

ADODB::_ParameterPtr pParam_ID = pCommand->CreateParameter("ID", 
    ADODB::adInteger, ADODB::adParamInput, sizeof(long));
hr = pCommand->Parameters->Append(pParam_ID);
if(FAILED(hr)) _com_issue_error(hr);
...
pCommand->Execute(NULL, NULL, 
    ADODB::adCmdStoredProc | ADODB::adExecuteNoRecords);

While pComand->Execute I get exception:

Code: 80040e10 (IDispatch error #3088)
Source: Microsoft OLE DB Provider for SQL Server
Desc: "Procedure 'MyProc' expects parameter '@ID', which was not supplied."

After adding the last parameter to CreateParameter:

ADODB::_ParameterPtr pParam_ID = pCommand->CreateParameter("ID", 
    ADODB::adInteger, ADODB::adParamInput, sizeof(long), _variant_t(0));

Stored procedure executes without errors, but @ID is not null.

From http://msdn.microsoft.com/en-us/library/ms675103%28VS.85%29.aspx

In C/C++, all operands must be specified. If you want to specify a missing parameter whose data type is a string, specify a _bstr_t containing a null string. If you want to specify a missing parameter whose data type is a Variant, specify a _variant_t with a value of DISP_E_PARAMNOTFOUND and a type of VT_ERROR. Alternatively, specify the equivalent _variant_t constant, vtMissing, which is supplied by the #import directive.

Any ideas?

Upvotes: 0

Views: 1516

Answers (1)

n4meless0nly
n4meless0nly

Reputation: 194

Solved:

_variant_t v_ID;
v_ID.vt = VT_NULL; // by default vt is VT_EMPTY

// by default if last parameter not provided 
// it equals vtMissing (it isn't VT_NULL equivalent)
ADODB::_ParameterPtr pParam_ID = pCommand->CreateParameter("ID", 
    ADODB::adInteger, ADODB::adParamInput, sizeof(long), v_ID);

Upvotes: 1

Related Questions