SQLServer Search Dialog Class

Environment: VC6 MSSQL65 ODBC MFC

What I did with this class is to automatize (not MS’s automation) several processes to do a search in a MS Sql Server database, and show
the results, retaining the data on an internal structure.

With this class you make your dialog with fields to be filled by the user with data to be searched.
So the class will:

  • Expand the dialog size to make room for the new controls;
  • Create a Group static control, a ListCtrl and 4 buttons: Cancel, Search, Clear and Ok;
  • Create an accelerator and bind it to the buttons;
  • Make the search, when the user ask for, and fill the listctrl with the result data, maintaining an internal structure with this data;

Note that this implementation is the very first functional one, but yet useful to many programs.

To use the class follow this steps:

  1. Create your dialog containing the editboxes that will have the data used as parameters of the query;
  2. Create the dialog’s class with ClassWizard, deriving it from CDialog (since we can’t use other classes to do it);
  3. Modify the files of the class created, changing CDialog by CDbSearchDlg everywhere CDialog appears.
  4. Include on the Dialog’s class’s header file the appropriate #include "DbSearchDlg.h"
  5. Close the project, delete the .clw file, reopen the project and call classwizard (ctrl-w). It will tell you that must recreate the file by source files. Follow the instructions and the new clw created will be compatible with the changes you just make.
  6. With classwizard, create the messge handler OnInitDialog.
  7. Before the call of base’s OnInitDialog, do the following (refer to the example below for details):
    • Inform what controls the input data will come from with AddFormField.
    • Inform the ColumnLabels and sql column names the output data will come from with AddResultColumn.
    • Inform the ConnectionString with SetDsn;
    • Inform the query with SetSql, using question mark where input data must be placed.
      Obs.: The columns returned by this query must be in the same order as informed with AddResultColum,
      and the question marks must be in the same order as informed with AddFormField.

  8. Optionally, override the function GetInfo to format the output data if you asked for with flag RCF_GET_INFO in AddResultColumn.
  9. Optionally, override the OnOk (manualy, since in desing time the button Ok don’t exsist) to validate and/ou take some information, like the item(s) selected, etc.

For example, to reach the dialog showed in the Figure 2, you make the dialog like the Figure 1 and write just this (omitting what was done by classwizard):

TestDlg.h:


// Include the base class’s file
#include “DbSearchDlg.h”

// Include the DbFormEdit class file (it just make the empty
// control to have an asterisk).
#include “DbFormEdit.h”

// Originaly the class wizard makes the class derived from
// CDialog, so we change to CDbSearchDlg
class CTstDlg : public CDbSearchDlg
{ …


// Implementation
protected:
// Overriding the base’s member function GetInfo to format
// the original data to the display version of it.
virtual void GetInfo(UINT nSqlField,
const CString strOriginal,
CString &target);

// Added with classwizad.
// Generated message map functions
//{{AFX_MSG(CTstDlg)
virtual BOOL OnInitDialog();
//}}AFX_MSG

// Added manualy, because in desining time the button
// don’t exists.
virtual void OnOK();
DECLARE_MESSAGE_MAP()
};

TestDlg.cpp

// TstDlg.cpp : implementation file
...

// Change the original 'CDialog' putted here by ClassWizard 
// with the 'CDbSearchDlg', and everywhere CDialog appears.
BEGIN_MESSAGE_MAP(CTstDlg, CDbSearchDlg)
 //{{AFX_MSG_MAP(CTstDlg)
 //}}AFX_MSG_MAP
 ON_BN_CLICKED(IDOK, OnOK)
END_MESSAGE_MAP()

///////////////////////////////////////////////////////////
// CTstDlg message handlers

