ADO with extensions Update with Microsoft Access (Jet 4)

The following code is taken from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscexampleadowithextensions.asp but the provider is changes to be Microsoft.Jet.OLEDB.4.0 and the database is the Northwinds database you get with Office 2003.

I have two questions:

(1) [Which is less bothersome] How to I get to SELECT fields with spaces in them? [See my comments (1) in the source below.]

(2) [Which if the one that's really bothering me] How do I update the database, using CADORecordBinding? My updates don't seem to be committed. [Please see my comments (2) in the source below.]

--------8<--------
// Modified very little from
// This connects to the Northwind database

// Visual C++ Extensions Example
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \
   no_namespace rename("EOF", "EndOfFile")

#include <stdio.h>
#include <icrsint.h>
_COM_SMARTPTR_TYPEDEF(IADORecordBinding, __uuidof(IADORecordBinding));

inline void TESTHR(HRESULT _hr) { if FAILED(_hr) _com_issue_error(_hr); }

class CCustomRs : public CADORecordBinding
{
BEGIN_ADO_BINDING(CCustomRs)
   ADO_VARIABLE_LENGTH_ENTRY2(2, adVarChar, m_ch_company,
                        sizeof(m_ch_company), m_ul_companyStatus, false)
   ADO_VARIABLE_LENGTH_ENTRY2(3, adVarChar, m_ch_contact,
                        sizeof(m_ch_contact), m_ul_contactStatus, false)
END_ADO_BINDING()
public:
   CHAR    m_ch_company[50];
   CHAR    m_ch_contact[50];
   ULONG   m_ul_companyStatus;
   ULONG   m_ul_contactStatus;
};

void main(void)
{
   ::CoInitialize(NULL);
   try
      {
      _RecordsetPtr pRs("ADODB.Recordset");
      CCustomRs rs;
      IADORecordBindingPtr picRs(pRs);

/* --------8<-------- */
// (2) How can I get fields with spaces to work?
// Using SELECT * isn't very efficient, and I'd prefer to specify fields.
// My attempts to use square brackets haven't worked.
// I know that it isn't a good idea to have field names with spaces...
// .... but it would be nice to know how to do it all the same.
/* --------8<-------- */

      pRs->Open(
         "SELECT * FROM Customers",
         //"SELECT Customers.[Company Name] AS Company,Customers.[Contact Name] AS Contact FROM Customers",
         "Provider=Microsoft.Jet.OLEDB.4.0;"
       " Data Source=\"C:\\Program Files\\Microsoft Office\\OFFICE11\\SAMPLES\\Northwind.mdb\";",
         adOpenStatic, adLockOptimistic, adCmdText);

      TESTHR(picRs->BindToRecordset(&rs));

      while (!pRs->EndOfFile)
         {
      // Process data in the CCustomRs C++ instance variables.
         printf("Name = %s serviced by %s\n",
            (rs.m_ul_companyStatus == adFldOK ? rs.m_ch_company: "<Error>"),
            (rs.m_ul_contactStatus == adFldOK ? rs.m_ch_contact: "<Error>"));

/* --------8<-------- */

// (2) What do I need to do to get this to have its update saved to the database ?
// I can see my changes go in when I use pRs->MovePrevious(), but the
// changes are not committed to the database. I do not see "Laurence Lebihan"
// changes to "Fred Bassett". I'm not interested in doing this in SQL.
// I want to know how to do this using C++ binbing (i.e. CADORecordBinding).

         if (strcmp(rs.m_ch_contact,"Laurence Lebihan") == 0)
            {
            const char *fred = "Fred Bassett";
            printf("** Changing %s to %s **\n",rs.m_ch_contact,fred);
            strcpy(rs.m_ch_contact,fred);
            pRs->Update();
            }
/* --------8<--------- */

      // Move to the next row of the Recordset.
      // Fields in the new row will automatically be
      // placed in the CCustomRs C++ instance variables.

         pRs->MoveNext();
         }
      }
   catch (_com_error &e )
      {
      printf("Error:\n");
      printf("Code = %08lx\n", e.Error());
      printf("Meaning = %s\n", e.ErrorMessage());
      printf("Source = %s\n", (LPCSTR) e.Source());
      printf("Description = %s\n", (LPCSTR) e.Description());
      }
   ::CoUninitialize();
}
--------8<--------
LVL 17
rstaveleyAsked:
Who is Participating?
 
AxterConnect With a Mentor Commented:
Check out the following link for update example:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdatexvc.asp

Seems that the _Recordset type doesn't use the same rules as CRecordset, so you don't need to call Edit method.
The above link does seem to do some type of query call after the update.  I think that binds the updated data to the database.

        //Open an IADORecordBinding interface pointer which
        //we'll use for binding Recordset to a class.
        TESTHR(pRstEmployees->QueryInterface(
            __uuidof(IADORecordBinding),(LPVOID*)&picRs));
0
 
AxterCommented:
Hi rstaveley,
> >         if (strcmp(rs.m_ch_contact,"Laurence Lebihan") == 0)
> >            {
> >            const char *fred = "Fred Bassett";
> >            printf("** Changing %s to %s **\n",rs.m_ch_contact,fred);
> >            strcpy(rs.m_ch_contact,fred);
> >            pRs->Update();
> >            }

To update a database you first have to call the Edit member method.  Then call Update

            pRs->Edit();
            strcpy(rs.m_ch_contact,fred);
            pRs->Update();

David Maisonave :-)
Cheers!
0
 
