Using DBGrid in Unbound Mode

This sample was contributed by Adrian Roman.

It took me some time to notice all this, and because I couldn't find anywhere documentation about DBGrid unbound programming in VC++ (only in VB), I think that will be nice to share my observations with others.

First, even documentation says that it accepts (almost) any type of Variant as bookmark, it seems that it is not true. DaoRecordsets have SAFEARRAY as bookmarks (GetBookmark returns Variant which contains VT_ARRAY | VT_UI1), and did not work with DBGrid (the grid simply converts the variant to VT_BSTR).

It may work with VT_I4, or something like this, but because somewhere it says that strings are the best, I used them. Of course, I converted the bookmarks to strings and back.

SchedQ is a dynaset (CDaoRecordset) resulted from a query.

If you find any bugs or have some ideas, please share your findings with me.

And, I almost forgotten! IMPORTANT! If you generate RowBuffer class from OCX's type library, modify the constructor that gets LPDISPACH as parameter to call base class constructor with the second one setted to FALSE (it is implicitly TRUE). Or else, the whole thing will crash when RowBuffer object gets destroyed.

Some supplementary tips: use BeforeColUpdate and KeyPress events to validate a field and, respectively, to filter key input.

Please don't send me e-mails with questions about using it in bound mode. Just add on the dialog the Microsoft Remote Data Control before inserting the dbgrid, and play a little with properties. You'll figure out how to use it.

For those who want to modify columns at run-time: The generated GetColumns() member function of dbgrid's class is really a GetColumn function (returns a LPDISPATCH to a single column - so you can use this LPDISPATCH to construct Column objects, not Columns). If you want to obtain the LPDISPATCH to Columns collection, rename it as GetColumn, and rewrite GetColumns like this one:

LPDISPATCH CMsDgridCtrl::GetColumns()
{
   LPDISPATCH result;
   InvokeHelper(0x8,DISPATCH_PROPERTYGET,VT_DISPATCH,
                (void*)&result,NULL,NULL);
   return result;
}

You can use the returned LPDISPATCH to construct the Columns object.

Now, the code for feeding the grid with data, deletind records, and obtaining data back when writes occur:

void CMyView::OnUnboundReadDataDbgridMy(LPDISPATCH RowBuf,
                                        VARIANT FAR* StartLocation,
                                        BOOL ReadPriorRows)

{
   // TODO: Add your control notification handler code here
   RowBuffer buf(RowBuf);
   VARIANT varbok;
   varbok.vt=VT_ARRAY | VT_UI1;
   long Row,RowsFetched;
   VARIANT var;
   RowsFetched=0;
   if(StartLocation->vt==VT_NULL){
      if(ReadPriorRows){
         try{
            SchedQ->MoveLast();
         }catch(CDaoException* e){
            e->Delete();
         }
      }else{
         try{
            SchedQ->MoveFirst();
         }catch(CDaoException* e){
            e->Delete();
         }
      }
   }else{
      // Find the position to start reading based on the
      // StartLocation bookmark and ReadPriorRows parameter
      try{
         VectorFromBstr(StartLocation->bstrVal,&varbok.parray);
         SchedQ->SetBookmark(COleVariant(varbok));
         if(ReadPriorRows)SchedQ->MovePrev();
         else SchedQ->MoveNext();
      }catch(CDaoException* e){
         e->Delete();
      }
   }
   // Transfer data from our data set array to the RowBuf
   // object which DBGrid uses to display the data
   for(Row = 0; Row<buf.GetRowCount() ;Row++){
      if(SchedQ->IsEOF() || SchedQ->IsBOF())break;
      var.vt=VT_I4;
      var.lVal=SchedQ->m_WorkCodeID;
      if(buf.GetColumnCount()>0)buf.SetValue(Row, 0,var);
      COleVariant olvi(SchedQ->m_TimeIn);
      var=*((LPVARIANT)olvi);
      if(buf.GetColumnCount()>1)buf.SetValue(Row, 1,var);
      COleVariant olvo(SchedQ->m_TimeOut);
      var=*((LPVARIANT)olvo);
      if(buf.GetColumnCount()>2)buf.SetValue(Row, 2,var);
      // Set bookmark using CurRow which is also our
      // array index
      var.vt=VT_BSTR;
      BstrFromVector(((LPVARIANT)SchedQ->GetBookmark())->
                     parray,&var.bstrVal);
      buf.SetBookmark(Row,var);
      RowsFetched++;
      if(ReadPriorRows)SchedQ->MovePrev();
      else SchedQ->MoveNext();
   }
   buf.SetRowCount(RowsFetched);
}


