Click here to Skip to main content
15,867,330 members
Articles / Desktop Programming / MFC
Article

Recordsets and invoices

Rate me:
Please Sign up or sign in to vote.
3.96/5 (9 votes)
23 Sep 2005CPOL10 min read 52.6K   808   21   2
How to use CRecordset with the Northwind database to create an invoice.

Introduction

Other than wanting to create something that was at least partially useful, I wasn't really sure what approach I wanted to take with this article. On the one hand, it started out as a response to a question that I see quite often in the VC++ forum regarding how to use a CRecordset object in an MFC application. On the other hand, it was a good exercise in dealing with the Northwind database.

You can choose to have database support either at the creation of the project or sometime afterwards. If you create an SDI or MDI application and opt for database support, the view is automatically derived from CRecordView. If you create an SDI or MDI application and derive the view from CFormView instead, you can add controls to it just like you would in a dialog-based application, but you'll need to manually add the database-specific code (e.g., creating a record set that connects to a DSN; add methods to the view for enabling and disabling of buttons on the toolbar). This article will focus on the former.

Creating a DSN

For the next two CRecordset-derived classes to work, a DSN must be created for the Northwind database. For this exercise, it does not matter if you create a User DSN or a System DSN. Other than giving the DSN a name (e.g., Northwind) and specifying an absolute path to the .mdb file, nothing special is required. You can optionally open the file as read-only. To create a DSN, open the ODBC Data Source Administrator dialog box via Control Panel. You may have to go through the Administrative Tools to get there.

Image 1

Once there, select either the User DSN or System DSN tab. Click the Add button:

Image 2

Select the Microsoft Access Driver (*.mdb) driver. Click the Finish button:

Image 3

Give the DSN a name. Click the Select button and locate the nwind.mdb file. Click the OK button. Now we can use this DSN later on in the code.

Creating the Project

As was mentioned at the beginning of this article, create an SDI application whose view is derived from CRecordView. Since CRecordView is derived from CFormView, this will also allow us to place UI controls on the form. For this example, I used a combination of static controls, and list controls.

Image 4

The top and bottom list controls are disabled. The middle one is not. This is explained in more detail later on in the article. You'll also notice that the column header has been removed from the bottom list control.

Deriving a CRecordset class for the purpose of an invoice

This recordset has a bit of complexity to it because it uses so many columns from so many different tables. Use ClassWizard to derive a class, CInvoiceSet, from CRecordset:

Image 5

Click the OK button. Select the Northwind datasource:

Image 6

Click the OK button. Select the Customers, Employees, Order Details, Orders, Products, and Shippers tables:

Image 7

Click the OK button. At this point, CInvoiceSet has been created. We'll need to do quite a bit to it in order to make it useful. First, a number of the member variables can be removed, and one will need to be added. Consult the class' declaration to see which ones are used. Second, clean up the class' constructor, and change the member variable m_nFields to 25. Third, change the GetDefaultSQL() method to return the following SQL query:

SQL
SELECT DISTINCTROW Orders.ShipName, Orders.ShipAddress,
    Orders.ShipCity, Orders.ShipRegion,
    Orders.ShipPostalCode, Orders.ShipCountry, 
    Orders.CustomerID, Customers.CompanyName, 
    Customers.Address, Customers.City, Customers.Region,
    Customers.PostalCode, Customers.Country,
    Employees.LastName, Orders.OrderID, 
    Orders.OrderDate, Orders.RequiredDate, 
    Orders.ShippedDate, Shippers.CompanyName, 
    [Order Details].ProductID, Products.ProductName,
    [Order Details].UnitPrice, [Order Details].Quantity, 
    [Order Details].Discount, Orders.Freight
