MDB Viewer with Out_Lookstyle

Environment: VC++6 plus CJ60Lib v 6.07 (CodeGuru: Advanced UI)

Overview
This mdbViewer works with CJ60 version 6.07. It is difficult for me to identify what is cool in this piece of work, I just put Kirk Stowell's framework and CDaoRecordset together and make them work. I think the project may be used as framework for those who are interested with CDaoRecordset and are fancy "Good-look" GUI. In addition to these, the project shows people how to

(1) open any MDB and tables
(2) navigate an opened MDB
(3) make non-SQL query

All these will provide a flexible way to manipulate the database.

However, I have to leave the project as IT IS. Features not implemented:
(1) create a new mdb
(1) print
(2) extract data and statistics
(3) implement different document viewers

Some points

To open an MDB file

Following code is used to open mdb files.

void CmdbViewerDoc::OnFileOpenmdb()
{
// initialization of database pointers
m_strDatabasePath.Empty();
if(m_pRecordset != NULL)
{
ASSERT_VALID(m_pRecordset);
if(m_pRecordset->IsOpen())
m_pRecordset->Close();
delete m_pRecordset;
m_pRecordset = NULL;
}

if(&m_mdbDatabase != NULL)
{
ASSERT_VALID(&m_mdbDatabase);
if(m_mdbDatabase.IsOpen())
m_mdbDatabase.Close();
}

DaoOpenMdb();

}

void CmdbViewerDoc::DaoOpenMdb()
{
if (m_strDatabasePath.IsEmpty()) {
CFileDialog dlg(TRUE, ".mdb", "*.mdb");
if (dlg.DoModal() == IDCANCEL) return;
m_strDatabasePath = dlg.GetPathName();
}

BeginWaitCursor();
try {
// nonexclusive, can update(not read-only)
m_mdbDatabase.Open(m_strDatabasePath, FALSE, FALSE);
}
catch (CDaoException* e) {
::DaoErrorMsg(e);
EndWaitCursor();
e->Delete();
return;
}

GetTableInfo();

CContainerView* pContainer =
((CMainFrame*)AfxGetMainWnd())->GetContainerView();
CFolderView* pFolderView = pContainer->GetFolderView();

pFolderView->UpdateTree();

UpdateAllViews(NULL);
EndWaitCursor();
}

where m_strDatabasePath, m_pRecordset and m_mdbDatabase have been declared in the file mdbViewerDoc.h as:

// Attributes
public:
CString m_strDatabasePath;

private:
CDaoRecordset* m_pRecordset;
CDaoDatabase m_mdbDatabase;

To load a recordset by double clicking threeview item

The treeview is updated as soon as an MDB file is opened. You may view a table by left double clicking an item of the treeview. This is implemented by catching NM_DBLCLK message.

void CFolderView::OnDblclk(NMHDR* pNMHDR, LRESULT* pResult)
{
NM_TREEVIEW* pNMTreeView = (NM_TREEVIEW*)pNMHDR;
// Get the pointer of GetmdbViewerView() from CMainFrame.
CmdbViewerView* pView =((CMainFrame*)AfxGetMainWnd())->GetmdbViewerView();
CmdbViewerDoc* pDoc =GetDocument();

HTREEITEM hti = m_pTreeCtrl->GetSelectedItem();

if (hti)
{
HTREEITEM htiP=m_pTreeCtrl->GetParentItem(hti);
// if the hited item's parent is the data base, it
// must be a table item, open it then.
if(htiP==m_htiDatabase) {
// set table name first
pDoc->SetTableName(m_pTreeCtrl->GetItemText (hti));
// open this table then
pView->ShowDBTable();
}
}
*pResult = 0;
}

The ShowDBTable() is responsible to update the listview. However, before override CListCtrl::SetItemText , we must convert the field value which is COleVariant, into CString, like this:

COleVariant var;
CString str;
... ...
var = m_pSet->GetFieldValue(0);
str=formatedValue(var);

where formatedValue is a public function.

CString CmdbViewerView::formatedValue(COleVariant var)
{
CString str;
switch (var.vt) {
case VT_BSTR:
str = (LPCSTR) var.bstrVal; // narrow characters in DAO
break;
case VT_I2:
str.Format("%d", (int) var.iVal);
break;
case VT_I4:
str.Format("%d", var.lVal);
break;
case VT_R4:
str.Format("%10.2f", (double) var.fltVal);
break;
case VT_R8:
str.Format("%10.2f", var.dblVal);
break;
case VT_CY:
str = COleCurrency(var).Format();
break;
case VT_DATE:
str = COleDateTime(var).Format();
break;
case VT_BOOL:
str = (var.boolVal == 0) ? "FALSE" : "TRUE";
break;
case VT_NULL:
str = "----";
break;
default:
str.Format("Unk type %d\n", var.vt);
TRACE("Unknown type %d\n", var.vt);
}
return str;
}

