[XenoCafe Logo] Click for Homepage
Home Tutorials Forum Blog Advertising Links Contact About

 



Human Resources Database - MFC, ADO, SQL, and MS Access

Written by Tony Bhimani
September 4, 2005

Requirements
Microsoft Visual C++ 6
Microsoft Windows 98/ME/NT4/2000/XP
Microsoft Data Access Components (MDAC) 2.8

Download the source code: HRDB.zip

This is the first of many code examples I'll be publishing. I've been digging through my hard drive backing stuff up and will be offering some old code I've written over the years. The Human Resources Database is a project I wrote for my Object Oriented Programming II class at DeVry in 2003. It was written using MFC with Visual C++ 6. Instead of using the typical MFC & DAO method for accessing a database, I decided to use ADO. The backend is an Access database since I wanted to keep things simple. There's nothing fancy about this application except using ADO in a non object oriented way. HRDB is an MDI application that consists of two main screens: the search window and employee view. The search window accepts several fields to locate employees. Once the search is executed, any employee records found will be displayed in a list view control. Double-click a record and the second view (the employee view) will open. From that screen an employee can be updated or deleted. New employees can be created by using the menu or toolbar icon. There is also a Tip of the Day dialog, docking toolbar, and window manipulation like horizontal and vertical tiling, close all, and switching between windows using the toolbar.

The Search Window accepts several parameters to locate employees. Drop-down lists are used to specify the type of search performed on each field, such as, the input being equal, not equal, and like for example. The input is taken and a SQL search query is built and used by the ADO connection to find the records that match. Any empty fields are ignored and excluded from the query. If all fields are empty, then all records in the database are returned. The returned records are loaded into the list view control which can be double-clicked on to open the employee's record in the Employee View.

Below is the code showing how the query is built and is then sent to the ADO connection to populate the list view.

void CSearchView::OnSearch() 
{
  CString query, condition, likeBuf;
  CString sql = "SELECT * FROM Employees WHERE ";

  UpdateData(TRUE);
  
  // building the SQL string is a pain in the ass as you will see

  // process Employee ID
  if (m_strID.GetLength())
  {
    m_cboID.GetLBText(m_cboID.GetCurSel(), condition);
    if (condition == "LIKE") likeBuf = _T("%"); else likeBuf = _T("");
    query.Format("id %s '%s%s%s' AND ", condition, likeBuf, m_strID, likeBuf);
    sql+=query;
  }
  // process First Name
  if (m_strFName.GetLength())
  {
    m_cboFName.GetLBText(m_cboFName.GetCurSel(), condition);
    if (condition == "LIKE") likeBuf = _T("%"); else likeBuf = _T("");
    query.Format("fname %s '%s%s%s' AND ", condition, likeBuf, m_strFName, likeBuf);
    sql+=query;
  }

  ...

  // process Supervisor
  if (m_strSupervisor.GetLength())
  {
    m_cboSupervisor.GetLBText(m_cboSupervisor.GetCurSel(), condition);
    if (condition == "LIKE") likeBuf = _T("%"); else likeBuf = _T("");
    query.Format("supervisor %s '%s%s%s' AND ", condition, likeBuf, m_strSupervisor, likeBuf);
    sql+=query;
  }
  // chop off the last 5 characters " AND "
  sql = sql.Left(sql.GetLength() - 5);

  // execute the search
  DoSQLSearch(sql);
}

int CSearchView::DoSQLSearch(const CString &sql)
{
  int index = 0;
  HRESULT hr;
  variant_t vNull;
  variant_t TheValue;
  vNull.vt = VT_ERROR;
  vNull.scode = DISP_E_PARAMNOTFOUND;
  CString id, fname, lname, hphone, position, dept, wphone, ext;

  try
  {
    hr = theApp.m_pRecordset.CreateInstance(_uuidof(Recordset));
    if (SUCCEEDED(hr))
    {
      theApp.m_pRecordset->PutRefActiveConnection(theApp.m_pConnection);
      hr = theApp.m_pRecordset->Open(_variant_t(sql), vNull, adOpenForwardOnly, adLockOptimistic, adCmdText);
      if (SUCCEEDED(hr)) 
      {
        m_lvResults.DeleteAllItems();
        while (!theApp.m_pRecordset->GetEndOfFile())
		{
          TheValue = theApp.m_pRecordset->GetCollect("id");
          if (TheValue.vt != VT_NULL)
            id = (char*)_bstr_t(TheValue);

          TheValue = theApp.m_pRecordset->GetCollect("fname");
          if (TheValue.vt != VT_NULL)
            fname = (char*)_bstr_t(TheValue);

          ...

          TheValue = theApp.m_pRecordset->GetCollect("ext");
          if (TheValue.vt != VT_NULL)
            ext = (char*)_bstr_t(TheValue);

          m_lvResults.InsertRow(index, 8, id, fname, lname, hphone, position, dept, wphone, ext);
          theApp.m_pRecordset->MoveNext();
          index++;
        }
      }
      theApp.m_pRecordset->Close();
    }
  }
  catch( _com_error &e )
  {
    TRACE( "Error:%08lx.\n", e.Error());
    TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
    TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
    TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
  }
  catch(...)
  {
    TRACE( "\n*** Unhandled Exception ***\n" );
  }
  return index;
}

