ADO Disconnected Recordset

Hi ...

This is a C++ / ADO / SQL question. Maybe not the right forum but I am guessing there are some programmers out there ...

I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.

Any comments are appreciated,
Thanks,
Chris

void CTestApp::TestDatabaseUpdateBatch1a(void)
{
int nDataCount = 0;
long nIndex = 0;
long nIndex2 = 0;

CString csMessage;
CString csErrorMessage;
CString csTemp;
CString csSQL;

BOOL bIsOpen;
BOOL bIsEmpty;

long nCount = 0;
int nTemp = 0;
int nLimit = 0;

int nTempInt = 0;
long nTempLong = 0;
double nTempDouble = 0;

HRESULT hResult;

SYSTEMTIME st;


int i = 0;

string strTemp;

_variant_t sval;

_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;

COleSafeArray colesaFieldList;
COleSafeArray colesaDataList;

vector<COleSafeArray> *pvecDataList;

pvecDataList = new vector<COleSafeArray>;


COleDateTime oledtCurrentDate = COleDateTime::GetCurrentTime();

// Convert the OleDateTime to the varient
// COleVariant vCurrentDateTime(oledtCurrentDate);
COleVariant vCurrentDateTime;

CMxTextParse *pMxTextParse = NULL;

CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd();
CFrameWnd* pChild = pMainFrame->GetActiveFrame();
CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView();

pView->WriteLog("Start TestDatabaseUpdateBatch1a.");
pView->WriteLog("Load table using AddNew() and UpdateBatch().");


// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
_RecordsetPtr pRecordset = NULL;

try
{
// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));

// Replace Data Source value with your server name.
bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';"
"Initial Catalog='AlphaNumericData';"
"User Id=cmacgowan;Password=cmacgowan");

// Open the ado connection
pConnection->Open(bstrConnect,"","",adConnectUnspecified);

// Create an instance of the database
pRecordset.CreateInstance(__uuidof(Recordset));

// Select the correct sql string. Note that we are creating an
// empty string by doing a select on the primary key. We are only
// doing inserts and we do not want to bring data back from the
// server

csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL";
// csSQL = "SELECT * FROM dbo.DICastRaw1Hr";


pRecordset->PutRefActiveConnection(pConnection);
pRecordset->CursorLocation = adUseClient;


pRecordset->Open(csSQL.AllocSysString(), vNull, adOpenStatic, adLockOptimistic, -1);

// Test to see if the recordset is connected
if(pRecordset->GetState() != adStateClosed)
{
// The recordset is connected, we will see if we are
// at the end

if((pRecordset->BOF) && (pRecordset->GetadoEOF()))
{
// The recordset is empty
bIsEmpty = false;
}


if(pRecordset->GetadoEOF())
{
bIsOpen = false;
}
else
{
// disconnect the database
pRecordset->PutRefActiveConnection(NULL);
}
}


// disconnect the database
// pRecordset->PutRefActiveConnection(NULL);

// Disassociate the connection from the recordset.
pRecordset->PutRefActiveConnection(NULL);

// Set the count
nCount = 1;

// now we will scroll through the file
while(nCount > 0)
{
nCount--;

nDataCount = 10;

// test that we got some data
if (nDataCount >= 0)
{
// Start the insert process
// m_pRecordset->AddNew();

COleSafeArray warningList;
//int index, listIndex = -1, bitIndex; // indexing variables
// long lowIndex, highIndex, arrayIndex[2];

VARIANT vFieldList[25];
VARIANT vValueList[25];

int nFieldIndex = 0;
int nValueIndex = 0;


// Setup the fields
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Name");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Section");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Code");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Latitude");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Longitude");
nFieldIndex++;


pView->WriteLog("Set data using AddNew() ...");

// COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is
// a wrapper for VARIANTs themselves. If you need to create a
// variant, you can say:
COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();

// Convert the OleDateTime to the varient
COleVariant vCurrentDateTime2(oledtCurrentDate2);

//Set the DATE variant data type.
memset(&st, 0, sizeof(SYSTEMTIME));
st.wYear = 2000;
st.wMonth = 1;
st.wDay = 1;
st.wHour = 12;

// vect is a vector of COleSafeArrays containing the records
for(i = 0; i < 10; i++)
{

// Setup the data
nValueIndex = 0;
vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE");
nValueIndex++;

vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"KSTP");
nValueIndex++;

vValueList[nValueIndex].vt = VT_I4;
vValueList[nValueIndex].dblVal = 100 + nFieldIndex;
nValueIndex++;

vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 11.11 + nFieldIndex;
nValueIndex++;

vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 22.22 + nFieldIndex;
nValueIndex++;

// Add the record to the recordset
pRecordset->AddNew(vFieldList, vValueList);
}



pView->WriteLog("Call UpdateBatch().");

// Re-connect.
pRecordset->PutRefActiveConnection(pConnection);

// Send updates.
pRecordset->UpdateBatch(adAffectAll);

// Close the recordset and the connection
pRecordset->Close();
pConnection->Close();

}
}
}
catch(_com_error *e)
{
CString Error = e->ErrorMessage();
AfxMessageBox(e->ErrorMessage());
pView->WriteLog("Error processing TestDatabase().");
}
catch(...)
{
csMessage = "Undefined exception handled. Error message details

";

hResult = GetAdoErrorMessage(m_pConnection,
&csErrorMessage);

csMessage += csErrorMessage;
csMessage += "
method: CTestMeteorlogixApp::OnTestDatabaseAdoBulkload()";

AfxMessageBox(csMessage);

}

csTemp.Format("Last Row %03d DIcastId = %s ", nIndex, strTemp.c_str());
pView->WriteLog(csTemp);

pView->WriteLog("End TestDatabaseUpdateBatch1.");

}