🚀 go-pugleaf

Hi,
        you can use the following code to insert a batch of records into a
datasource with a single call.

#define DBINITCONSTANTS
#include "OleDB25.h"
#include <iostream.h>

const LPOLESTR strMakeDept=OLESTR("Create Table Dept(DeptNo short, Name
char(50))");
const LPOLESTR strMakeEmpInfo=OLESTR("CREATE TABLE EmpInfo (EmpID Integer,
LastName CHAR(25), HireDate DATETIME)");
const LPOLESTR strInsert=L"Insert into Dept values(?, ?)";

void main()
{
 CoInitialize(NULL);
 {
  USES_CONVERSION;
  CODataSource  ODataSource;
  COSession   OSession;
  COTable<CRBase>  BulkRecord;
  CBatchParam<CParamBase>   BatchParam;
  CPInfo    pPInfo[2];
  CPData    pPData[6];
  short    pnDeptNo[3]={1,2,3};

  long    *pnDept=NULL;
  char    *strDeptName=NULL;
  long    nIndex=0;

  DeleteFile(_T("xlsTest.xls"));
  ODataSource.SetProviderString(L"Excel 8.0;");
  ODataSource.SetServer(L"xlsTest");

  //create an excel file
  if(!ODataSource.Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"))
  {
   cout<<"Error information: "<<W2T(ODataSource.m_strError)<<endl;
   return;
  }

  if(!OSession.Open(ODataSource.m_pIDBInitialize))
  {
   cout<<"Error information: "<<W2T(OSession.m_strError)<<endl;
   return;
  }

  //Begin two-phase transaction
  if(!OSession.BeginTrans())
  {
   cout<<"Error Information: "<<W2T(OSession.m_strError)<<"  ";
   return;
  }

  //The following build two tables and define fields
  if(!OSession.ExecuteSQL(strMakeDept))
   goto ROLLBACK;
  if(!OSession.ExecuteSQL(strMakeEmpInfo))
   goto ROLLBACK;

  //if successful, commit the above transaction
  OSession.Commit();

  //it is a must to set a data type for each of parameters
  pPInfo[0].m_nDBType=DBTYPE_I2;
  pPInfo[1].m_nDBType=DBTYPE_STR;

  if(!BatchParam.Open(OSession.m_pIOpenRowset, strInsert, pPInfo))
  {
   cout<<"Error Information: "<<W2T(BatchParam.m_strError)<<"  ";
   return;
  }

  //Prepare an array of parameter data
  pPData[0].m_pData=&(pnDeptNo[0]);
  pPData[2].m_pData=&(pnDeptNo[1]);
  pPData[4].m_pData=&(pnDeptNo[2]);

  pPData[1].m_pData="Sales Dept";
  pPData[3].m_pData="Software Development";
  pPData[5].m_pData="Admin Dept";

  //set 3 sets of parameter data into a data source by a single call at the
fastest speed!
  if(!BatchParam.DoBatch(6, pPData))
  {
   cout<<"Error Information: "<<W2T(BatchParam.m_strError)<<"  ";
   return;
  }

  //open a table using table name
  if(!OpenObject(OSession.m_pIOpenRowset, OLESTR("Dept"), BulkRecord))
  {
   cout<<"Error Information: "<<W2T(BulkRecord.m_strError)<<"  ";
   return;
  }

  //set data types
  BulkRecord.SetDataType(1, DBTYPE_I4);
  if(BulkRecord.GetDataType(2)==DBTYPE_WSTR)
   BulkRecord.SetDataType(2, DBTYPE_STR,
BulkRecord.GetMaxLength(2)/sizeof(WCHAR));

  BulkRecord.MoveFirst();

  //get internal buffers for fields
  pnDept=(long*)BulkRecord.GetData(1);
  strDeptName=(char*)BulkRecord.GetData(2);
  while(!BulkRecord.IsEOF())
  {
   cout<<*pnDept<<"   "<<strDeptName<<endl;
   BulkRecord.MoveNext();
  }
  return;

ROLLBACK:
  OSession.Rollback();

 }
 CoUninitialize();
}

Can't be simpler, easier and more elegant!
--
Yuancai (Charlie) Ye
See 29 OLEDB examples at http://www.geocities.com/oledbpro


"Fred Gassmann" <Fred_Gassmann@ElationTechnologies.com> wrote in message
news:3b79a033_1@dnews...
> How can I access the contents of an Excel spreadsheet and create a string
> with them?
>
> Any help would be greatly appreciated!
>
> Thanks!
> Fred Gassmann
>
>