Search code examples
c++sybaseado

ADO Command saying that I am inserting NULL, when I'm not

I'm using ADO to communicate with my Sybase server..

This is how I'm executing a simple command:

_ConnectionPtr m_ConnPtr;
//... Instantiate connection

_CommandPtr m_CommPtr;
m_CommPtr->CreateInstance(__uuidof(Command))
m_CommPtr->ActiveConnection = m_ConnPtr;

Variant m_variant;
m_variant.SetString("My Param Value");
_ParameterPtr ParamPtr;
ParamPtr = m_CommPtr->CreateParameter("@StrParam", (DataTypeEnum) m_variant.vt, adParamInput, NULL, m_variant);
m_CommPtr->Parameters->Append(PrmPtr);

m_CommPtr->CommandText = "EXECUTE my_stored_procedure @StrParam";

m_CommPtr->Execute(NULL, NULL, adOptionUnspecified);

@StrParam is supposed to be a VarChar type..

Running this gives me an error: 

Attempt to insert NULL value into column 'StrParam'. table 'MYTABLE'; 
column does not allow nulls. Update fails.

I'm not sure why I'm getting this error, since I am specifiying its value ("My Param Value")..

Does anyone know what I'm doing wrong?

(I didn't include the Stored procedure,, because I'm sure there's nothing wrong with the procedure itself.. Other application using the same procedure works fine. So there must be something wrong with how I'm using the parametized command)

Solution

  • I have no clue what your Variant class even is. But the traditional variant type (vt) and the ADO data type are not synonymous. Second, you're not setting up the call nor parameters correctly for a typical stored-proc invoke.

    Below is how you would do this using a standard stored proc call and variant_t from the comutil library:

    _CommandPtr m_CommPtr(__uuidof(Command));
m_CommPtr->ActiveConnection = m_ConnPtr;
m_CommPtr->CommandType = adoCmdStoredProc;
m_CommPtr->CommandText = L"my_stored_procedure";

// setup parameter
variant_t vParam = L"My Param Value";
_ParameterPtr ParamPtr = m_CommPtr->CreateParameter(L"@StrParam", adBSTR, adParamInput, 0, vParam);
m_CommPtr->Parameters->Append(ParamPtr);
m_CommPtr->Execute(NULL, NULL, adOptionUnspecified);

    Note that the ParamPtr is generally optional and you can straight-away append the parameter to the command's Parameters collection if you don't need it for anything else, like this:

    m_CommPtr->Parameters->Append(m_CommPtr->CreateParameter(
     L"@StrParam", adBSTR, adParamInput, 0, vParam));

    The method you're using is common for parameters that are both input and output, as you retain the parameter object reference to extract the output side of the parameter. I see no evidence of that in your call, which is the only reason I mention it here.

    Also note that unless the command returns rows for a result set you should also invoke with adExecuteNoRecords for the execution third option (which is typical for many fire-and-forget stored procedure executions)

    Finally, the names of the parameters are not important unless you use the NamedParameters property of the command object. This is commonly done when you have additional parameters with default values that you would like to retain, setting only specific parameters as part of your append list.

    Best of luck.