Using ADO: two Excel files and two ADOConnections Simultaneously

Windows 7, Delphi XE2.

I am using ADO to access two separate excel spreadsheets.

They have identical structure.

Thus, I have set up two ADOConnections, two ADOQueries, and two TDatasets and connected them all together in a data module.

I also have two dbgrids on the main form, one for each spreadsheet.

I have prelinked the ADOConnections to the ADOQueries and the datasets and grids.

The settings for each connection chain are connected identically except for the files pointed to.

The object is to display each spreadsheet in a grid.

OK, so I am making the connections dynamically, setting the connection strings and opening the ADOConnections and then the ADOQueries.

SQL string in each ADOQuery is "select * from [$sheet1]"

Code for each open operation looks like the following:

****************************************************************************

procedure TfrmMain.btnLatterPlannerClick(Sender: TObject);
begin
  OpenDialogPlanner.Execute();
  if OpenDialogPlanner.FileName <> '' then
  begin
    DataModule1.ADOConLatterPlanner.Connected:=False;
    DataModule1.ADOQryLatterPlanner.Close;
    edLatterPlanner.Text:=Trim(OpenDialogPlanner.FileName);
    DataModule1.ADOConLatterPlanner.ConnectionString :=
    'Provider=Microsoft.Jet.OLEDB.4.0;' +
    'Data Source=' +
    edLatterPlanner.Text + ';' +
    'Mode=ReadWrite;' +
    'Extended Properties=Excel 8.0;' +
    'Persist Security Info=False';
    DataModule1.ADOConLatterPlanner.Connected:=True;
    DataModule1.ADOQryLatterPlanner.Open;

  end;
end;

procedure TfrmMain.btnPriorPlannerClick(Sender: TObject);
begin
  OpenDialogPlanner.Execute();
  if OpenDialogPlanner.FileName <> '' then
  begin
    DataModule1.ADOConPriorPlanner.Connected:=False;
    DataModule1.ADOQryPriorPlanner.Close;
    edPriorPlanner.Text:=Trim(OpenDialogPlanner.FileName);
    DataModule1.ADOConPriorPlanner.ConnectionString :=
    'Provider=Microsoft.Jet.OLEDB.4.0;' +
    'Data Source=' +
    edPriorPlanner.Text + ';' +
    'Mode=ReadWrite;' +
    'Extended Properties=Excel 8.0;' +
    'Persist Security Info=False';
    DataModule1.ADOConPriorPlanner.Connected:=True;
    DataModule1.ADOQryPriorPlanner.Open;

  end;
end;

*************************************************************************************

Here's the problem.

* If I use btnLatterPlannerClick the code executes with no issues but no data appears in grid 2.
* If I use btnPriorPlannerClick the code executes with no issues and data appears in grid 1
* If I use btnLatterPlannerClick after having used btnPriorPlannerClick the data in grid1 appears in grid 2
* If I then use btnPriorPlannerClick new data loads into grid 1 but grid 2 retains original data

I have verified that the grids are linked to the correct datasets and the connection-Query-Dataset-grid links are correct for each of the two grids.


I also tried eliminating the ADOConnections and using direct table connections. I got analagous results.

Please, does anybody have any idea what is going on here and how to fix it?

Thanks,
Mark
0
Mark
1/20/2012 7:38:27 PM
embarcadero.delphi.ole 598 articles. 1 followers. Follow

1 Replies
723 Views

Similar Articles

[PageSpeed] 10

Please disregard; I made a booboo.

Thanks,
Mark


