[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO with extensions Update with Microsoft Access (Jet 4)

Posted on 2005-04-11
14
Medium Priority
?
415 Views
Last Modified: 2008-01-09
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<--------
0
Comment
Question by:rstaveley
  • 7
  • 7
14 Comments
 
LVL 30

Expert Comment

by:Axter
ID: 13754554
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
 
LVL 30

Expert Comment

by:Axter
ID: 13754580
>>// 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
 
LVL 30

Expert Comment

by:Axter
ID: 13754622
>>"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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Expert Comment

by:Axter
ID: 13754650
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
 
LVL 17

Author Comment

by:rstaveley
ID: 13755438
> 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
 
LVL 30

Expert Comment

by:Axter
ID: 13755507
Look for anther member method with the word edit in it.

I don't have access to this file from my current location.
0
 
LVL 30

Accepted Solution

by:
Axter earned 750 total points
ID: 13755581
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
 
LVL 17

Author Comment

by:rstaveley
ID: 13755735
That example binds only for the update. Which seems like only half the reason to bind. If I rebind, no joy. Odd.
0
 
LVL 17

Author Comment

by:rstaveley
ID: 13757319
> 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
 
LVL 17

Author Comment

by:rstaveley
ID: 13757505
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
 
LVL 17

Author Comment

by:rstaveley
ID: 13757624
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
 
LVL 17

Author Comment

by:rstaveley
ID: 13759926
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
 
LVL 17

Author Comment

by:rstaveley
ID: 13804604
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
 
LVL 30

Expert Comment

by:Axter
ID: 13804633
Thanks for the points.

If I find any thing new, I'll posted here.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question