Errors when releasing ADO objects in Visual C++

[tempc]

Hi,

I am using Visual C++ 2008 and ADO to implement parameterized query to SQL Server 2008.

My new codes are below:

void CTestADOCmdDlg::OnBnClickedButton1()
{
// TODO: Add your control notification handler code here
_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);

ADODB::_ConnectionPtr Conn1;
ADODB::_CommandPtr Cmd1, Cmd2;
ADODB::_ParameterPtr Param1;
ADODB::_RecordsetPtr Rs1, Rs2;

CoInitialize(NULL);

// Trap any error/exception.
try
{
// Create and Open Connection Object.
Conn1.CreateInstance( __uuidof( ADODB::Connection) );
Conn1->ConnectionString = _bstr_t(L"Provider=sqloledb;Data Source=MYPC;Integrated Security=SSPI;");
Conn1->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );
Conn1->Execute(_bstr_t(_T("Create Database TestCmdDB2")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);
Conn1->Execute(_bstr_t(_T("Use TestCmdDB2")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);
Conn1->Execute(_bstr_t(_T("Create Table MyTable (MyID bigint);")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);

// Create Command Object.
Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
Cmd1->ActiveConnection = Conn1;
Cmd1->CommandText = _bstr_t(L"SELECT * FROM MyTable WHERE MyID = ?");

// Create Parameter Object.
Param1 = Cmd1->CreateParameter( _bstr_t(L""),
ADODB::adBigInt,
ADODB::adParamInput,
-1,
_variant_t( (LONGLONG) 0) );
Param1->Value = _variant_t( (LONGLONG) 0 );
Cmd1->Parameters->Append( Param1 );

// Create Command Object.
Cmd2.CreateInstance( __uuidof( ADODB::Command ) );
Cmd2->ActiveConnection = Conn1;
Cmd2->CommandText = _bstr_t(L"INSERT INTO MyTable VALUES(?);");

// Append parameter
Cmd2->Parameters->Append( Param1 );

for (LONGLONG nIndex = 0; nIndex < 65536; nIndex ++)
{
// Set parameter value
Param1->Value = _variant_t( (LONGLONG) nIndex);

// Open Recordset Object.
Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );

if (Rs1->BOF && Rs1->ADOEOF)
{
Rs2 = Cmd2->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );
// Rs2->Close();
Rs2 = NULL;
}

Rs1->Close();
Rs1 = NULL;
}

Param1->Release();
Param1 = NULL;
Cmd1->Release();
Cmd1 = NULL;

Cmd2->Release(); Cmd2 = NULL;
Conn1->Close();
Conn1 = NULL;

}
catch( CException *e )
{
e->Delete();
}
catch(_com_error& e)
{
CString strError = e.Description();

}
catch(...)
{
}

CoUninitialize();
}


Now I have 4 questions(sorry for so many):

1. Should I receive a recordset for executing a "INSERT INTO" statement" since it does not return any records? Currently I use Rs2 to receive it, though it is useless at all.

2. I try to close Rs2 gracefully by using Rs2->Close(), but that will cause an exception with description ""Operation is not allowed when the object is closed". However, my Rs2 is not closed yet, I justr try to close it.

3. I try to release all objects and set them to NULL. However, when executing the following line:

Cmd1 = NULL;

I will an exception and the source line is in Release() function of the com object. Why?

4. I see some parameterized query use ? as the parameter, while in others, they use parameter like @MyID. Are both correct way to paramerize a query?

Thank you very much

Alan

Continue reading...