Re: Help! ADO Connection Problem..

From: Val Mazur (group51a_at_hotmail.com)
Date: 03/27/04 

Date: Fri, 26 Mar 2004 23:35:27 -0500

Hi,

If you even cannot instantiate variable of ADO connection, then it is
possible that MDAC is corrupted on your PC.
"Operation is not allowed when the object is closed." error message means
that your application tries to read some data from the recordset's fields,
but recordset is closed. It looks like it was not opened and your
application does not handle this situation. Why it was not opened is another
store. If connection was not opened, then recordset will be closed as well.
But if you are sure that opening of the recordset was successful, then you
need to specify how you opened it. 

-- 
Val Mazur
Microsoft MVP
"matt" <matthias.aerni@cablecom.ch> wrote in message 
news:vaq860dnld9mkshphk3o2alrdv38fvatn3@4ax.com...
>
> Hello,
>
> I wote an ISAPI Application(C++) with ADO access to MS SQL-Server.
> While testing I did not have any Problems.. everything worked great!
>
> But now on the productive system with many concurrent Users I get
> a lot of Errors!
>
> com_error
> " (0x800A0E78) Operation is not allowed when the object is closed."
> or
> " (0x800A0E7D)
> The connection cannot be used to perform this operation. It is either
> closed or invalid in this context."
>
> and when there a lot of Errors occur even my ASP-Pages fail..
> Set adoCon = Server.CreateObject("ADODB.Connection")
> Failed to create Object.
>
>
> I tried to be  smart an put a reconnect() method into the prepare
> function see below..(UserManager.cpp)
>
> now it works a bit better but there are still a lot of these errors I
> think I do something completly wrong..
>
> I use the Class like this..
>
> UserManager userMgr(dbDataSource,db,dbUserId,dbPwd);
> ...
> ..
> userMgr.updateLastMessage(username);
> ..
> ..
> StringMap bannedUser=userMgr.getBannedUserList(username);
> ..
> ..
> userMgr.closeConnetion()
>
> sometimes I loose the connection even between
> prepareCommand() and. getOpenRecordSet()!?
>
> I have no idea!
>
> any help would be great..
>
> thanks,
> matt
>
>
>
>
> ------------------------- UserManager.cpp ----------------------
> // ADO User Manager
> //
>
>
> _ConnectionPtr pConnection;
> HRESULT  hr;
>
> inline void TESTHR(HRESULT x)
> {
> if FAILED(x)
> _com_issue_error(x);
> };
>
> UserManager::UserManager(string in_dataSource,string in_db,string
> in_user,string in_pwd)
> {
>
> dataSource=in_dataSource;
> db=in_db;
>    user=in_user;
> pwd=in_pwd;
>
> currentDateTime = COleDateTime::GetCurrentTime();
> setConnection(dataSource,db,user,pwd);
> hr = S_OK;
>    CoInitialize(NULL);
> pConnection  = NULL;
>
> try
>    {
>        //Open a connection
>        TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
>
> pConnection->Open(dbConnection.data(),"","",adConnectUnspecified);
> }
> catch(_com_error &e)
>    {
>
> if (pConnection)
>        if (pConnection->State == adStateOpen)
> pConnection->Close();
>
>   //throw ("DB Error in UserManager::UserManager ->open
> connection");
>    }
> catch(...)
>    {
> if (pConnection)
>        if (pConnection->State == adStateOpen)
> pConnection->Close();
> //throw ("Unhandled Exception in
> UserManager::UserManager -> open connection");
>
> }
> }
>
>
> UserManager::~UserManager(void)
> {
> if (pConnection)
>        if (pConnection->State == adStateOpen)
> pConnection->Close();
>
> ::CoUninitialize();
> }
>
>
> void UserManager::reconnect() {
>
> setConnection(dataSource,db,user,pwd);
>
> hr = S_OK;
>
>    CoInitialize(NULL);
>
> pConnection  = NULL;
>
> try
>    {
>        //Open a connection
>        TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
>
> pConnection->Open(dbConnection.data(),"","",adConnectUnspecified);
> }
> catch(_com_error &e)
>    {
>
> if (pConnection)
>        if (pConnection->State == adStateOpen)
> pConnection->Close();
>    }
> catch(...)
>    {
> if (pConnection)
>        if (pConnection->State == adStateOpen)
> pConnection->Close();
> }
> }
>
> void UserManager::closeConnection() {
>
> if (pConnection)
>        if (pConnection->State == adStateOpen)
> pConnection->Close();
>
> ::CoUninitialize();
>
> }
>
> void UserManager::setConnection(string aDataSource,string aDb,string
> aUser,string aPwd)
> {
> dbConnection="Provider=sqloledb;";
> dbConnection.append("Data
> Source=").append(aDataSource).append(";");
> dbConnection.append("Initial
> Catalog=").append(aDb).append(";");
> dbConnection.append("User Id=").append(aUser).append(";");
> dbConnection.append("Password=").append(aPwd).append(";");
> }
>
> void UserManager::cleanUp(_RecordsetPtr pRecordset) {
> cleanUp(pRecordset,NULL);
> }
> void UserManager::cleanUp(_CommandPtr pCmd) {
> cleanUp(NULL,pCmd);
> }
> void UserManager::cleanUp(_RecordsetPtr pRecordset,_CommandPtr pCmd) {
>
> if (pRecordset)
> if (pRecordset->State == adStateOpen)
> pRecordset->Close();
> if (pCmd)
> if (pCmd->State == adStateOpen)
> pCmd->Release();
> }
> _CommandPtr UserManager::prepareCommand(CString& strQuery)
> {
> _CommandPtr pCmd;
>
>    try
>    {
> if (pConnection) {
> if (pConnection->State != adStateOpen)
> reconnect();
>
> }else{
> reconnect();
> }
>
> pCmd.CreateInstance(_uuidof(Command));
> pCmd->ActiveConnection=pConnection;
> pCmd->CommandText= (_bstr_t) strQuery;
> }
> catch(_com_error &e)
>    {
> cleanUp(pCmd);
> //throw ("DB Fehler in UserManager::isInBanList");
> //PrintComError(e);
>
>    }
>    catch(...)
>    {
> cleanUp(pCmd);
> //throw ("Unhandled Exception in
> UserManager::isInBanList");
>    }
>  return pCmd;
> }
> _RecordsetPtr UserManager::getOpenRecordSet(_CommandPtr pCmd)
> {
> _RecordsetPtr pRecordset;
>
>    try
>    {
> pRecordset.CreateInstance(__uuidof(Recordset));
> pRecordset->CursorLocation=adUseClient;
> pRecordset->Open((IDispatch*)pCmd,
> vtMissing,
> adOpenStatic,
> adLockOptimistic,
> adCmdUnknown);
> }
>    catch(_com_error &e)
>    {
> cleanUp(pRecordset);
> //throw ("DB Fehler in
> UserManager::getOpenRecordSet");
>    }
>    catch(...)
>    {
> cleanUp(pRecordset);
> //throw ("Unhandled Exception in
> UserManager::getOpenRecordSet");
>
>    }
>
>  return pRecordset;
> }
>
> void UserManager::doExecuteSql(CString &strQuery,CString &methodeName)
> {
> FILETIME ftStart = getFileTime();
>
> _RecordsetPtr pRecordSet=NULL;
> _CommandPtr pCmd=NULL;
>    try
>    {
>        pCmd=prepareCommand(strQuery);
>        pRecordSet=getOpenRecordSet(pCmd);
> cleanUp(pRecordSet,pCmd);
>    }
>    catch(_com_error &e)
>    {
> cleanUp(pRecordSet,pCmd);
>
>    }
> catch(...)
>    {
> cleanUp(pRecordSet,pCmd);
>
> }
> }
>
>
> StringMap UserManager::getBannedUserList(long id) {
>
> FILETIME ftStart = getFileTime();
>
> StringMap banList;
>
> CString strQuery;
> strQuery.Format("SELECT * FROM stUserUserBan WHERE Author_ID =
> %d AND Type=1",id);
>
> _RecordsetPtr pRecordset=NULL;
> _CommandPtr pCmd=NULL;
>
> try
>    {
> pCmd=prepareCommand(strQuery);
> pRecordset=getOpenRecordSet(pCmd);
>
> _variant_t vBanUsername;
>
> while (!pRecordset->ADOEOF)
> {
>
> vBanUsername=pRecordset->Fields->GetItem(L"BanUsername")->GetValue();
> string banUser=static_cast<char
> *>(_bstr_t(vBanUsername));
>
> banList.insert(value_type (string
> (banUser.data()),string (banUser.data())));
> pRecordset->MoveNext();
> }
>
> cleanUp(pRecordset,pCmd);
> }
> catch(_com_error &e)
>    {
> cleanUp(pRecordset,pCmd);
> //throw ("DB Fehler in
> UserManager::getBannedUserList");
>    }
>    catch(...)
>    {
> cleanUp(pRecordset,pCmd);
>
>    };
> return banList;
> }
>
> void UserManager::updateLastMessage(char* username)
> {
> CString methodeName("updateLastMessage");
>
> string sUsername=username;
>
> int id=lookupUserAccountID(sUsername);
>
> CString strQuery;
>
> strQuery.Format("Execute UpdateLastWrite
> @strUsername='%s'",username);
> doExecuteSql(strQuery,methodeName);
> }
>
>
> void UserManager::updateUserDetail(UserDetail &aUserDetail)
> {
> CString methodeName("updateUserDetail");
>
> COleDateTime out=aUserDetail.lastLogout;
> COleDateTime in=aUserDetail.lastLogin;
> COleDateTime last=aUserDetail.lastMessage;
>
> //prepare sql statement
> CString strQuery;
> strQuery.Format("Update stUserDetail"
> " SET Retry_Counter=%d,"
> " GrantedLogins=%d,"
> " Last_Login='%d-%d-%d %d:%d:%d',"
> " Last_Logout='%d-%d-%d %d:%d:%d',"
> " LastMessage='%d-%d-%d %d:%d:%d'"
> " WHERE Author_Id=%d"
>
> ,aUserDetail.retryCounter
>
> ,aUserDetail.grantedLogins
>
> ,in.GetYear(),in.GetMonth(),in.GetDay(),in.GetHour(),in.GetMinute(),in.GetSecond()
>
> ,out.GetYear(),out.GetMonth(),out.GetDay(),out.GetHour(),out.GetMinute(),out.GetSecond()
>
> ,last.GetYear(),last.GetMonth(),last.GetDay(),last.GetHour(),last.GetMinute(),last.GetSecond()
>
> ,aUserDetail.getAuthorId());
>
> //execute
> doExecuteSql(strQuery,methodeName);
> }
>
> UserAccount UserManager::getUserAccount(const char* username)
> {
> FILETIME ftStart = getFileTime();
>
> UserAccount useraccount;
>
> _RecordsetPtr pRecordset=NULL;
>
> _CommandPtr pCmd=NULL;
>
> //Interface Pointer declared.(VC++ Extensions)
> IADORecordBinding *picAuthorRs = NULL;
>
>    try
>    {
> //C++ class object
> CAuthorRs authrs;
>
> CString strQuery;
> strQuery.Format("SELECT * FROM tblAuthor WHERE
> username='%s'",username);
>
> pCmd=prepareCommand(strQuery);
> pRecordset=getOpenRecordSet(pCmd);
>
> //Open an IADORecordBinding interface pointer which
> we'll use for Binding
>        //Recordset to a class
>
> TESTHR(pRecordset->QueryInterface(__uuidof(IADORecordBinding),(LPVOID*)&picAuthorRs));
>
>        //Bind the Recordset to a C++ Class here
>        TESTHR(picAuthorRs->BindToRecordset(&authrs));
>
> //Get the Fields collection from the recordset
> FieldsPtr pFields=pRecordset->Fields;
>
> //From the Fields collection, get the desired Field
> FieldPtr pFieldId=pFields->GetItem("author_id");
>
> _variant_t vId;
>
> if (!pRecordset->ADOEOF)
>        {
> //useraccount.setId(authrs.m_id);
> vId=pFieldId->Value;
> useraccount.setId(static_cast<int>(int(vId)));
> useraccount.setUsername(authrs.m_username);
> useraccount.userCode=authrs.m_User_code;
> useraccount.setPassword(authrs.m_Password);
> useraccount.setSalt(authrs.m_Salt);
>
> useraccount.author_email=authrs.m_Author_email;
>
> useraccount.active=bool((_variant_t)authrs.m_active);
>
> if (picAuthorRs)
> picAuthorRs->Release();
> cleanUp(pRecordset,pCmd);
>
> }; //end if (!pRecordset->ADOEOF)
>    }
>    catch(_com_error &e)
>    {
>        if (picAuthorRs)
> picAuthorRs->Release();
> cleanUp(pRecordset,pCmd);
> //throw ("DB Fehler in UserManager::getUserAccount");
>    }
>    catch(...)
>    {
>     if (picAuthorRs)
> picAuthorRs->Release();
>
> cleanUp(pRecordset,pCmd);
>
> //throw ("Unhandled Exception in
> UserManager::getUserAccount");
>    };
>
>  return useraccount;
>
> }
> ------------------------- UserManager.cpp ----------------------

Relevant Pages