AxterCommented:
>>// My attempts to use square brackets haven't worked.

Exactly how is it not working.

Have you tested it with fields that do not have spaces first, and then tested it with fields that do have spaces?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
AxterCommented:
>>"SELECT Customers.[Company Name] AS Company,Customers.[Contact Name] AS Contact FROM Customers"

For a derived CRecordset or CDaoRecordset, the select statement should be used to filter out imformation, or to change the order.

Your above select statement is being used to change the field names on the resulting query.
If you changed the field names, and your derived CRecordset class is still looking for the original names, it's going to cause a failure.
0
 
AxterCommented:
Continue....

Your derived CRecordset class should have something like the following:

void AccountSet::DoFieldExchange(CDaoFieldExchange* pFX)
{
    //{{AFX_FIELD_MAP(AccountSet)
    pFX->SetFieldType(CDaoFieldExchange::outputColumn);
    DFX_Text(pFX, _T("[Account]"), m_Account);
    DFX_Text(pFX, _T("[Hops]"), m_Hops);
    DFX_Text(pFX, _T("[AccntName]"), m_AccntName);
    DFX_Text(pFX, _T("[Comments1]"), m_Comments1);
    DFX_Text(pFX, _T("[Comments2]"), m_Comments2);
    DFX_Text(pFX, _T("[Channel]"), m_Channel);
    DFX_Text(pFX, _T("[MCASecurty]"), m_MCASecurty);
    DFX_Byte(pFX, _T("[AcctMess]"), m_AcctMess);
    DFX_Text(pFX, _T("[DateOpen]"), m_DateOpen);
    DFX_Text(pFX, _T("[DateUpdat]"), m_DateUpdat);
    DFX_Text(pFX, _T("[MiscUnknownBytes]"), m_MiscUnknownBytes);
    DFX_Bool(pFX, _T("[PulledByFirstPass]"), m_PulledByFirstPass);
    DFX_Bool(pFX, _T("[ValidFirstTwoDigitsInDateFields]"), m_ValidFirstTwoDigitsInDateFields);
    DFX_Bool(pFX, _T("[ProcessedByAutoFetch]"), m_ProcessedByAutoFetch);
    DFX_Bool(pFX, _T("[FoundSignatures]"), m_FoundSignatures);
    DFX_Bool(pFX, _T("[ProcessedByAutoFetch_FirstPass]"), m_ProcessedByAutoFetch_FirstPass);
    DFX_Bool(pFX, _T("[PrintableCharInAllMainFields]"), m_PrintableCharInAllMainFields);
    DFX_Bool(pFX, _T("[ValidFirstTwoDigitsInDateOpenField]"), m_ValidFirstTwoDigitsInDateOpenField);
    DFX_Bool(pFX, _T("[ClosedAccount]"), m_ClosedAccount);
    //}}AFX_FIELD_MAP
}