Non SQL-query

In this project no class is derived from CDaoRecordset and I did not use DoFieldExchange(CDaoFieldExchange* pFX) stuff. This makes thing flexible but it may difficult to make standard SQL query. Instead I wrote two functions to do the job: CmdbViewerView::Enquery() and CmdbViewerView::ExecuteQuery(). The Enquery() was called when L.Click the icon in the OutLookbar pane and then it passes m_queryField and m_queryString to the ExecuteQuery() as shown below:

void CmdbViewerView::ExecuteQuery()
{
if(m_queryString.IsEmpty()) {
m_IsQuery=FALSE;
return;
} else m_IsQuery=TRUE;
ProcessUpdating(_T("Loading recordset ..."));
// suppose a record set has been opened
CmdbViewerDoc* pDoc = GetDocument();
if(m_pListCtrl!=NULL) ClearListView();
pDoc->LoadRecordset();
m_pSet=pDoc->GetRecordSet();
m_TableName=pDoc->GetTableName();
ProcessUpdating(_T("Searching and updating list view ..."));
int queryFieldIndex=0;
if(m_pSet!= NULL)
{
ASSERT_VALID(m_pSet);
m_nFields = (int) m_pSet->GetFieldCount();
int nColSize =80;
CString fieldname;
CDaoFieldInfo fi;

// build columns first
LV_COLUMN lvCol;
lvCol.mask = LVCF_FMT|LVCF_WIDTH|LVCF_TEXT|LVCF_SUBITEM;
lvCol.cx = nColSize;
lvCol.fmt = LVCFMT_LEFT;

for(int i = 0; i < m_nFields; i++) {
m_pSet->GetFieldInfo(i, fi);
fieldname=fi.m_strName;
if(fieldname.Compare(m_queryField)==0) queryFieldIndex=i;
// Insert the ith column into the list control
lvCol.iSubItem = i;
lvCol.pszText = (char*)(LPCTSTR)fieldname;
if(i<m_nColsmax)
{
m_pListCtrl->SetColumn(i, &lvCol);
} else m_pListCtrl->InsertColumn(i, &lvCol);
}
if(m_nColsmax-m_nFields>0) {
for(i = m_nFields; i <m_nColsmax; i++) {
lvCol.iSubItem = i;
lvCol.pszText = (char*)(LPCTSTR)(_T(""));
lvCol.cx = 0; // set this column width as 0
m_pListCtrl->SetColumn(i, &lvCol);
}
}

if(m_nColsmax<m_nFields) m_nColsmax=m_nFields;

// insert items

LV_ITEM lvi;
lvi.mask = LVIF_TEXT|LVIF_IMAGE|LVIF_STATE;
lvi.stateMask = LVIS_STATEIMAGEMASK;
lvi.state = INDEXTOSTATEIMAGEMASK(1);
lvi.iSubItem = 0;
lvi.iImage = 1; // use icon IDI_ICON_RECORDSEL
COleVariant var;
CString str,str1,str2;
int selectedItems=0;
for (int j=0;! m_pSet->IsEOF () ;j++) {
var = m_pSet->GetFieldValue(queryFieldIndex);
str1=formatedValue(var);
str1.MakeLower();
str2=m_queryString;
str2.MakeLower();

if(str1.Find(str2)>=0) {
lvi.iItem = selectedItems;
var = m_pSet->GetFieldValue(0);
str=formatedValue(var);
lvi.pszText = (char*)(LPCTSTR)str;
if (m_pListCtrl->InsertItem(&lvi) != -1) {
for(i = 1; i < m_nFields; i++) {
var = m_pSet->GetFieldValue(i);
str=formatedValue(var);
m_pListCtrl->SetItemText(selectedItems, i, str);
} // loop on column index i
}
if(m_IsQuery) m_selectedRecords.SetAtGrow(selectedItems,j);
selectedItems++;
}
m_pSet->MoveNext();
} // loop on row index j
m_selectedRecords.FreeExtra();

} else {
AfxMessageBox(_T("This recordset is not properly opened"));
return;
}


}

Other matters?

Let the source code tell you more.

Download src - 220 Kb