Click to See Complete Forum and Search --> : Can't edit Recordset after Execute.("CREATE TABLE")!!!!!


Finder
October 14th, 2002, 02:01 AM
Hi! I'm working with VC & ADO & MSSQL2000.
I found that it's impossible to edit a table via _RecordsetPtr after execution of _CommandPtr.Execute("CREATE TABLE").
Message: Item cannot be found in the collection corresponding to the requested name or ordinal.
ReConnect does not solve it. Properties _ConnectPtr and RecordsetPtr are not changed.
If run the program for second time (without CREATE TABLE), editing of any table works fine.
What mistake could I make?
Addition: editing of an existing table (not only created one) does not work too. But after executing of Execute("INSERT") existing tables can be edited normally.

::CoInitialize(NULL);
try{
CString ConnStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=MyDB;Data Source=FINDER";
CString userid = "sa"; CString paswd = "mypaswd"; CString TableName = "THandleAttr";
HRESULT hr;
_variant_t vCn;
_ConnectionPtr Cn;
_RecordsetPtr Rs;
_CommandPtr Cmnd;
// Connect
hr = Cn.CreateInstance(__uuidof(Connection)); if(FAILED(hr)) throw _com_error(hr);
hr = Cn->Open(_bstr_t(ConnStr),_bstr_t(userid),_bstr_t(paswd),adConnectUnspecified);
Cn->CursorLocation = adUseServer;//Cn->CursorLocation = adUseClient;
// CREATE TABLE TTmp
hr = Cmnd.CreateInstance(__uuidof(Command)); if(FAILED(hr)) throw _com_error(hr);
Cmnd->ActiveConnection = Cn;
Cmnd->CommandType = adCmdText;
Cmnd->CommandText = _bstr_t("CREATE TABLE TTmp (F_ID int NOT NULL DEFAULT 0)");
Cmnd->Execute(&vtMissing,&vtMissing,adCmdText);
// after executing of Execute("INSERT") existing tables can be edited normally.
//Cmnd->CommandText = _bstr_t("INSERT INTO THandleAttrDef (F_ISFREE) VALUES (0)");
//Cmnd->Execute(&vtMissing,&vtMissing,adCmdText);
// Reconnect
Cmnd = NULL; Cn->Close(); Cn = NULL;
::CoUninitialize();
Sleep(1000);
::CoInitialize(NULL);
hr = Cn.CreateInstance(__uuidof(Connection)); if(FAILED(hr)) throw _com_error(hr);
hr = Cn->Open(_bstr_t(ConnStr),_bstr_t(userid),_bstr_t(paswd),adConnectUnspecified);
Cn->CursorLocation = adUseServer;//Cn->CursorLocation = adUseClient;
// OPEN TABLE THandleAttr & AddNew()
hr = Rs.CreateInstance(__uuidof(Recordset)); if(FAILED(hr)) throw _com_error(hr);
Rs->CursorLocation = adUseServer; //Rs->CursorLocation = adUseClient;
Rs->CursorType = adOpenKeyset; //adOpenDynamic;
Rs->LockType = adLockBatchOptimistic;
vCn = Cn.GetInterfacePtr();
hr = Rs->Open(_bstr_t(TableName),vCn,adOpenStatic, adLockBatchOptimistic, adCmdTableDirect);
if(FAILED(hr)) throw _com_error(hr);
// IRowseChange & IRowsetUpdate == TRUE. //bool bAdd = Rs->Supports(adAddNew); == true.
// Next command call error!!!
Rs->AddNew(); // Error: Item cannot be found in the collection corresponding to the requested name or ordinal
// ...SetValue; Rs->UpdateBatch(adAffectCurrent);
Rs->Close();
// OPEN NEW TABLE TTmp & AddNew
hr = Rs.CreateInstance(__uuidof(Recordset)); if(FAILED(hr)) throw _com_error(hr);
Rs->CursorLocation = adUseServer; //Rs->CursorLocation = adUseClient;
Rs->CursorType = adOpenKeyset;
Rs->LockType = adLockBatchOptimistic;
vCn = Cn.GetInterfacePtr();
hr = Rs->Open(_bstr_t("TTmp"),vCn,adOpenKeyset, adLockBatchOptimistic, adCmdTable);
if(FAILED(hr)) throw _com_error(hr);
// Next command call error!!!
Rs->AddNew(); // Error: Item cannot be found in the collection corresponding to the requested name or ordinal
// ...SetValue; Rs->UpdateBatch(adAffectCurrent);
Rs->Close();
//
Cn->Close();
}
catch(_com_error e)

Help me!!!!!!!!! Please!!!!!!!!!