Employee records are opened from the list view where you can update the employee or delete them from the database. There isn't any error checking to make sure a valid email address or salary is entered, but it wouldn't be difficult to add.

This code is for updating and deleting the employee. The button events that use these functions are shown later.

BOOL CEmployeeView::UpdateEmployee()
{
  HRESULT hr;
  CString sql;
  variant_t vNull;
  vNull.vt = VT_ERROR;
  vNull.scode = DISP_E_PARAMNOTFOUND;

  UpdateData(TRUE);

  sql.Format("SELECT * FROM Employees WHERE id='%s'", m_strID);

  try
  {
    hr = theApp.m_pRecordset.CreateInstance(_uuidof(Recordset));
    if (SUCCEEDED(hr))
    {
      theApp.m_pRecordset->PutRefActiveConnection(theApp.m_pConnection);
      hr = theApp.m_pRecordset->Open(_variant_t(sql), vNull, adOpenForwardOnly, adLockOptimistic, adCmdText);
      if (!theApp.m_pRecordset->GetEndOfFile())
      {
        theApp.m_pRecordset->PutCollect("fname", _variant_t(m_strFName));
        theApp.m_pRecordset->PutCollect("lname", _variant_t(m_strLName));
        theApp.m_pRecordset->PutCollect("address", _variant_t(m_strAddress));
        theApp.m_pRecordset->PutCollect("city", _variant_t(m_strCity));
        theApp.m_pRecordset->PutCollect("state", _variant_t(m_strState));
        theApp.m_pRecordset->PutCollect("zip", _variant_t(m_strZip));
        theApp.m_pRecordset->PutCollect("home_phone", _variant_t(m_strHPhone));
        theApp.m_pRecordset->PutCollect("home_email", _variant_t(m_strHEmail));
        theApp.m_pRecordset->PutCollect("position", _variant_t(m_strPosition));
        theApp.m_pRecordset->PutCollect("dept", _variant_t(m_strDept));
        theApp.m_pRecordset->PutCollect("salary", _variant_t(m_strSalary));
        theApp.m_pRecordset->PutCollect("work_phone", _variant_t(m_strWPhone));
        theApp.m_pRecordset->PutCollect("ext", _variant_t(m_strExt));
        theApp.m_pRecordset->PutCollect("work_email", _variant_t(m_strWEmail));
        theApp.m_pRecordset->PutCollect("supervisor", _variant_t(m_strSupervisor));
        theApp.m_pRecordset->Update(vNull, vNull);
        theApp.m_pRecordset->Close();
        return TRUE;
      }
    }
  }
  catch( _com_error &e )
  {
    TRACE( "Error:%08lx.\n", e.Error());
    TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
    TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
    TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
  }
  catch(...)
  {
    TRACE( "\n*** Unhandled Exception ***\n" );
  }
  return FALSE;
}

BOOL CEmployeeView::DeleteEmployee()
{
  HRESULT hr;
  CString sql;
  variant_t vNull;
  vNull.vt = VT_ERROR;
  vNull.scode = DISP_E_PARAMNOTFOUND;

  //UpdateData(TRUE);

  sql.Format("SELECT * FROM Employees WHERE id='%s'", m_strID);

  try
  {
    hr = theApp.m_pRecordset.CreateInstance(_uuidof(Recordset));
    if (SUCCEEDED(hr))
    {
      theApp.m_pRecordset->PutRefActiveConnection(theApp.m_pConnection);
      hr = theApp.m_pRecordset->Open(_variant_t(sql), vNull, adOpenForwardOnly, adLockOptimistic, adCmdText);
      if (!theApp.m_pRecordset->GetEndOfFile())
      {
        theApp.m_pRecordset->Delete(adAffectCurrent);
        theApp.m_pRecordset->Close();
        return TRUE;
      }
    }
  }
  catch( _com_error &e )
  {
    TRACE( "Error:%08lx.\n", e.Error());
    TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
    TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
    TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
  }
  catch(...)
  {
    TRACE( "\n*** Unhandled Exception ***\n" );
  }
  return FALSE;
}

Adding a new employee is done through the same form that displays an existing employee, so it has two modes. An empty version of the form is opened from the toolbar or file menu. The employee id, first name, and last name are required fields. The employee can't be saved to the database unless those three fields are filled in. If an id is entered that already exists, an error will be displayed to choose a different id.

BOOL CEmployeeView::IDExists(const CString &strID)
{
  HRESULT hr;
  CString sql;
  _variant_t vNull;
  vNull.vt = VT_ERROR;
  vNull.scode = DISP_E_PARAMNOTFOUND;

  sql.Format("SELECT id FROM Employees WHERE id='%s'", strID);

  try 
  {
    hr = theApp.m_pRecordset.CreateInstance(_uuidof(Recordset));
    if (SUCCEEDED(hr))
    {
      theApp.m_pRecordset->PutRefActiveConnection(theApp.m_pConnection);
      hr = theApp.m_pRecordset->Open(_variant_t(sql), vNull, adOpenForwardOnly, adLockOptimistic, adCmdText);
      if (SUCCEEDED(hr))
      {
        BOOL bEof = theApp.m_pRecordset->GetEndOfFile();
        theApp.m_pRecordset->Close();
        return !bEof;
      }
    }
  }
  catch (_com_error &e)
  {
    TRACE( "Error:%08lx.\n", e.Error());
    TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
    TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
    TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
  }
  catch(...)
  {
    TRACE( "\n*** Unhandled Exception ***\n" );
  }
  return TRUE;
}

BOOL CEmployeeView::AddEmployee()
{
  HRESULT hr;
  CString sql = "SELECT * FROM Employees WHERE id IS NULL";
  _variant_t vNull;
  vNull.vt = VT_ERROR;
  vNull.scode = DISP_E_PARAMNOTFOUND;

  UpdateData(TRUE);

  try 
  {
    hr = theApp.m_pRecordset.CreateInstance(_uuidof(Recordset));
    if (SUCCEEDED(hr)) 
    {
      if (IDExists(m_strID) == FALSE) 
      {
        theApp.m_pRecordset->PutRefActiveConnection(theApp.m_pConnection);
        hr = theApp.m_pRecordset->Open(_variant_t(sql), vNull, adOpenForwardOnly, adLockOptimistic, adCmdText);
        if (SUCCEEDED(hr))
        {
          COleSafeArray vaFieldlist;
          vaFieldlist.CreateOneDim(VT_VARIANT,16);
          // Fill in the field names now.
          long lArrayIndex[1];
          lArrayIndex[0] = 0;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("id")));
          lArrayIndex[0] = 1;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("fname")));
          lArrayIndex[0] = 2;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("lname")));
          lArrayIndex[0] = 3;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("address")));
          lArrayIndex[0] = 4;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("city")));
          lArrayIndex[0] = 5;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("state")));
          lArrayIndex[0] = 6;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("zip")));
          lArrayIndex[0] = 7;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("home_phone")));
          lArrayIndex[0] = 8;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("home_email")));
          lArrayIndex[0] = 9;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("position")));
          lArrayIndex[0] = 10;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("dept")));
          lArrayIndex[0] = 11;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("salary")));
          lArrayIndex[0] = 12;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("work_phone")));
          lArrayIndex[0] = 13;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("ext")));
          lArrayIndex[0] = 14;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("work_email")));
          lArrayIndex[0] = 15;
          vaFieldlist.PutElement(lArrayIndex, &(_variant_t("supervisor")));

          // Create an array for the list of values to go in the Employees table.
          COleSafeArray vaValuelist;
          vaValuelist.CreateOneDim(VT_VARIANT,16);
          // Fill in the values for each field.
          lArrayIndex[0] = 0;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strID)));
          lArrayIndex[0] = 1;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strFName)));
          lArrayIndex[0] = 2;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strLName)));
          lArrayIndex[0] = 3;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strAddress)));
          lArrayIndex[0] = 4;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strCity)));
          lArrayIndex[0] = 5;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strState)));
          lArrayIndex[0] = 6;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strZip)));
          lArrayIndex[0] = 7;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strHPhone)));
          lArrayIndex[0] = 8;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strHEmail)));
          lArrayIndex[0] = 9;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strPosition)));
          lArrayIndex[0] = 10;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strDept)));
          lArrayIndex[0] = 11;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strSalary)));
          lArrayIndex[0] = 12;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strWPhone)));
          lArrayIndex[0] = 13;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strExt)));
          lArrayIndex[0] = 14;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strWEmail)));
          lArrayIndex[0] = 15;
          vaValuelist.PutElement(lArrayIndex, &(_variant_t(m_strSupervisor)));

          theApp.m_pRecordset->AddNew(vaFieldlist, vaValuelist);
          theApp.m_pRecordset->Close();
          return TRUE;
        }
      } 
      else 
        return FALSE;
    }
  }
  catch (_com_error &e)
  {
    TRACE( "Error:%08lx.\n", e.Error());
    TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
    TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
    TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
  }
  catch(...)
  {
    TRACE( "\n*** Unhandled Exception ***\n" );
  }
  return FALSE;
}

