Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Saint Paul, Minnesota
    Posts
    6

    Unanswered: 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 = :ysAllocString(L"Name");
    nFieldIndex++;

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

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

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

    vFieldList[nFieldIndex].vt = VT_BSTR;
    vFieldList[nFieldIndex].bstrVal = :ysAllocString(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 = :ysAllocString(L"BLUE");
    nValueIndex++;

    vValueList[nValueIndex].vt = VT_BSTR;
    vValueList[nValueIndex].bstrVal = :ysAllocString(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.");

    }

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yup...wrong forum...

    Why not use Stored procedures though?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you checked the errors collection after calling UpdateBatch? Check out the VC example to see how they handle the errors.

    -PatP

  4. #4
    Join Date
    Mar 2004
    Location
    Saint Paul, Minnesota
    Posts
    6

    Using Stored Procedures

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •