Need help creating ADO connection 1

[ShawnCoutts]

I am relatively new to ADO in BCB6, and have been unable to figure out how/where to start. I know that I need to make an TADOConnection object, but when i try to compile my code:

 TADOConnection ado = new TADOConnection(null);

I get an error: Undefined symbol 'TADOConnection'

do I need to include any library files? and which activeX component do I need to install to get ADO capabilities??

 

[2ffat]

I usually drag and drop an ADO component onto my form so it's easier to set up. After all, that's why I like BCB.

If you want to do it manually, yes, you need the correct libraries. In your header file, use #include <ADODB.hpp> and you might also have to add #include <DB.hpp>. When I drop an ADOQuery component onto my form, both header files are added to my header file.

James P. Cottingham
-----------------------------------------
^{I'm number 1,229!
I'm number 1,229!}

 

[ShawnCoutts]

thanks a lot. Which activeX component do i need to install to get the ADO components?

 

[ShawnCoutts]

and i guess i should also ask, does BCB6 come with the ADO headers? Because when i tried to compile it, i received an error stating: Unable to open inlcude file ADODB.hpp

 

[2ffat]

Different "editions" of BCB come with different libraries. There are the personal, pro, and developer's editions. I have the developer's so it comes with all sorts of libraries that yours may not have. Sorry, I didn't think about that.




James P. Cottingham
-----------------------------------------
^{I'm number 1,229!
I'm number 1,229!}

 

[2ffat]

Another thought. Does your version have dbGO?

Also, Borland has released a free version of their software call Turbo C++ at

http://www.turboexplorer.com/cpp

. Bob Swart has written an intro manual for DB and Turbo C++ at

http://www.bobswart.nl/Weblog/Blog.aspx?RootId=5:580

. Both are free. See if anything there helps.

James P. Cottingham
-----------------------------------------
^{I'm number 1,229!
I'm number 1,229!}

 

[ShawnCoutts]

i have bcb6 personal, and i dont think that it comes with those libraries. Is there anywhere I can download the libraries?

 

[2ffat]

I don't know. Check out the Turbo edition. I think it has the libraries you need. It is equivalent to Builder C++ 2006 personal, or so I've been told.

James P. Cottingham
-----------------------------------------
^{I'm number 1,229!
I'm number 1,229!}

 

[ShawnCoutts]

thanks. Ill let you know what i come up with

 

[ShawnCoutts]

I have gotten a little farther, but now I have run into a strange error and i cannot understand why.

in the ADODB.hpp file i get the error:
[C++ Error] ADODB.hpp(290): E2015 Ambiguity between 'ConnectionEventsVt' and 'Adodb_tlb::ConnectionEventsVt'

and then it directs me to the following line:

operator ConnectionEventsVt*(void) { return (ConnectionEventsVt*)&__ConnectionEventsVt; }

theres also another error:
[C++ Error] ADODB.hpp(917): E2015 Ambiguity between 'RecordsetEventsVt' and 'Adodb_tlb::RecordsetEventsVt'

and this line of code:

operator RecordsetEventsVt*(void) { return (RecordsetEventsVt*)&__RecordsetEventsVt; }

why does it say its ambiguous??

 

[2ffat]

Apparently RecordsetEventsVt is defined in different classes. The compiler can't decide which one to use. You have to declare which namespace to use. For example, if you need to use Adodb_tbl namespace try

operator Adodb_stl::RecordsetEventsVt*(void)...

James P. Cottingham
-----------------------------------------
^{I'm number 1,229!
I'm number 1,229!}

 

[ShawnCoutts]

thanks, guess i'll see if it works.

 

[ShawnCoutts]

instead of modding the .hpp file, i decided to use turbo C++ explorer, it works similar to bcb6 as you had mentioned. I have successfully built the project, but everytime i try to open my ADOConnection object, I get an error:

"Could not find installable ISAM"

I have spent close to 4 hours searching google for a solution, but to no avail. Everything I have tried has done me no good. I have even tried using 3 or 4 different connection strings.

I am trying to connect to an Access 2002 database and am using the connection string:

"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Files\Data\Powell.mdb;Uid=admin;Pwd="

this problem has caused me no end of trouble, any/all help would be greatly appreciated

 

[2ffat]

Because the connection string tends to be fairly complex, I use Window's PromptDataSource to get my connection string. There are two ways to use this. On my BCB6, when I place an ADO component on my form, I press the "three dots" button on that component's ConnectionString. This will bring up another form. If I press the "Build" button the PromptDataSource program is displayed. I can then use that to set everything up and test the connection before closing it. When the program is closed, it puts the correct info in the connection string.

Alternately, you can call the program directly from your program. For example:

WideString WADOStr = ProductionADODataSet->ConnectionString;
WideString WADOSetupStr = PromptDataSource((int)Application->Handle, WADOStr);

    if (WADOSetupStr != "")
    {
        if (ProductionADODataSet->Active) ProductionADODataSet->Close();
        ProductionADODataSet->ConnectionString = WADOSetupStr;
    }

As you can see from the code, PromptDataSource needs the Handle number of the calling app and a default setup string and returns a wide string with the ADO string. If the default setup string is valid, it will be used in PromptDataSource as the default.

Instead of doing the above, which requires the PromptDataSource be called everytime the program is run, I put the return string (WADOSetupStr) in the registry or an INI file (encrypted, of course) so it can be called everytime the program starts.

BTW, I don't use Access but I do use this to connect to SQL.

Also, remember to check the correct password box, either "Blank password," or "Allow saving password." If you don't, then the password won't be returned. This was a bear to figure out when I first started using this.

James P. Cottingham
-----------------------------------------
^{I'm number 1,229!
I'm number 1,229!}

 

[ShawnCoutts]

thanks a lot. I would much rather use SQL than Access, but since its not an option I will make do with what I am given.

Setting up the connection through the promptdatasource did wonders. My code compiles and runs just fine now. Thanks for all your help

 

[ShawnCoutts]

ok, i have the connection set up, and i have run a query using a tadocommand object to get a dataset to work with.

the problem i am having now, is that i dont know how to access the data in the dataset. I want to use the data to populate a listbox.

ado->Open();
cmd->CommandText = "SELECT CoName FROM Customers";
data->Recordset = cmd->Execute();
ado->Close();

this is what i am doing. First off, am i attempting to get the data in the right manner? and second, if i am getting the data correctly, then how do i access it in the dataset?

 

[2ffat]

OK, here we go. I prefer to use the ADODataSet or the ADOQuery. The query has an advantage over the dataset in that it can do update via SQL statements. I don't use the command object but I suspect you access the data just like I do with datasets or queries.

Here are some example of how I do it. First, using the dataset. Note: all of these are snippets and may not run without some "tweeking."

AnsiString SQLStr = "SELECT CoName FROM Customers";
try
{
    CustADODataSet->Close();
    CustADODataSet->CommandText = SQLStr;
    if (!CustADODataSet->Prepared) ProductionADODataSet->Prepared = true;
    CustADODataSet->Open();

    int NumSQLRecInt = CustADODataSet->RecordCount;
    if (NumSQLRecInt > 0)           // We have records to process
    {
        CustADODataSet->First();    // Start with first record
        for (int j = 0; j < NumSQLRecInt; ++j)  // Collect each record
        {
            AnsiString CoNameStr = CustADODataSet->FieldByName("CoName")->AsString;
            // Do something here

            CustADODataSet->Next();
        }                           // Collect each record
    }                               // We have records to process
    else Application->MessageBox("No records found.", "0 Records", MB_OK);
}
catch ( ... )                       // Error! Opps.
{
    Application->MessageBox("Error running SQL command!", "Data Set Error", MB_OK);
}                                   // Error! Opps.
CustADODataSet->Close();

You can do the same thing using the query, too.

try
{
    CustADOQuery->Close();
    CustADOQuery->SQL->Clear();    // Needed!!!!
    CustADOQuery->SQL->Add(SQLStr);
    CustADOQuery->Prepared = true; // You could use an if statement like above, too
    CustADOQuery->Open();

    int NumSQLRecInt = CustADOQuery->RecordCount;
    if (NumSQLRecInt > 0)           // We have records to process
    {
        CustADOQuery->First();
        for (int j = 0; j < NumSQLRecInt; ++j)  // Collect each record
        {
            AnsiString CoNameStr = CustADOQuery->FieldByName("CoName")->AsString;
            // Do something here

            CustADOQuery->Next();
        }                           // Collect each record
    }                               // We have records to process
}                                  // Error! Opps
catch ( ... )
{
    Application->MessageBox("Error in SQL statement collecting name.", "SQL Error", MB_OK);
}                                  // Error! Opps
ProductionSummaryADOQuery->Close();

The advantage the query has is that the dataset MUST return data. If you try to do and INSERT or UPDATE using the dataset, nothing is returned and the dataset kicks out an exception. The query doesn't.

AnsiString UpdateSQLStr = "UPDATE ProductionSummary SET Hours = '" + HoursStr +"'"; // HoursStr set up elsewhere
try
{
    ProductionSummaryADOQuery->Close();
    ProductionSummaryADOQuery->SQL->Clear();
    ProductionSummaryADOQuery->SQL->Add(UpdateSQLStr);
    ProductionSummaryADOQuery->Prepared = true;
    ProductionSummaryADOQuery->ExecSQL(); // Note the difference
}
catch (...)                        // Error! Opps
{
    Application->MessageBox("Error in SQL statement update production summary.", "SQL Error", MB_OK);
}                                  // Error! Opps
ProductionSummaryADOQuery->Close();

As far as getting and changing records, you can use either the dataset or the query as above.

For example, here is an actual snippet from one of my programs. It uses the dataset but a query can work, too, if you call the query's dataset. Note that not every variable I used is shown but it does show you can get fairly complex.

// Set up SQL query
AnsiString SQLStr = "SELECT Production.MachineDate, Production.Machine, Production.MachineShift,";
SQLStr += " PurchaseHeading.CustomCutFlag, Production.ExportDomestic, Production.ParcelNumber,";
SQLStr += " Production.CutCode, Production.Specie, SUM(Production.Bdft) AS Bdft,";
SQLStr += " SUM(Production.SquareFeet) AS SquareFeet, SUM(Production.SquareMeters) AS SquareMeters, COUNT(*) AS LogCount";
SQLStr += " FROM Production Production LEFT OUTER JOIN";
SQLStr += " PurchasedLogs PurchasedLogs ON Production.LogNumber = PurchasedLogs.LogNumber LEFT OUTER JOIN";
SQLStr += " PurchaseHeading PurchaseHeading ON PurchasedLogs.PurchaseNumber = PurchaseHeading.PurchaseNumber";
SQLStr += " LEFT OUTER JOIN WIP WIP ON Production.ProductionNumber = WIP.ProductionNumber";
SQLStr += " WHERE (Production.MachineDate >= CONVERT(DATETIME, '" + SelectedDateStr + "', 102)) AND";
SQLStr += " (Production.MachineDate <= CONVERT(DATETIME,'" + SelectedDateStr + " 23:59:59.000', 102))";

if (ParcelListBox->Items->Count > 0)  // Get excluded parcels
{
    for (int j = 0; j < ParcelListBox->Items->Count; ++j)
    {
        AnsiString XParcelStr = ParcelListBox->Items->Strings[j];
        SQLStr += " AND (Production.ParcelNumber <> '" + XParcelStr + "')";
    }
}                                   // Get excluded parcels

SQLStr += " AND (WIP.Mill = '" + MillStr + "')";
SQLStr += " GROUP BY Production.MachineDate, Production.Machine, Production.MachineShift, PurchaseHeading.CustomCutFlag,";
SQLStr += " Production.ExportDomestic, Production.ParcelNumber, Production.CutCode, Production.Specie";

try
{
    // See if there are records already in Production Summary
    int ProdSumRecInt = ProductionSummaryADODataSet->RecordCount;
    if (ProdSumRecInt != 0)
    {
        Application->MessageBox("Records for this date already exists in Production Summary.", "Data Error", MB_OK);
        ProcessStaticText->Caption = "Records already exists!";
        return;
    }

    // If we don't have records, add them
    ProductionADODataSet->Close();
    ProductionADODataSet->CommandText = SQLStr;
    if (!ProductionADODataSet->Prepared) ProductionADODataSet->Prepared = true;
    ProductionADODataSet->Open();

    int NumSQLRecInt = ProductionADODataSet->RecordCount;
    if (NumSQLRecInt > 0)           // We have records to process
    {
        ProcessStaticText->Caption = "Updating the production summary . . .";

        ProductionADODataSet->First();
        for (int j = 0; j < NumSQLRecInt; ++j)  // Collect each record
        {
            TDateTime MachineDate = ProductionADODataSet->FieldByName("MachineDate")->AsDateTime;
            AnsiString MachineStr = ProductionADODataSet->FieldByName("Machine")->AsString;
            AnsiString MachineShiftStr = ProductionADODataSet->FieldByName("MachineShift")->AsString;
            AnsiString CCFlagStr = ProductionADODataSet->FieldByName("CustomCutFlag")->AsString;
            AnsiString MarketStr = ProductionADODataSet->FieldByName("ExportDomestic")->AsString;
            AnsiString ParcelStr = ProductionADODataSet->FieldByName("ParcelNumber")->AsString;
            AnsiString CutStr = ProductionADODataSet->FieldByName("CutCode")->AsString;
            AnsiString SpecieCodeStr = ProductionADODataSet->FieldByName("Specie")->AsString;
            int BFInt = ProductionADODataSet->FieldByName("BdFt")->AsInteger;
            int SFInt = ProductionADODataSet->FieldByName("SquareFeet")->AsInteger;
            double SMFlt = ProductionADODataSet->FieldByName("SquareMeters")->AsFloat;
            int LogCountInt = ProductionADODataSet->FieldByName("LogCount")->AsInteger;

            // Set up Market
            if (MarketStr != "X" && (CutStr == "X" || CutStr == "B")) MarketStr = "X";
            if (CCFlagStr == "1") MarketStr = "C";

            // Insert data into summary table
            AnsiString MsgStr = "Processing machine " + MachineStr + " . . . ";
            Application->ProcessMessages();

            ProductionSummaryADODataSet->Append(); // Insert and Append adds a new record while Update changes the current record
            ProcessStaticText->Caption = MsgStr;
            ProductionSummaryADODataSet->FieldByName("Machine")->AsString = MachineStr;
            ProductionSummaryADODataSet->FieldByName("MachineDate")->AsDateTime = MachineDate;
            ProductionSummaryADODataSet->FieldByName("Shift")->AsString = MachineShiftStr;
            ProductionSummaryADODataSet->FieldByName("ParcelNumber")->AsString = ParcelStr;
            ProductionSummaryADODataSet->FieldByName("SpecieCode")->AsString = SpecieCodeStr;
            ProductionSummaryADODataSet->FieldByName("CutCode")->AsString = CutStr;
            ProductionSummaryADODataSet->FieldByName("Market")->AsString = MarketStr;
            ProductionSummaryADODataSet->FieldByName("SquareMeters")->AsFloat = SMFlt;
            ProductionSummaryADODataSet->FieldByName("SquareFeet")->AsInteger = SFInt;
            ProductionSummaryADODataSet->FieldByName("LogCount")->AsInteger = LogCountInt;
            ProductionSummaryADODataSet->FieldByName("Hours")->AsString = "0";
            if (CutStr == "B")
            {
                ProductionSummaryADODataSet->FieldByName("Pounds")->AsInteger = BFInt;
               ProductionSummaryADODataSet->FieldByName("BdFt")->AsInteger = 0;
            }
            else
            {
                ProductionSummaryADODataSet->FieldByName("BdFt")->AsInteger = BFInt;
                ProductionSummaryADODataSet->FieldByName("Pounds")->AsInteger = 0;
            }
            ProductionSummaryADODataSet->Post(); // NEEDED!!!!!!!
            ProductionADODataSet->Next();

        }                           // Collect each record

        // Fini
        ProductionSummaryADODataSet->Open();
    }                               // We have records to process
    else Application->MessageBox("No production records found.", "0 Records", MB_OK);
}
catch ( ... )
{
    Application->MessageBox("Error running SQL command!", "Data Set Error", MB_OK);
}

// Done!
ProcessStaticText->Caption = "Done!";
ParcelListBox->Clear();
ProductionADODataSet->Close();

Clear as mud, now?

James P. Cottingham
-----------------------------------------
^{I'm number 1,229!
I'm number 1,229!}

 

[ShawnCoutts]

thanks a lot. I think I've got it now