The above DoFieldExchange method is mapping out data member field names with the table field names.
If your derived class is mapping out [Company Name] field to a data member field name, it's going to fail if you setup a select statement that changes the field names, or limits the field names to exclude other mapped out field names in your derived DoFieldExchange method.
0
 
rstaveleyAuthor Commented:
> northwinds.cpp(74) : error C2039: 'Edit' : is not a member of '_Recordset'
>        c:\devt\msado15.tlh(2754) : see declaration of '_Recordset'

I see GetEditMode, but I don't see Edit in the generated msado15.tlh file.
0
 
AxterCommented:
Look for anther member method with the word edit in it.

I don't have access to this file from my current location.
0
 
rstaveleyAuthor Commented:
That example binds only for the update. Which seems like only half the reason to bind. If I rebind, no joy. Odd.
0
 
rstaveleyAuthor Commented:
> How can I get fields with spaces to work?

Well this one turned out to be embarrassing enough. The field names don't have spaces. They are just displayed that way. Doh.

You were quite right to ask the questions you did, David.

I ca. write:

  "SELECT CompanyName,ContactName AS Contact FROM Customers"

However, the update is perplexing
0
 
rstaveleyAuthor Commented:
OK, I've got the update working, but I don't like the way that I'm doing it. I am assigning via an expensive looking chain of IDispatches (i.e. pRs->Fields->GetItem("ContactName")->Value = _bstr_t(fred)) rather than using the record which is bound. It looks like the bound record only works for reads, which is a bit naff. I got the impression that you could update via changes to the the CADORecordBinding, but I've yet to see an example of this.

Here's my working code (for the record):
--------8<--------
/* Modified very little from http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscexampleadowithextensions.asp */

// This reads through and updates the Northwind sample database which you can install via Access 2003's help menu.

// Visual C++ Extensions Example
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \
   no_namespace rename("EOF", "EndOfFile")

#include <stdio.h>
#include <icrsint.h>
_COM_SMARTPTR_TYPEDEF(IADORecordBinding, __uuidof(IADORecordBinding));

inline void TESTHR(HRESULT _hr) { if FAILED(_hr) _com_issue_error(_hr); }

class CCustomRs : public CADORecordBinding
{
BEGIN_ADO_BINDING(CCustomRs)
   ADO_VARIABLE_LENGTH_ENTRY2(1, adVarChar, m_ch_company,
                        sizeof(m_ch_company), m_ul_companyStatus, false)
   ADO_VARIABLE_LENGTH_ENTRY2(2, adVarChar, m_ch_contact,
                        sizeof(m_ch_contact), m_ul_contactStatus, false)
END_ADO_BINDING()
public:
   CHAR    m_ch_company[50];
   CHAR    m_ch_contact[50];
   ULONG   m_ul_companyStatus;
   ULONG   m_ul_contactStatus;
};