> {quote:title=Mark Skeels wrote:}{quote}
> Windows 7, Delphi XE2.
> 
> I am using ADO to access two separate excel spreadsheets.
> 
> They have identical structure.
> 
> Thus, I have set up two ADOConnections, two ADOQueries, and two TDatasets and connected them all together in a data module.
> 
> I also have two dbgrids on the main form, one for each spreadsheet.
> 
> I have prelinked the ADOConnections to the ADOQueries and the datasets and grids.
> 
> The settings for each connection chain are connected identically except for the files pointed to.
> 
> The object is to display each spreadsheet in a grid.
> 
> OK, so I am making the connections dynamically, setting the connection strings and opening the ADOConnections and then the ADOQueries.
> 
> SQL string in each ADOQuery is "select * from [$sheet1]"
> 
> Code for each open operation looks like the following:
> 
> ****************************************************************************
> 
> procedure TfrmMain.btnLatterPlannerClick(Sender: TObject);
> begin
>   OpenDialogPlanner.Execute();
>   if OpenDialogPlanner.FileName <> '' then
>   begin
>     DataModule1.ADOConLatterPlanner.Connected:=False;
>     DataModule1.ADOQryLatterPlanner.Close;
>     edLatterPlanner.Text:=Trim(OpenDialogPlanner.FileName);
>     DataModule1.ADOConLatterPlanner.ConnectionString :=
>     'Provider=Microsoft.Jet.OLEDB.4.0;' +
>     'Data Source=' +
>     edLatterPlanner.Text + ';' +
>     'Mode=ReadWrite;' +
>     'Extended Properties=Excel 8.0;' +
>     'Persist Security Info=False';
>     DataModule1.ADOConLatterPlanner.Connected:=True;
>     DataModule1.ADOQryLatterPlanner.Open;
> 
>   end;
> end;
> 
> procedure TfrmMain.btnPriorPlannerClick(Sender: TObject);
> begin
>   OpenDialogPlanner.Execute();
>   if OpenDialogPlanner.FileName <> '' then
>   begin
>     DataModule1.ADOConPriorPlanner.Connected:=False;
>     DataModule1.ADOQryPriorPlanner.Close;
>     edPriorPlanner.Text:=Trim(OpenDialogPlanner.FileName);
>     DataModule1.ADOConPriorPlanner.ConnectionString :=
>     'Provider=Microsoft.Jet.OLEDB.4.0;' +
>     'Data Source=' +
>     edPriorPlanner.Text + ';' +
>     'Mode=ReadWrite;' +
>     'Extended Properties=Excel 8.0;' +
>     'Persist Security Info=False';
>     DataModule1.ADOConPriorPlanner.Connected:=True;
>     DataModule1.ADOQryPriorPlanner.Open;
> 
>   end;
> end;
> 
> *************************************************************************************
> 
> Here's the problem.
> 
> * If I use btnLatterPlannerClick the code executes with no issues but no data appears in grid 2.
> * If I use btnPriorPlannerClick the code executes with no issues and data appears in grid 1
> * If I use btnLatterPlannerClick after having used btnPriorPlannerClick the data in grid1 appears in grid 2
> * If I then use btnPriorPlannerClick new data loads into grid 1 but grid 2 retains original data
> 
> I have verified that the grids are linked to the correct datasets and the connection-Query-Dataset-grid links are correct for each of the two grids.
> 
> 
> I also tried eliminating the ADOConnections and using direct table connections. I got analagous results.
> 
> Please, does anybody have any idea what is going on here and how to fix it?
> 
> Thanks,
> Mark
0
Mark
1/20/2012 10:26:15 PM
Reply:

Similar Artilces:

testing two three two three two two
Test ...

Loading the two datatables from a dataset in to two different excel sheets using OleDbAdapter
Hi,   I have a problem now, I just created two excel sheets and I loading the data to a datatable in a dataset. If the rows are greater than 65536, then I load the remaining in the second datatable of a dataset. But while calling the update method of OledbAdapter after the first sheet get filled it throws error as "Spreadsheet is full". OledbAdapter.Update(dataset) or even I tried OledbAdapter.Update(datatable). Since i m not using insert query to insert in to the datatable. I have a dataset which I got from someother data and using that dataset i want to fill another ...

How to merge two cell , and how to use fillcolor in Excel through OLE ?
Hello All, How to merge two cell , and how to use fillcolor in Excel through OLE ? I am opening an excel file through OLE, I want to fill the color in a cell so I am using below function ole_object.Range('B1:B3').Select ole_object.Selection.FillColor = 0 ole_object.Selection.FillColor(0) in both thing is is not working , please tell me the function for FillColor, How to Merge the two cell please tell me ? How can I do this ? Do replay Urgent ? Hi TOM, This should be the answer you're looking for. ex. for fillcolor: &q...

how do i link two different sitemap with the use of menu control in one .aspx file to two different hyperlink in the master page?
I have two menu control with similar file(eg.defect.aspx) in the master page, whenever i click the similar file(eg.defect.aspx) in either menu control, it will show both the menu control(eg.student menu and staff menu), but i only want to show only either one of the menu (eg,student menu only). Is there any solution to my problem? Am i able to use similar file(eg.defect.aspx) for two different sitemap and two menu control and only show one menu control. Hey, What should or should not be visible; the whole menu, or the selected field?  Are you h...

Using two Contentplaceholders and two Updatepanels
Hey all, I have created a masterpage with two contentplaceholders and updatepanels. In a derived page I want to update one of these updatepanels if a dropdownlist located in the other updatepanel changes it's values. But if I add a trigger with referenceing a control which is sitting in the other contentplaceholder I get the exception  The ControlID property of the trigger must reference a valid control It works fine, if both updatepanels are in the same contentplaceholder. Any idea how to access a control for a trigger which is located in another contentplaceholder?  ...

Two aspx files that use one codebehind file. Is that possible?
Hi,I try to deploy my new webproject. Unfortunately I allways get the aspnet_merge error duplicate types.This error is based on my structure I think. There are two pages using one codebehindfile.Is it possible to handle that? Thanks in advanceBest regardsradi5P.S. Thats my structure-codebehind | |-App_data |  | |  |-CodeTest.aspx.vb | |-TestSeite1.aspx |-TestSeite2.aspx |-web.config Hi again,is my question to stupid, to easy, to hard? Or is it just my bad english that prevents help?Any reply is welcome ;-)Best regardsradi5 Hi Forum,I ...