FROM Shippers <a href=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off200
0/html/defInnerJoin.asp>INNER JOIN</a> (Products INNER JOIN (
    (Employees INNER JOIN (
        Customers INNER JOIN Orders
        ON Customers.CustomerID = 
    Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID)
    INNER JOIN [Order Details]
    ON Orders.OrderID = [Order Details].OrderID)
    ON Products.ProductID = [Order Details].ProductID)
    ON Shippers.ShipperID = Orders.ShipVia

This creates a relationship that looks like:

Image 8

For each row in the Orders table, we get one row each in the Customers, Shippers, and Employees tables. We get multiple rows in the Order Details table, because one order can contain one or more items (e.g., order 10249 contains a quantity of apples and tofu).

It's also important to note that the ordering of the columns in the DoFieldExchange() method is critical. It must match the order of the columns in the table. Failure to do so results in some fun rabbit-chasing!

Since the OrderDate, RequiredDate, and ShippedDate columns in the Orders table all have dates in the 1970-2038 range, the use of CTime is allowable here. Otherwise, we'd have to use TIMESTAMP_STRUCT directly.

Deriving a CRecordset class for the Orders table

This class was actually created for us by AppWizard. We need to do four things to it before we use it. First, remove all but one of the member variables, m_lOrderID. Second, in the class' constructor, change the member variable m_nFields to 1. Also in the constructor, assign the name of one of the columns in the table to the m_strSort member variable, most likely OrderID. Lastly, remove all RFX_xxx() calls in DoFieldExchange() except the one referencing m_lOrderID. It should now look like:

COrderSet::COrderSet(CDatabase* pdb) : CRecordset(pdb)
{
    //{{AFX_FIELD_INIT(COrderSet)
    m_lOrderID = 0;
    m_nFields = 1;
    //}}AFX_FIELD_INIT
    m_nDefaultType = snapshot;
    m_strSort      = _T("OrderID");
}

void COrderSet::DoFieldExchange(CFieldExchange* pFX)
{
    //{{AFX_FIELD_MAP(COrderSet)
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long(pFX, _T("[OrderID]"), m_lOrderID);
    //}}AFX_FIELD_MAP
}

In the GetDefaultSQL() method, the SQL statement is [Orders] which the framework will eventually expand to SELECT * FROM [Orders] ORDER BY [OrderID]. This is fine for our example. For larger tables and datasets, replacing the * with only those columns of interest will make the query a bit more efficient. There is no filter for this recordset.

Next we need to tie the COrderSet and CInvoiceSet together. This requires parameterizing CInvoiceSet. This is all fully explained in an MSDN article titled Recordset: Parameterizing a Recordset (ODBC). First, remove all but the referenced member variables from the DoFieldExchange() method, and add an RFX_xxx() call for each parameter data member, one in this case. Precede the RFX_xxx() call(s) with a single call to pFX->SetFieldType(CFieldExchange::param). Second, change the m_nParams member variable to 1. Lastly, set the m_strFilter member variable to "Orders.OrderID = ?". Now each time that currency changes in COrderSet, we must update the parameter data member.

CInvoiceSet::CInvoiceSet(CDatabase* pdb) : CRecordset(pdb)
{
    //{{AFX_FIELD_INIT(CInvoiceSet)
    ...
    m_nFields = 25;
    m_nParams = 1;
    m_lOrderIDParam = 0;
    //}}AFX_FIELD_INIT
    m_nDefaultType = snapshot;
    m_strFilter = _T("Orders.OrderID = ?");
}

void CInvoiceSet::DoFieldExchange(CFieldExchange* pFX)
{
    //{{AFX_FIELD_MAP(CInvoiceSet)
    ...
    pFX->SetFieldType(CFieldExchange::param);
    RFX_Long(pFX, _T("[OrderID]"), m_lOrderIDParam);
    //}}AFX_FIELD_MAP
}

At this point, our recordsets are ready for use.

DSN-less connections

If for some reason you are not able to create a DSN on the target machine, you can always create a connection to the database that does not require a DSN. This type of connection is called DSN-less. To employ this type of connection, simply modify the record set's GetDefaultConnect() method to return something like:

return _T("ODBC;Driver={Microsoft Access Driver (*.mdb)};
                 DBQ=C:\\...\\dataaccess\\ADO\\nwind.mdb;");

The only drawback to this is that if the database ever moved, you would need to change your code and recompile, or use some sort of configuration file. On the other hand, if creating a DSN on the target machine is strictly off limits, or you have a large number of machines to configure, this may be your only option.

Updating the view to display the records

In the view's OnInitialUpdate() method, the COrderSet recordset is opened, the columns are inserted, and the cursor is set to the first record.

// the record set is actually opened by 
// calling CRecordView::OnInitialUpdate()
m_pOrderSet = &GetDocument()->m_setOrder;
ASSERT(NULL != m_pOrderSet);
...
OnMove(ID_RECORD_FIRST);

Each time one of the navigational buttons is clicked, or when the view is first displayed, the virtual method OnMove() is called which in turn calls the appropriate method in the COrderSet recordset. The CInvoiceSet recordset is then re-queried, or opened if this is the first time, using the new order number, and the UI controls are updated with the new information.

m_InvoiceSet.m_lOrderIDParam = m_pOrderSet->m_lOrderID;
    
TRY
{
    if (! m_InvoiceSet.IsOpen())
        m_InvoiceSet.Open();
    else
        m_InvoiceSet.Requery();

    UpdateAddress();
    UpdateHeader();
        
    double dSubtotal = 0.0;
    // add up the totals to display in the footer
    UpdateDetails(dSubtotal); 
    UpdateFooter(dSubtotal);
}
CATCH(CDBException, e)
{
    AfxMessageBox(e->m_strError);
}
END_CATCH

The date and currency values that are displayed use GetDateFormat() and GetCurrencyFormat() respectively. This is so those values look correct no matter what locale is being used.

Enabling/disabling the navigational buttons

One thing you may have noticed from running the sample application is that the "Next" and "Last" buttons do not appear to enable and disable correctly when the last row is being displayed. They may or may not, depending on your point of view. The reason for this is because the record set's underlying cursor does not yet know what the last row in the set looks like; it simply knows that the highest-numbered record yet seen is simply the "high water mark". The only way around this is to click the "Next" button repeatedly until the last row is reached. At this point, both the "Next" and "Last" buttons will disable correctly.

The ON_UPDATE_COMMAND_UI() handlers are virtual methods provided by CRecordView. Had we derived from CFormView instead, we would need to provide a handler for each of the navigational buttons.

Extras

These next few sections had nothing to do with the article itself. I just added them to the project to make it a bit more polished.

Restricting column resizing

For two of the list controls, I made them read-only so column resizing was not an issue. For the other list control, it could have several items in it which would require the use of the scroll bar. The downside to not being read-only is that the columns could be resized. To keep this from happening, derive a class from CHeaderCtrl. Override the OnChildNotify() method and look for the HDN_BEGINTRACKA or HDN_BEGINTRACKW notifications. If either is encountered, simply return TRUE. This indicates to the framework that the child is responsible for handling the message. Otherwise, call the base class implementation. This looks like:

BOOL CHeaderCtrlEx::OnChildNotify(UINT message, 
         WPARAM wParam, LPARAM lParam, LRESULT *pResult) 
{
    NMHDR nmh = *(LPNMHDR) lParam;

    if (HDN_BEGINTRACKA == nmh.code || 
               HDN_BEGINTRACKW == nmh.code)
    {
        *pResult = TRUE;
        return TRUE;
    }

    return CHeaderCtrl::OnChildNotify(message, 
                        wParam, lParam, pResult);
}

Next add a member variable of this type, call it m_ctrlHeader, to the view. In the view's OnInitialUpdate() method, make use of this new class like:

m_ctrlHeader.SubclassDlgItem(0, &m_lcDetails);

The header control of a list control always has an ID of 0. At this point, one list control has columns that cannot be resized. However, when the mouse hovers over the header's dividers, the cursor indicates that the column can be resized. To address this, override the OnSetCursor() method and simply return TRUE. This indicates to the framework that it should do no further processing on the cursor.

Columns with non-left justification

Column 0 in each of the three list controls uses a style other than LVCFMT_LEFT. However, because of some "feature" in the common ListView control (SysListView32), if the leftmost control is center or right-justified, this will result in a text that is actually left justified. To remedy this, insert a temporary column at index 0, insert the remainder of the columns, and then delete the temporary column. This looks like:

m_lcDetails.InsertColumn(0, _T("David")); // temporary
m_lcDetails.InsertColumn(1, _T("Product ID"), 
                                 LVCFMT_RIGHT, 80);
m_lcDetails.InsertColumn(2, _T("Product name"), 
                                 LVCFMT_LEFT, 275);
m_lcDetails.InsertColumn(3, _T("Quantity"),     
                                LVCFMT_RIGHT, 75);
m_lcDetails.InsertColumn(4, _T("Unit price"),   
                                LVCFMT_RIGHT, 75);
m_lcDetails.InsertColumn(5, _T("Discount"),     
                                LVCFMT_RIGHT, 75);
m_lcDetails.InsertColumn(6, _T("Total"),        
                                LVCFMT_RIGHT, 75);
m_lcDetails.DeleteColumn(0);

Bold static controls

I thought it would be visually appealing to make two of the static controls have a bold font. The first thing to do is derive a class from CStatic, call it CStaticEx. Add a CFont member variable to this class. In the class' constructor, create the desired font like:

CStaticEx::CStaticEx()
{
    LOGFONT lf = {0};

    _tcscpy(lf.lfFaceName, _T("MS Sans Serif"));
    lf.lfHeight = 8;
    lf.lfWeight = FW_BOLD;

    m_font.CreateFontIndirect(&lf);
}

Now, we need to override the OnChildNotify() method and look for the WM_CTLCOLORSTATIC message. If this message is encountered, select the font into the DC. Otherwise, call the base class implementation. This looks like:

BOOL CStaticEx::OnChildNotify(UINT message, 
      WPARAM wParam, LPARAM lParam, LRESULT* pLResult) 
{
    if (WM_CTLCOLORSTATIC == message)
    {
        ::SelectObject((HDC) wParam, m_font);
        return TRUE;
    }

    return CStatic::OnChildNotify(message, 
                      wParam, lParam, pLResult);
}

To make use of this class, give the static controls an ID other than IDC_STATIC. Then create a CStaticEx member variable for each of them. That's it!

Restricting frame resizing

This is a simple matter of removing the WS_MAXIMIZEBOX and WS_THICKFRAME styles from the frame. The WS_MAXIMIZEBOX style allows you to double-click the frame's caption to maximize it. The WS_THICKFRAME style allows you to size the frame by grabbing its border. This looks like:

BOOL CMainFrame::PreCreateWindow(CREATESTRUCT& cs)
{
    if (! CFrameWnd::PreCreateWindow(cs))
        return FALSE;
    
    cs.style &= 
      ~(FWS_ADDTOTITLE | WS_MAXIMIZEBOX | WS_THICKFRAME);

    return TRUE;
}

Notice the use of the one’s complement and bitwise-AND operators. You might be tempted to remove these styles by simply subtracting them. This will work as long as those styles don't already exist. However, your code will be much more "portable" if you make no assumptions about the frame's current styling.

Summary

This was really just a high-level overview on what is required to use a recordset, create a useful, multi-table query, and update an SDI with the results. If you want to do the same thing with a dialog-based application instead, most of what was shown here still applies. One thing that you would need to do is open the "main" recordset, COrderSet, yourself since there would be no view to do it for you. You would also need to provide a handler for each of the navigational buttons. This is akin to the view's OnMove() method.

Enjoy!

License

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


Written By
Software Developer (Senior) Pinnacle Business Systems
United States United States

The page you are looking for might have been removed, had its name changed, or is temporarily unavailable.

HTTP 404 - File not found
Internet Information Services

Comments and Discussions

 
GeneralNice! Pin
ThatsAlok17-Oct-05 18:48
ThatsAlok17-Oct-05 18:48 
GeneralNice - well written and presented Pin
Garth J Lancaster23-Sep-05 13:51
professionalGarth J Lancaster23-Sep-05 13:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.