Here is the code for the Save and Delete buttons being clicked.

void CEmployeeView::OnFileSaveEmp() 
{
  CString strTitle = GetDocument()->GetTitle();
  if (strTitle.Find("New Employee", 0) != -1)
  {
    if (AddEmployee())
    {
      CString strTitle;
      strTitle.Format("Employee ID: %s", m_strID);
      GetDocument()->SetTitle(strTitle);
      GetParentFrame()->SetIcon(AfxGetApp()->LoadIcon(IDI_EDITEMP),FALSE);
      m_txtID.SetReadOnly(TRUE);
    }
    else
      MessageBox("ERROR: The specified ID already exists. Please choose another.",
                 "Error Adding Employee", MB_ICONEXCLAMATION | MB_OK);
  }
  else
  {
    if (UpdateEmployee() == FALSE)
    {
      MessageBox("ERROR: An error occured while attempting to update the employee record.",
                 "Error Updating Employee", MB_ICONEXCLAMATION | MB_OK);
    }
  }
}

void CEmployeeView::OnFileDeleteEmp() 
{
  if (MessageBox("Are you sure you want to delete the employee?", 
                 "Delete Employee", MB_ICONQUESTION | MB_YESNO) == IDYES)
  {
    if (DeleteEmployee())
    {
      CMDIFrameWnd *pMainFrame = (CMDIFrameWnd*)AfxGetApp()->m_pMainWnd;
      HWND hWnd = pMainFrame->MDIGetActive()->GetSafeHwnd();
      ::SendMessage(hWnd, WM_CLOSE, (WPARAM)0, (LPARAM)0);
    }
    else
    {
      MessageBox("ERROR: An error occured while attempting to delete the employee record.",
                 "Error Deleting Employee", MB_ICONEXCLAMATION |MB_OK);
    }
  }
}

The rest of the application is very basic. I declared the ADO reference to msado15.dll in stdafx.h and the connection and recordset objects are created in HRDB.h. Check out HRDB.cpp for the code to establish the ADO connection to the Access database.

#define INITGUID
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" \
rename_namespace("ADOCG") rename("EOF", "EndOfFile")
using namespace ADOCG;

...

// Global ADO Objects
// connection
_ConnectionPtr  m_pConnection;
_CommandPtr     m_pCommand;
_RecordsetPtr   m_pRecordset;

That's about it. If you encounter any problems compiling the code or have any questions about it, post all inquiries to the forum. This project is two years old and I haven't tested it on any other system than the one it was built on, so I make no promises it will work. Nonetheless, the application has a little bit of everything, so you can pick and choose what you find useful (MFC, ADO, SQL, and MS Access).



How would you rate the usefulness of this content?

Poor 1
2
3
4
5
6
7
8
9
Outstanding

Optional: Tell us why you rated the content this way.
Characters remaining: 1024
Average rating: 3.51 out of 9.

1 2 3 4 5 6 7 8 9
92 people have rated this content.
This page has been viewed 15,351 times
Copyright © 2004-2014 XenoCafe. All Rights Reserved. XenoCafe is Powered by Linux. Free your mind and your wallet. Switch to Linux.