void CMyView::OnUnboundWriteDataDbgridMy(LPDISPATCH RowBuf,
                                         VARIANT FAR* WriteLocation)
{
   // TODO: Add your control notification handler code here
   RowBuffer buf(RowBuf);
   VARIANT varbok;
   varbok.vt=VT_ARRAY | VT_UI1;
   COleVariant var;
   CString str;
   try{
      VectorFromBstr(WriteLocation->bstrVal,&varbok.parray);
      SchedQ->SetBookmark(COleVariant(varbok));
   }catch(CDaoException* e){
      e->Delete();
      buf.SetRowCount(0);
      return;
   }

   //****************************************************
   //Here was a portion of code that validated the record
   //****************************************************
   // Only columns that have been changed will be updated.
   // Otherwise, the value will be set to NULL
   try{
      SchedQ->Edit();
   }
   catch(CDaoException* e){
      e->Delete();
      buf.SetRowCount(0);
      return;
   } 
   if(buf.GetValue(0L, 0).vt!=VT_NULL){
      var=buf.GetValue(0, 0);
      str=CString(var.bstrVal);
      SchedQ->m_WorkCodeID=atol(str);
   }
   if(buf.GetValue(0L, 1).vt!=VT_NULL){
      var=buf.GetValue(0, 1);
      str=CString(var.bstrVal);
      SchedQ->m_TimeIn.ParseDateTime(str);
   }
   if(buf.GetValue(0L, 2).vt!=VT_NULL){
      var=buf.GetValue(0, 2);
      str=CString(var.bstrVal);
      SchedQ->m_TimeOut.ParseDateTime(str);
   }
   try{
      SchedQ->Update();
   }catch(CDaoException *e){
      SchedQ->CancelUpdate();
      e->Delete();
      buf.SetRowCount(0);
   }
}


void CMyView::OnUnboundAddDataDbgridMy(LPDISPATCH RowBuf,
                                       VARIANT FAR* NewRowBookmark) 
{
   // TODO: Add your control notification handler code here
   RowBuffer buf(RowBuf);
   COleVariant var;
   CString str;
   NewRowBookmark->vt=VT_NULL;

   //****************************************************
   //Here was a portion of code that validated the record
   //****************************************************
   try{
      SchedQ->AddNew();
      SchedQ->Update();
      SchedQ->SetBookmark(SchedQ->GetLastModifiedBookmark());
      SchedQ->Edit();
   }catch(CDaoException* e){
      e->Delete();
      buf.SetRowCount(0);
      return;
   } 
   SchedQ->m_EmployeeID=m_pSet->m_EmployeeID;
   try{
      if(buf.GetValue(0L, 0).vt!=VT_NULL){ 
         var=buf.GetValue(0, 0);
      }else{
         var.vt=VT_I4; 
         var.lVal=0;
         Column col(m_SchedGrid.GetColumns(var));
         var=col.GetDefaultValue();
      }
      str=(char*)_bstr_t(var.bstrVal);
      SchedQ->m_WorkCodeID=atol(str);
      if(buf.GetValue(0L, 1).vt!=VT_NULL){
         var=buf.GetValue(0, 1);
      }else{
         var.vt=VT_I4; 
         var.lVal=1;
         Column col(m_SchedGrid.GetColumns(var));
         var=col.GetDefaultValue();
      }
      str=(char*)_bstr_t(var.bstrVal);
      SchedQ->m_TimeIn.ParseDateTime(str);
      if(buf.GetValue(0L, 2).vt!=VT_NULL){
         var=buf.GetValue(0, 2);
      }else{
         var.vt=VT_I4; 
         var.lVal=2;
         Column col(m_SchedGrid.GetColumns(var));
         var=col.GetDefaultValue();
      }
      str=(char*)_bstr_t(var.bstrVal);
      SchedQ->m_TimeOut.ParseDateTime(str);
      var.vt=VT_I4;
      SchedQ->Update();
   }catch(CDaoException *e){
      e->Delete();
      SchedQ->CancelUpdate();
      SchedQ->Delete();
      buf.SetRowCount(0);
   }
   NewRowBookmark->vt=VT_BSTR;
   BstrFromVector(((LPVARIANT)SchedQ->GetBookmark())->parray,
                   &(NewRowBookmark->bstrVal));
}


void CMyView::OnUnboundDeleteRowDbgridMy(VARIANT FAR* Bookmark)
{
   // TODO: Add your control notification handler code here
   VARIANT varbok;
   varbok.vt=VT_ARRAY | VT_UI1;
   VectorFromBstr(Bookmark->bstrVal,&varbok.parray);
   try{
      SchedQ->SetBookmark(COleVariant(varbok));
      SchedQ->Delete();
   }catch(CDaoException* e){
      Bookmark->vt=VT_NULL;
      e->Delete();
   }
}