MDB Viewer with Out_Lookstyle

(xiaojian.liu@port.ac.uk).
Home Page : http://www2.civl.port.ac.uk/structures/javafe/javacorner/content.html

 

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

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read