How to delete data using ADO ?

Question

0.00/5 (No votes)

See more:

I am not able to delete data using ADO ,Will you please help me ?
Code I written as follows

C++

#include <iostream>
#include <tchar.h>
#import " C:/Program Files/Common Files/System/ado/msado15.dll"  \
     rename( "EOF", "AdoNSEOF" )

_bstr_t bstrConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\\VCPrograms\\Other Trials\\Database2.accdb;";
 
    HRESULT hResult = CoInitialize( 0 );
    if( FAILED( hResult ))
    {
        return;
    }
    try
    {
	 ADODB::_ConnectionPtr pConnect("ADODB.Connection");
        hResult = pConnect->Open( bstrConnect, "admin", "",    
                  ADODB::adConnectUnspecified );
        if (SUCCEEDED(hResult))
        {
	   _bstr_t query = "SELECT * FROM Table1 WHERE EmpId = 1;";
            ADODB::_RecordsetPtr pRecSet( "ADODB.Recordset" );
            hResult = pRecSet->Open( query, _variant_t((IDispatch *) pConnect, true), ADODB::adOpenUnspecified,ADODB::adLockUnspecified, ADODB::adCmdText);
	if( SUCCEEDED( hResult ))
        {
	    pRecSet->Delete(ADODB::adAffectCurrent);
	    pRecSet->UpdateBatch(ADODB::adAffectCurrent);
	}
        pRecSet->Close();
        pConnect->Close();
	}
    }
    catch( _com_error& e )
    {
        // Handle Exception
    }
 
    // Release COM
    CoUninitialize();
}

Posted 2-Apr-12 0:04am

adityarao31

Updated 2-Apr-12 0:41am

Jochen Arndt

v3

Add a Solution

Comments

adityarao31 2-Apr-12 6:40am

Does ActiveX control - Microsoft ADO Data Control is available for VC++ 2010?
if yes how to get it,it does not get installed with VS 2010 professional edition

Jochen Arndt 2-Apr-12 6:48am

Where is the problem (which call fails with which HRESULT value)?
Or did you miss the ACE OLEDB driver? Then you may download the 'Microsoft Access Database Engine 2010 Redistributable' [^]

adityarao31 2-Apr-12 6:57am

Both hresults are returning success,Com error occurs at in Delete function,
I tested that query is successful
The code i tetsted like below,while debugging I found in delete function error occurs,but it is not tracable
ADODB::_ConnectionPtr pConnect("ADODB.Connection");
hResult = pConnect->Open( bstrConnect, "admin", "", ADODB::adConnectUnspecified );
if (SUCCEEDED(hResult))
{

_bstr_t query = "SELECT * FROM Table1 WHERE EmpId = 1;";
ADODB::_RecordsetPtr pRecSet( "ADODB.Recordset" );
hResult = pRecSet->Open( query, _variant_t((IDispatch *) pConnect, true), ADODB::adOpenUnspecified,ADODB::adLockUnspecified, ADODB::adCmdText);
if( SUCCEEDED( hResult ))
{
////////////////////////////////////////////////////////////////////////////////////////////////////////
ADODB::Fields* pFields = NULL;
hResult = pRecSet->get_Fields( &pFields );
long l = 1;
_bstr_t csname = _bstr_t(pFields->GetItem(l)->GetValue());
CString scname = (LPCTSTR)csname;
MessageBox(scname.GetBuffer(scname.GetLength()),_T(""),MB_OK);
///////////////////////////////////////////////////////////////////////////////////////////////////////////
pRecSet->Delete(ADODB::adAffectCurrent);
pRecSet->UpdateBatch(ADODB::adAffectCurrent);
temp = "Row deleted";
MessageBox(temp.GetBuffer(temp.GetLength()),_T(""),MB_OK);
}
pRecSet->Close();
pConnect->Close();
}
}
catch( _com_error& e )
{
// Handle Exception
}

// Release COM
CoUninitialize();

Jochen Arndt 2-Apr-12 12:42pm

If you would have used the Reply link below my comment, I would have get an email notification.

You may use the exception handler to show error information:
catch (_com_error& e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
CString strMsg;
strMsg.Format(
_T("COM Error\nSource: %hs\nDescription: %hs\nCode: %#X"),
(LPCSTR)bstrSource,
(LPCSTR)bstrDescription,
e.Error());
MessageBox(strMsg.GetString());
}

adityarao31 3-Apr-12 7:50am

COM Error
Source: ADODB.Recordset
Description: Current Recordset does not support updating.This may be a limitation of the provider or of selected locktype.Code: 0X800a0CB3

Jochen Arndt 3-Apr-12 8:29am

One reason may be that the database is write protected (you did not specify a password in the connect string).
You should also specify a cursor and locking mode (adOpenStatic or adOpenDynamic and adLockOptimistic) because using unspecified values may prevent from updating.

adityarao31 4-Apr-12 7:04am

Yes it seems to be problem of cursor and locking mode when I assigned
cursor adOpenDynamic OR adOpenStatic and lock adLockOptimistic the data gets deleted but it also throws following error

"COM Error
Source: Microsoft OfficeAccess Database Engine
Description : Multiple- step OLEDB operation generated errors, Check each OLEDB status value,if available.No work was done
code: 0x80040E21

Jochen Arndt 4-Apr-12 7:26am

Is the error from Delete() or UpdateBatch()? You should also use Update() here, because you have only one recordset here. To delete a specific recordset, you may also use a SQL command. I will add a solution with SQL delete.

adityarao31 3-Apr-12 8:00am

I googled with this information,but didnot find any thing helpful please help.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Accepted Answer · 2012-04-04T01:37:00

Besides the notes from my comments (specify cursor and lock modes, use Update() rather than UpdateBatch()), you may also use a SQL command to delete a specific recordset:

C++

ADODB::_CommandPtr pCmd = NULL;  
HRESULT hr = pCmd.CreateInstance(__uuidof( ADODB::Command));
if (FAILED(hr))
{
    throw _com_error(hr);
}   
_bstr_t bstrCmd = "DELETE FROM Table1 WHERE EmpId=1;";
pCmd->CommandText = bstrCmd;
pCmd->ActiveConnection = pConnnect;  
pCmd->Execute(NULL, NULL, ADODB::adCmdText);

When using Windows 7 SP1, see also the article Your ADO is broken[^] and this comment [^].