void main(void)
{
   ::CoInitialize(NULL);
   try
      {
      _RecordsetPtr pRs("ADODB.Recordset");
      CCustomRs rs;
      IADORecordBindingPtr picRs(pRs);
      pRs->Open(
         "SELECT CompanyName,ContactName FROM Customers",
         "Provider=Microsoft.Jet.OLEDB.4.0;"
         "Data Source=\"C:\\Program Files\\Microsoft Office\\OFFICE11\\SAMPLES\\Northwind.mdb\";",
         adOpenStatic, adLockOptimistic, adCmdText);

      TESTHR(picRs->BindToRecordset(&rs));

      while (!pRs->EndOfFile)
         {
      // Process data in the CCustomRs C++ instance variables.
         printf("Name = %s serviced by %s\n",
            (rs.m_ul_companyStatus == adFldOK ? rs.m_ch_company: "<Error>"),
            (rs.m_ul_contactStatus == adFldOK ? rs.m_ch_contact: "<Error>"));

/* --------8<-------- */

// Here's my arbirary update. I toggle between "Laurence Lebihan" and "Fred Bassett".
// I'm using  pRs->Fields->GetItem("ContactName")->Value because I can't see
// how to do this using CCustomRs, which would be preferable. However,
// the chain of IDispatch interfaces can't be very efficient for production code(!)

         if (strcmp(rs.m_ch_contact,"Laurence Lebihan") == 0)
            {
            const char *fred = "Fred Bassett";
            printf("** Changing %s to %s **\n",rs.m_ch_contact,fred);
            //strcpy(rs.m_ch_contact,fred); // This doesn't do it! :-(
            pRs->Fields->GetItem("ContactName")->Value = _bstr_t(fred); // Need to do this :-(
            pRs->Update();
            }
         else if (strcmp(rs.m_ch_contact,"Fred Bassett") == 0)
            {
            const char *fred = "Laurence Lebihan";
            printf("** Changing %s to %s **\n",rs.m_ch_contact,fred);
            //strcpy(rs.m_ch_contact,fred); // This doesn't do it! :-(
            pRs->Fields->GetItem("ContactName")->Value = _bstr_t(fred); // Need to do this :-(
            pRs->Update();
            }
/* --------8<--------- */

      // Move to the next row of the Recordset.
      // Fields in the new row will automatically be
      // placed in the CCustomRs C++ instance variables.

         pRs->MoveNext();
         }
      }
   catch (_com_error &e )
      {
      printf("Error:\n");
      printf("Code = %08lx\n", e.Error());
      printf("Meaning = %s\n", e.ErrorMessage());
      printf("Source = %s\n", (LPCSTR) e.Source());
      printf("Description = %s\n", (LPCSTR) e.Description());
      }
   ::CoUninitialize();
}
--------8<--------
0
 
rstaveleyAuthor Commented:
Looking at the header file makes me think that picRs->Update(&rs) ought to work, but I get an 0x800A0C93 exception (Operation is not allowed in this context).

--------8<--------
         if (strcmp(rs.m_ch_contact,"Laurence Lebihan") == 0)
            {
            const char *fred = "Fred Bassett";
            printf("** Changing %s to %s **\n",rs.m_ch_contact,fred);      
            strcpy(rs.m_ch_contact,fred);   // Make the update to my CADORecordBinding object
            TESTHR(picRs->Update(&rs));  // This Update results in a 0x800A0C93 exception
            }
--------8<---------
0
 
rstaveleyAuthor Commented:
Off topic, but in case anyone else beats the same path updating records using ADO with extensions and finds themselves reading this...

<OFF_TOPIC>

Having identified that I need to use FieldPtr for the update, I was pulling hair out about getting items from collections by index rather than name.

By name works fine:

            pRs->Fields->GetItem("CompanyName")->Value = _bstr_t(fred);
            pRs->Update();

... but it seems a bit ineffcient.

But indexing was perplexing:

            int index = 0;
            pRs->Fields->GetItem(index)->Value = _bstr_t(fred); // Throws exception
            pRs->Update();

...the error message being...

  Code = 800a0cc1
  Meaning = Unknown error 0x800A0CC1
  Source = ADODB.Fields
  Description = Item cannot be found in the collection corresponding to the requested name or ordinal.

What was happening is that the integer was implicitly converted to a _variant_t, but the _variant_t constructor created variant type VT_I4, because we have 32-bit integers. Collection indexing requires VT_I2, and apparently doesn't attempt to convert VT_I4 to VT_!2 itself. The error message was very misleading!

The solution is casting the index to a short:

            int index = 0; // Should have used a 16 bit int
            pRs->Fields->GetItem((short)index)->Value = _bstr_t(fred); // Works fine
            pRs->Update();

...or indeed...

            int index = 0;
            pRs->Fields->Item[(short)index]->Value = _bstr_t(static_cast<short>(fred)); // Works fine
            pRs->Update();

This gets the _variant_t to have vt VT_I2 and COM happiness prevails.

</OFF_TOPIC>
0
 
rstaveleyAuthor Commented:
Thanks for the help David. Alas, I haven't been able to get CADORecordBinding, though I'm convinced that it is supposed to be usable for updates as well as selects and have seen nothing to prove otherwise. I've resported to using the Fields IDispatch for updates. I'm going to grade this with a 'B' for the sake of the PAQ, because it was inconclusive. If I find out how to do this properly, I'll leave a post.

Best wishes

    Rob
0
 
AxterCommented:
Thanks for the points.

If I find any thing new, I'll posted here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.