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 \n\n";

hResult = GetAdoErrorMessage(m_pConnection,
&csErrorMessage);

csMessage += csErrorMessage;
csMessage += "\nmethod: CTestMeteorlogixApp::OnTestDatabaseAdoBulkload()";

AfxMessageBox(csMessage);

}

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

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

}Yup...wrong forum...

Why not use Stored procedures though?|||Have you checked the errors (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcolerrors.asp) collection after calling UpdateBatch (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdatebatch.asp)? Check out the VC example (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdatebatchxvc.asp) to see how they handle the errors.

-PatP|||Originally posted by Brett Kaiser
Yup...wrong forum...

Why not use Stored procedures though?

Thanks for the response. Currently we are using a BULK INSERT to get about 100,000 rows / 30 columns into a table ... then calling a Stored Procedure to do some derivations on the data and populating another table with the results.

We have lots of 'stuff' working on the SQL server and are looking to move some of the business rules from the server to the middle tier. The entire process described above takes about 10-15 minutes and if we could get that type of performace in ADO on the C++ side then we would be happy.

Any comments / suggestions are appreciated
Thanks,
Chris