using two arrays for two different fields
I'm trying to extract all the dates from a db table and load them in an array(seems the most logical).  However I keep getting the following error: Index was outside the bounds of the array. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IndexOutOfRangeException: Index was outside the bounds of the array.Source Error: Line 130: For Each row In DsVacationData1.RequestDataManager Line 131: ...

Using two graphics cards and two monitors?
Is it possible to use two graphics cards each with its own monitor? I have spent considerable effort trying to make this work with a Intel 865 / Vizio 37 monitor as well as a ATI Rage XL / 19" VESA 1280 x 1024 without success. I can use each monitor/card combination individually, by setting my machine to boot from that card, but cannot get Suse 11.1 to recognize both simultaneously (even though the "Hardware Information" from Yast recognizes both graphics cards). Incidentally, Windows XP recognizes and uses both cards and monitors simultaneously. sax2 -r only re...

Two menus and two different sitemap files
ok....heres my problem. I have two menu's ...each using its own sitemap file.<siteMap defaultProvider="MainMapProvider" enabled="true"> <providers> <clear /> <!-- Get rid of the inherited standard provider --> <add name="MainMapProvider" description="Main menu map" type="System.Web.XmlSiteMapProvider" siteMapFile="Main.sitemap" securityTrimmingEnabled="true" /> <add name="FooterMapProvider" description="Footer menu map" type="System.Web.XmlSiteMapProvider" siteMapFile="Footer.sitemap" securityTrimmingEnabled="true" /...

Linking two controls in two seperate .ascx files
hello,im trying to make a couple of image buttons in one usercontrol affect the ActiveViewIndex state of my multiview in another control so far both codebehinds look like this...1: this is where the multiview is located, the MV is called contentsusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;    public partial class UserControls_Content : System.Web.UI.UserControl  ...

Trying to read two files using a sub to get the data, but 2nd read on the 2nd file goes to EOF
------_=_NextPart_001_01C73F21.48FF8233 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Here is a snippet of the code: my $MyFileHand; =20 my $MyFileHand1; =20 =20 open($MyFileHand,"<$MyFileIn") || diet (3, $MyFileIn, $!); open($MyFileHand1,"<$MyFileIn1") || diet (3, $MyFileIn1, $!); =20 ............ =20 proc_getrcd( $MyFileHand , $MyEOFProd, $MyWorkp, $MyInp , $GlblInfo{compcnt}, $MyUnpackSw ); proc_getrcd( $MyFileHand1, $MyEOFTest, $MyWorkt, $MyInt , $GlblInf...

How to create and load data in to an excel worksheet of an existing excel file using ASP.NET, ADO.NET, and Visual C# .NET
Hi, I am using ASP.net with C# 1.1 version. Currently I am generating an report using an excel file. I created and loaded data in to an file using ASP.NET, ADO.NET, and Visual C# .NET. But when the rows of a dataset having count which is greater than 65536 I got error as "Spreadsheet is full". So I want to dynamically create an excel worksheet for the existing excel file and load data in to it using ASP.NET, ADO.NET, and Visual C# .NET. Suppose if the rows are 80000 and I want a code to create an excel worksheet for the excel file with 65536...

how can i take two sitemapdatasource controls for two sitemap files
Hi, I have 2  web.sitemap files, for 2 diffrent languages, when user login with 1 st langauge mode, i want to display that language menus and  user login with 2  nd langauge mode, i want to display that language menus here i am displaying menus in Master page, -----Her can i take two sitemapdatasource controls for two sitemap files if then how i assign  sitemap files to two diffrent sitemapdatasource controlsadilahmed -----Her can i take two sitemapdatasource controls for two sitemap files or can i take two web.sitemap files   and bind  wi...

Comapre two fileds using javascript
Hi, I have to textbox fields one is for email and second one is for confirmation email, using Javascript I want to check whether the value in both textboxes are equal or not I write the function like thie below <script language="javascript"> <!-- function checkEmail() { if (document.TheForm.Email.Value != document.TheForm.EmailVerify.Value) { return false; } } //--> </script>   Now I want to do the above validation in the emailverify textbox is loosing the focus and display the message after that textbox if they didn't match. Don't want the ale...

Web resources about - Using ADO: two Excel files and two ADOConnections Simultaneously - embarcadero.delphi.ole

var Item1 : TTreeViewItem; Item2 : TTreeViewItem; begin Item1 := TTreeView - Pastebin.com
var Item1 : TTreeViewItem; Item2 : TTreeViewItem;begin Item1 := TTreeViewItem.Create(Self); Item1.Text := 'My First Node'; Item1.Parent := TreeView1; ...

bitweaver - White Screen of Death
Click here to log a bug , if this appears to be an error with the application. Go here to begin the installation process , if you haven't done ...

Resources last updated: 12/5/2015 2:04:26 PM