Page 3 of 3 FirstFirst 123
Results 21 to 27 of 27
  1. #21
    Join Date
    Dec 2007
    Posts
    25
    Hi DaveK,

    Thanks for the attempt to point me in the right direction.

    Although I have now managed to link to an XL sheet and access the data, there appears to be something wierd about the connection being open, I have to move the record pointer and then open the link again in order to be able to read anything and the process of reopening seems to slow the whole process down, reading 1000 records and writing back the soundex took ca 20 mins.

    As for the 'Briefcase' demo, my copy of Turbo Delphi Pro must be different because I can follow your path all the way to VCLWin32, but then no ADO and no Briefcase in the whole of the demos area.

    It's just so frustrating, but thanks anyway for trying and for your time in replying.

    Derek

  2. #22
    Join Date
    Nov 2006
    Location
    UK
    Posts
    3,774
    then at some point you must be inadvertently shutting the connection.
    Delphi !ROCKS!
    Got a question? Read this first!!!
    "You gotta help us, Doc. We've tried nothin' and we're all out of ideas"

  3. #23
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Quote Originally Posted by DerekSmith View Post
    Although I have now managed to link to an XL sheet and access the data, there appears to be something wierd about the connection being open, I have to move the record pointer and then open the link again in order to be able to read anything and the process of reopening seems to slow the whole process down, reading 1000 records and writing back the soundex took ca 20 mins.
    Hmmmm, that's not right. Unfortunately, I've never actually used XL as a data source. I'm certainly willing to take a crack at it if you'd like the help - we could take it to email or continue to bash it about here.

    Quote Originally Posted by DerekSmith View Post
    As for the 'Briefcase' demo, my copy of Turbo Delphi Pro must be different because I can follow your path all the way to VCLWin32, but then no ADO and no Briefcase in the whole of the demos area.
    Crap, I don't have Turbo installed on this machine ... here's the relevant code:
    Code:
    procedure TForm1.ConnectionIndClick(Sender: TObject);
    // ConnectionInd is a check-box on the form
    // Connection is a TADOConnection
    // Employees is a TADODataset
    begin
      { Toggle the connection's state }
      if ConnectionInd.Checked then   //make the connection
      begin
        Connection.Open;
        Employees.Connection := Connection;
      end else   //break the connection                        
      begin
        { Note here you must clear the connection property of the dataset before 
          closing the connection.  Otherwise the dataset will close with the
          connection. }
        Employees.Connection := nil;
        Connection.Close;
      end;
    end;
    Quote Originally Posted by DerekSmith View Post
    It's just so frustrating, ...
    Yea, it sure can be ... let's keep kicking it around until you get over the hump
    [don't feel obliged though - totally your call]

    --
    Dave

  4. #24
    Join Date
    Dec 2007
    Posts
    25
    Hi Dave,

    Yes please, If you don't mind giving this some you your time, I would appreciate it.

    I have spent some time trying to resolve this for myself but have failed.

    Having used the code put up by Zarco, I have added in the following bit to read in the names to a small array. It works very fast provide I have run the SQL fist.

    while not ADOquery1.EOF do begin
    cnt:= cnt + 1;
    NameArray[cnt]:= adoquery1.FieldByName('Surname').AsString;
    ADOQuery1.Next;
    end;

    However, if I then reset the pointer to the front of the data in XL with

    adoquery1.FindFirst ;

    I then have to do

    adoquery1.Edit;

    Otherwise I get the error that I cannot perform the action on a closed database. Having opened it for edit, I run the following loop to calculate the soundex function and write it into the column called 'Soundex' in the XL spreadsheet.

    for c := 1 to cnt do
    begin
    adoquery1.FieldByName('Soundex').AsString := soundex(NameArray[c]);
    ADOQuery1.Next;
    adoquery1.Edit;
    end;

    You will notice that every iteration I have to call adoquery1.Edit;

    If I leave out this call I get the closed database error. With the call, the routine works but it is very slow. I cut down the number of records in the sheet. Read takes ca 0.4 sec and the write back takes ca 45 seconds.

    Thoughts much appreciated and if you would prefer, by all means email me on derek at execsec dot co dot uk

    Derek
    Still struggling.

  5. #25
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Quote Originally Posted by Derek
    However, if I then reset the pointer to the front of the data in XL with

    adoquery1.FindFirst ;
    Findfirst isn't the right method to use to do this, you want to use First [Next Prior or Last]. That may be part of the problem you're seeing. I'll have a good read of your post and see if there's other things I can pick out/suggestions to make.

    FindFirst is one of four dataset methods that navigate among records in a filtered dataset. It moves to the first record that matches the current filter criteria.
    [ripped from the help file - I only knew the VB version which is different again]
    Last edited by TDelphiHobbyist; 03-09-2008 at 03:52 PM.

  6. #26
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Derek,

    Another bit ... I'm not getting the whole Soundex issue. You have all the data loaded from the XL sheet in memory.

    If the name is entered in an Edit control why not replace it all with:
    Code:
    adoquery1.First;
    repeat
      if adoquery1.FieldByName('Surname').AsString = Edit1.Text then
      begin
        ShowMessage('I Found that person');
        exit;
      end else
        ShowMessage('Person not Found');
      ADOQuery1.Next;
    until adoquery1.EOF;
    The name has to be typed in exactly the same as what's in the data in this case but it's a pretty good example of what could be done.

    Even better would be to only load the data you need using a query - you are already using a ADOQuery control, let's make use of that. I've got to go off and work up an example [not having ever used XL as a data source] so I'll be back. I'll use Zarco's work as you [and I] already have that. The plan is, using the example from Zarco, to only load select records.

    ... stand by one

  7. #27
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Derek,

    Base on what I think it is you are trying to do I've got a simpler solution using Zarco's example for you to play with.

    Copy one of your XL spereadsheets to the same folder you have Zarco's program installed in.

    Open the program in Delphi and let's automate it a bit more than it is.

    Open the main form and double-click on the Sheets ComboBox [called ComboBox1 and located under the label Sheets:]. Delphi will automatically create a ComboBox1Change event and switch you to the code window. Paste the following code in between the Begin and End:
    Code:
    Edit2.Text := 'SELECT * FROM [' + ComboBox1.Text + ']';
    FetchData;
    Now find the ConnectToExcel procedure and after line
    AdoConnection1.GetTableNames(ComboBox1.Items,True) ;
    paste the following:
    Code:
    if ComboBox1.Items.Count > 0 then ComboBox1.ItemIndex := 0;
    You can skip this next change if you want and enter the different file name at run-time.
    Switch back to the Designer [F12] and click on the Edit control [Edit1] under the label Workbook:
    In the object inspector [bottom left in the Default Layout] select the Text property [should have a path in it that Zarco used]. Delete what's there and type in the name of your XL file including the .xls extension.

    Run the program. It you should be able to select the various sheets in your XL workbook. Just open the combobox under the Sheets label and select one. IF the first row in your sheets is field names/column titles it should list all the fields correctly. Otherwise we have to get into range selection and although [I looked it up on the tutorial] I know to do it, it doesn't look like fun

    Now, I'm finally getting to where I want to go here ... let's alter the existing program again.

    Go to the form view, select the Workbook Edit box where you put the file name and make it half the existing width. Drop a new Edit control from the tool palette right beside the one you just shortened. In the Object inspector delete the Text property value [should be found as Edit3] - it has to be blank. Drop a label control just above the new edit control. Select the label and in the Object inspector change the caption property to 'Surname Search:'.

    Switch to the code window [F12], find the FetchData procedure and after the line
    AdoQuery1.SQL.Text:= Edit2.Text;
    add
    Code:
    if Edit3.Text > '' then
      AdoQuery1.SQL.Text := AdoQuery1.SQL.Text + ' ' + 'WHERE Surname="' + Edit3.Text + '"';
    Run the program using the correct XL file, select a valid sheet, type a known Surname [using correct case] into the new edit control and click on the Re-Fetch button.

    See where I'm going here ... not a Mutex, Array or write to XL sheet in sight. All we did is add a WHERE clause to the SQL query to find what you wanted.

    Another thing, we can apply the query criterion [WHERE] before you even open the XL file for the first time so that it only returns the info that you want. SELECT statements are very powerful you should know them well. This is still the basics yet, the query can get more complicated depending on the number of fields you want to select by - we can even specify which columns of data are returned.

    Hope this helps.

Page 3 of 3 FirstFirst 123

Posting Permissions

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