BOOL CTstDlg::OnInitDialog()
{
 // Inform to base class what controls the input data 
 // comes from:
 // AddFormField(CTRL_ID,FLAGS) - Must be EditBox;
 AddFormField(IDC_PRODUTO    ,FFF_SEARCH_SUBSTRING   );
 AddFormField(IDC_ID         ,FFF_EXACT_PHRASE       );
 AddFormField(IDC_LABORATORIO,FFF_SEARCH_SUBSTRING   );


 // Inform the label and what recordset's field the output 
 // data comes from:
 // AddResultColumn(ID,LABEL,SQL_FIELD_NAME,FLAGS);
 AddResultColumn(0,"Id"          ,"produto_id"   ,RCF_GET_INFO   );
 AddResultColumn(1,"Produto"     ,"produto"      ,RCF_NONE       );
 AddResultColumn(2,"Laboratorio" ,"laboratorio"  ,RCF_GET_INFO   );


 // Inform the Connection String to be used.
 SetDsn( "DSN=Ultrafarma;UID=Atendente_ultrafarma;PWD=ultra");

 // Inform the query, respecting the order of form-fields 
 // and sql-field-names established above:
 SetSql( "select                                              rn"
         "    t1.produto_id as produto_id,                    rn"
         "    t1.nome    as produto,                          rn"
         "    t2.nome    as laboratorio                       rn"
         "from                                                rn"
         "    produtos t1                                     rn"
         "    inner join                                      rn"
         "        laboratorios t2                             rn"
         "    on                                              rn"
         "        t1.lbrtr_id = t2.lbrtr_id                   rn"
         "where                                               rn"
         "    t1.nome like '?' and                            rn"
         "    convert(varchar,t1.produto_id)  like '?' and    rn"
         "    t2.nome like '?'                                rn");

 // Call the base class's. It will create the controls, 
 // expand dialog's size, etc.
 CDbSearchDlg::OnInitDialog();

 // Here you may alter some characteristics of what 
 // was created.
 // We have at this point the following controls:
 // IDC_SEARCH   101 -> The Search button
 // IDC_CLEAR    102 -> The Clear button
 // IDC_RESULTS 103  -> The ListCtrl
 // IDC_GROUP   104  -> The Group Static Control.

 // You also may reach the ListCtrl by the function GetListCtrl().

 return TRUE;
}

// This function will be called for each row by the base 
// class, where you asked for with the flag RCF_GET_INFO.
void CTstDlg::GetInfo(UINT nSqlField,
                      const CString strOriginal,
                      CString &target)
{
 // Here we have the original data in strOriginal.
 // What you put on &target will be showed in the listctrl.
 switch(nSqlField)
 {
  case 0: // Id
  // O texto vem em currency (local locale format)
  {
   CStrCurrency aCur;
   aCur = strOriginal;
   target.Format("%ld",((COleCurrency)(aCur)).m_cur.int64 / 10000);
  }
  break;

  case 2: // Laboratorio
   // Involving the original string with [], just to see 
   // the GetInfo working.
   target = strOriginal;
   target.MakeLower();
   target += "[t]";
  break;
 }

 return;
}

// Here I'm getting the item selected by the user.

// The result data is stored in an CPtrList with rows of 
// CStringArray.

// To acess this data based on the ListCtrl itens, the 
// POSITION of this CPtrList is stored on lParam of the 
// ListCtrl itens.

void CTstDlg::OnOK()
{
 UpdateData();

 // Taking the position of the first selected item:
 POSITION pos = GetListCtrl().GetFirstSelectedItemPosition();

 // If there is no item selected, we as to the user if 
 // he forgot:
 if(pos == NULL)
 {
  AfxMessageBox("No item was selected.n"
                "Select an item or cancel the operation.");
  return;
 }

 // Take the row of the internal struct with the data 
 // refering the selected item:
 // Listctrl's selected item index
 int             iDataRow    ;

 // CPtrList's position of internal data structure.
 POSITION        posDataRow  ;

 // Each element in this StringArray correspond to the 
 // column's data of that row.
 CStringArray   *pDataRow    ;

 iDataRow   = GetListCtrl().GetNextSelectedItem(pos);
 posDataRow = (POSITION)GetListCtrl().GetItemData(iDataRow);
 pDataRow   = (CStringArray *)GetDataList().GetAt(posDataRow);

 // The only data I'm interested is the Product ID, on the 
 // column 0 as informed in SetResultColumn:
 m_dwProdutoId = atoi(pDataRow->GetAt(0));

 // Done!
 CDbSearchDlg::OnOK();
}

I am working on a help file that will master this class in detail’s. When done, I’ll put here.

Downloads

Download demo project – 37 Kb – Will need debug and/or release binaries

Download source – 25 Kb – Makes the DLL and LIB files.

Download debug binaries – 17 Kb

Download release binaries – 12 Kb

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read