ado.html

ActiveX Data Objects Programming in C++ Description: ActiveX Data Objects Programming How To: Hey,

<p>For navigating through the record set you can use the following member 
functions of the Recordset object:</p>
<pre>HRESULT MoveFirst();</pre>
<pre>HRESULT MovePrevious();</pre>
<pre>HRESULT MoveNext();</pre>
<pre>HRESULT MoveLast();</pre>
<p>Their names speak for themselves and I won't explain their purposes anymore.&nbsp; 
In addition to these functions, the BOFILE and EOFILE variables (remember that 
we've renamed these variables when we imported the msado15.dll), provide 
information where we are in the record set.&nbsp; BOFILE is true when we are 
past the first record while EOFILE is true when we are past the last record.&nbsp; 
A typical application would be:</p>
<pre>while (!pRset-&gt;EOFILE)
{
    // Display the contents of the record set.
    // . . .

    pRset-&gt;MoveNext();
}</pre>
<h3>Accessing and Updating Field Data in the Recordset</h3>
<h4>Accessing Field Values</h4>
<p>Because ADO is intended to be used in other languages other than C++, there 
is no straightforward way to access data from a record set, since the values are 
of a variant type.&nbsp; And because these values are variant, we need to 
convert them to their proper type to be able to make sense out of them.&nbsp; 
For example:</p>
<pre>_variant_t vtItem;
CString strItem;
vtItem = pRs-&gt;GetCollect(_variant_t(&quot;item&quot;));
vtItem.ChangeType(VT_BSTR);
strItem = vtItem.bstrVal;</pre>
<p>To make life easier I created a function that should bring some 
uniformity to the way we access data:</p>
<pre>_variant_t FieldValue(const _RecordsetPtr pRS, const char *field)
{
    return pRS-&gt;GetCollect(_variant_t(field));
}</pre>
<p>Applying this to the example above:</p>
<pre>CString strItem = (const char *)_bstr_t(FieldValue(pRset, &quot;item&quot;));

double basePrice = double(FieldValue(pRS, &quot;baseprice&quot;));    // &lt;-- Another example</pre>
<h4>Updating Field Values</h4>
<p>Updating field values entails the same tedious process.&nbsp; For example:</p>
<pre>_variant_t vtItem, vtValue;
vtItem.SetString(&quot;item&quot;);
vtValue.SetString(&quot;Power drill&quot;);
pRset-&gt;Update(vtItem, vtValue);</pre>
<p>Again, to make life easier, I created a function or, rather, a series of 
overloaded functions in the spirit of the one I wrote to access data, to bring 
uniformity in updating data.&nbsp; The third parameter should provide you a clue 
on what each function does:</p>
<pre>void FieldValue(_RecordsetPtr pRS, const char *field, const short value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value, VT_I2));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const long value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value, VT_I4));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const float value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const double value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value, VT_R8));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const char *value)   // strings of type char*
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const wchar_t *value)    // wide strings of type wchar_t*
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, _bstr_t &amp;value)    // strings of type _bstr_t
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const unsigned char value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const bool value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const CY &amp;value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const DECIMAL &amp;value)
{
    pRS-&gt;Update(_variant_t(field), _variant_t(value));
}</pre>
<p>Applying to our example above:</p>
<pre>FieldValue(pRset, &quot;item&quot;, &quot;Power drill&quot;);
FieldValue(pRset, &quot;baseprice&quot;, 235.00);    // &lt;-- Another example</pre>
<h3>Adding and Deleting Records</h3>
<p>To add a new record to the record set call the Recordset object's AddNew() 
function.&nbsp; Doing so adds a new, albeit, blank record to the record set 
while making that record the current one.&nbsp; After the record is added you 
can begin to update each field value using the PutCollect().&nbsp; For example:</p>
<pre>pRset-&gt;AddNew();
pRset-&gt;PutCollect(_variant_t(&quot;item&quot;), _variant_t(&quot;Lasgun&quot;));
pRset-&gt;PutCollect(_variant_t(&quot;specification&quot;), _variant_t(&quot;High-powered, medium range&quot;));
pRset-&gt;PutCollect(_variant_t(&quot;baseprice&quot;), _variant_t(3456.50));
pRset-&gt;PutCollect(_variant_t(&quot;actualprice&quot;), _variant_t(3400.00));
pRset-&gt;PutCollect(_variant_t(&quot;supplier&quot;), _variant_t(1));</pre>
<p>To delete a record, call the Delete() member function of the Recordset 
object, which has the syntax:</p>
<pre>HRESULT Delete(enum AffectEnum AffectRecords);</pre>
<p>Where AffectEnum has the following values:</p>
<pre>enum AffectEnum
{
    adAffectCurrent = 1,
    adAffectGroup = 2,
    adAffectAll = 3,
    adAffectAllChapters = 4
};</pre>
<p>For example, to delete the current record:</p>
<pre>pRset-&gt;Delete(adAffectCurrent);
pRset-&gt;MovePrevious();
if (pRset-&gt;BOFILE)
    pRset-&gt;MoveNext();</pre>
<h3>Finding a Specific Record</h3>
<p>To find the specific record in the record set, use the member function 
Find(), which has the syntax:</p>
<pre>HRESULT Find(_bstr_t Criteria, long SkipRecords, enum <a href="#SearchDirectionEnum">SearchDirectionEnum</a> SearchDirection, const _variant_t &amp;Start = vtMissing);</pre>
<p>Where SearchDirectionEnum has the values:</p>
<pre><a name="SearchDirectionEnum"></a>enum SearchDirectionEnum
{
    adSearchForward,
    adSearchBackward
};</pre>
<p>For example, to find the item that has the id of 23 after and including the 
current record:</p>
<pre>pRet-&gt;Find(&quot;itemdid = 23&quot;, adSearchForward);</pre>
<h3>Error Handling</h3>
<p>Most of the member functions of the ADO objects return an HRESULT value that 
will give us a clue of the how an operation fares.&nbsp; However, it would be 
far elegant and better to use the C++ <i>try</i> and <i>catch</i> mechanism for 
handling ADO errors.&nbsp; ADO objects throw an exception of type _com_error 
whenever an error is encountered.&nbsp; The Description() member of _com_error 
returns a meaningful description of the error encountered.&nbsp; For example:</p>
<pre>try
{
    // Delicate ADO operations normally go here
    // . . . 
}

catch (_com_error &amp;e)    // Catch any error...
{
    cerr &lt;&lt; endl &lt;&lt; (const char *)e.Description() &lt;&lt; endl;  // ...then show its description.
}</pre>
<h2><a name="Sample Code"></a>Sample Code</h2>
<p>This tutorial comes with supplementary sample programs that will further 
demonstrate the concepts elaborated above.</p>
<ul>
  <li><a href="sample/ADOTemplate.zip">ADOTemplate</a> - A console applet that 
  demonstrates opening a connection to the database, executing an SQL command 
  and navigating through a record set.&nbsp; [<a target = "_blank" href="images/ADOTemplate.jpg">screenshot</a>]<br>
&nbsp;</li>
  <li><a href="sample/ADOMFC.zip">ADOMFC</a> - A program demonstrating how  the concepts in the tutorial could 
  be applied in creating a simple but concrete database application in MFC.&nbsp; 
  [<a target = "_blank" href="images/ADOMFC.jpg">screenshot</a>]<br>
&nbsp;</li>
  <li><a href="mysql.html">MySQL commands</a> - SQL statements used in creating the pcs database used in 
  the examples</li>
</ul>
<h2>Future Installments</h2>
<p>Watch out for Part II of this tutorial which would include a detailed 
walkthrough of the creation of the MFC example program above and another program 
with a different 
approach to accessing and updating field values using ADO record binding.&nbsp; 
'Till then, ciao and happy hacking!!!</p>
<p>&nbsp;</p>
<p>12302003<br>
Mark Jundo P. Documento<br>
<a href="mailto:[e-mail]">[e-mail]</a></p>
<p>&nbsp;</p>
<p class="cnotice">Copyright 2003-2004 Mark Jundo P. Documento</p>

</body>

</html>