Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1585
  • Last Modified:

ADOQuery Help

Hi

I have the following query to perform a search on my database.  But everytime i press the search button, my application freezes.  The search is to search for any field (listed) or exclude a field and perform the search on the values that was entered in the textboxes.  Then it would display in a DBGrid.
Im using Delphi 5 with an ADOQuery.  Ive also listed the parameters and what text boxes they are associated with.  Example the telno can be any telno in the function table or in the customer table.

Select Function.VenueNameA, Function.CoOrdinator1, Function.ContactFirstName, Customer.FirstName,
Function.ContactLastName, Customer.Surname, Function.Date, Function.FunctionType, DJ.FirstName,
Function.CompanyName, Function.TelA1, Function.TelA2, Function.TelA3, Function.CellNo, Function.WorkNo,
Function.FaxNo, Customer.Cell, Customer.Work, Customer.Home, Customer.Fax, Invoice.InvoiceNumberNo,
Quote.IDs, Booking.TotalBookingCost, Invoice.Outstanding
from Function, Customer, DJ, Invoice, Quote, Booking
where VenueNameA Like :Edit1
Or Function.CoOrdinator1 Like :EditSearch2
OR Customer.FirstName Like :EditSearch3
OR Function.ContactFirstName Like :EditSearch4
OR Customer.Surname Like :EditSearch5
OR Function.ContactLastName Like :EditSearch6
OR Function.Date Like :EditSearch7
OR Function.Date Between :EditSearch8 And :EditSearch9
OR Function.FunctionType Like :EditSearch10
OR Function.FunctionType Like :EditSearch11
OR Function.FunctionType Like :EditSearch12
OR Function.FunctionType Like :EditSearch13
OR Function.FunctionType Like :EditSearch14
OR DJ.FirstName Like :EditSearch15
OR Function.CompanyName Like :EditSearch16
OR Function.TelA1 Like :EditSearch17
OR Function.TelA2 Like :EditSearch18
OR Function.TelA3 Like :EditSearch19
OR Function.CellNo Like :EditSearch20
OR Function.WorkNo Like :EditSearch21
OR Function.FaxNo Like :EditSearch22
OR Customer.Cell Like :EditSearch23
OR Customer.Work Like :EditSearch24
OR Customer.Home Like :EditSearch25
OR Customer.Fax Like :EditSearch26
OR Invoice.InvoiceNumberNo Like :EditSearch27
OR Quote.IDs like :EditSearch28
OR Booking.TotalBookingCost Between :EditSearch29 AND :EditSearch30
OR Invoice.Outstanding Between :EditSearch31 AND :EditSearch32

Parameters
:Edit1 = Edit2.text
:EditSearch2 = Edit7.text
:EditSearch3 = Edit7.text
:EditSearch4 = Edit7.text
:EditSearch5 = Edit13.text
:EditSearch6 = Edit13.text
:EditSearch7 = Edit3.text
:EditSearch8 = Edit3.text
:EditSearch9 = Edit6.text
:EditSearch10 = Edit1.text
:EditSearch11 = Edit14.text
:EditSearch12 = Edit15.text
:EditSearch13 = Edit16.text
:EditSearch14 = Edit17.text
:EditSearch15 = Edit11.text
:EditSearch16 = Edit8.text
:EditSearch17,18,19,20,21,22,23,24,25,26 = Edit12.text
:EditSearch27 = Edit5.text
:EditSearch28 = Edit4.text
:EditSearch29 = Edit10.text
:EditSearch30 = Edit9.text
:EditSearch31 = Edit10.text
:EditSearch32 = Edit9.text

Thanx
0
whythetorment
Asked:
whythetorment
  • 11
  • 11
  • 5
  • +2
1 Solution
 
mikelittlewoodCommented:
Have you specifically set up your parameters to be string inputs?

You could always do
where (
(VenueNameA Like QuotedStr('%' + Edit2.text + '%')) OR
(Function.CoOrdinator1 Like QuotedStr('%' + Edit7.text + '%')) OR
... etc
)
0
 
mikelittlewoodCommented:
Or you could do

SQL.Text :=
'Select Function.VenueNameA, Function.CoOrdinator1, Function.ContactFirstName, Customer.FirstName,
Function.ContactLastName, Customer.Surname, Function.Date, Function.FunctionType, DJ.FirstName,
Function.CompanyName, Function.TelA1, Function.TelA2, Function.TelA3, Function.CellNo, Function.WorkNo,
Function.FaxNo, Customer.Cell, Customer.Work, Customer.Home, Customer.Fax, Invoice.InvoiceNumberNo,
Quote.IDs, Booking.TotalBookingCost, Invoice.Outstanding
from Function, Customer, DJ, Invoice, Quote, Booking
where (';

now loop through each of the parameters
if Trim( Edit2.text) <> '' then
  SQL.Text := SQL.Text + '(VenueNameA Like ' + QuotedStr('%' + Edit2.text + '%') + ') OR';
if Trim( Edit7.text) <> '' then
  SQL.Text := SQL.Text + '(Function.CoOrdinator1 Like ' + QuotedStr('%' + Edit7.text + '%') + ') OR';

.. etc

Repeat for each parameter

At the end remove the last OR as it will cause an error
Then place on the final bracket
0
 
whythetormentAuthor Commented:
im not sure how set them up as string inputs.....ive used Parameters.ParamByName('Edit1').value := Edit1.text ?

Just a related question.....do i need to specify the sql in the adoquery and in my onclick event of my button aswell?  coz it feels like im doing everything twice.

with my button ive used
with adoquery do begin
close;
  with sql do begin
  clear;
  Add('Select *.....');
  Param......
  end;
open;
end;

and in the sql of the adoquery ive just specified the sql again that ive used in the onclick.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mokuleCommented:
I can't see any joinings in Your query ??
What You are try to do is combining is combinig each record from each records from each tables.
This produces great great amount of records

mokule
0
 
mikelittlewoodCommented:
If you set up a specific query object to handle your search, and you enter all those parameters, you should go to the parameters section of the query and specifically set each of the parameters datatype to ftString. This way the query knows what type of data it is expecting

Mokule is also correct, you have not specified any links between the tables to limit your search records

If Function, Customer, DJ, Invoice, Quote, Booking all have 10 records ... your result set is going to be

10x10x10x10x10x10 = 1 Million records

You need to relook at what you are trying to get your application to do, maybe specific search areas depending on task
0
 
mikelittlewoodCommented:
If your SQL for the query is static and wont change, put it in your ADOQuery object.
If your SQL is dynamic, then remove from ADOQuery object and build all SQL and Parameters at runtime.
0
 
whythetormentAuthor Commented:
i guess thats why its freezing....
0
 
whythetormentAuthor Commented:
ok i have
with adosearch do begin
sql.text := 'yada yada yada'
end;

and ive removed the sql from the adoquery...and set the cursortype to dynamic....my datagrid to dsadosearch....but its not returning any values.  when i put close; and open; above and below the sql...then it says Invalid SQL statement expected : 'insert', 'delete', 'update', 'procedure'

also put in my linking between tables as Booking.FunctionID = Function.FunctionID and Function.QuoteID = Quote.QuoteID etc.
0
 
mokuleCommented:
What is it 'yada' ?
Some kind of joke :) ?

mokule
0
 
whythetormentAuthor Commented:
ummm........ my sql....was not going to retype everything
0
 
mokuleCommented:
I hope it is rather something like

sql.text := 'SELECT * FROM  Booking, Function, Quote WHERE Booking.FunctionID = Function.FunctionID and Function.QuoteID = Quote.QuoteID';

mokule
0
 
mokuleCommented:
Try this first then add some additional conditions

mokule
0
 
aikimarkCommented:
whythetorment,

1. Let's see your SQL now that you have added the table joins
2. Is the following what you have actually typed or is this a notational shortcut?
":EditSearch17,18,19,20,21,22,23,24,25,26 = Edit12.text"

3. Do you allow the user to use wildcards in the textbox values?
Example:

Edit5.text := '86*';  // or := '86%';

// allows retrieval of all InvoiceNumberNo fields starting with the characters '86'

4. The LIKE operator is primarily for text values.  Some of your lockup performance might be the conversion of non-string data for use with the LIKE operator.

5. You might be facing a problem with the order of your BETWEEN clause parameters.
Example:

OR Invoice.Outstanding Between :EditSearch31 AND :EditSearch32

:EditSearch31 = Edit10.text
:EditSearch32 = Edit9.text

If the data in Edit10 is less than the data in Edit9, you may encounter unanticipated results (if any, if it doesn't raise an error)
0
 
whythetormentAuthor Commented:
1.  
var
ADOQuery : TADOQuery;
dsDataSource : TDataSource;
a : string;
b : string;
c : string;
d : string;
e, f, g , h , i : string;
begin
        ADOQuery := TADOQuery.create(nil);
        ADOQuery.Connection := DataModule1.ADOConnection1;

        dsDataSource := TDataSource.create(nil);
        dsDataSource.DataSet := ADOQuery;
      
      ADOQuery.SQL.Clear;
        a := 'Select Function.VenueNameA, Function.CoOrdinator1, Function.ContactFirstName, Customer.FirstName,Function.ContactLastName, Customer.Surname,';
        b := 'Function.Date, Function.FunctionType, DJ.FirstName,Function.CompanyName, Function.TelA1, Function.TelA2, Function.TelA3, Function.CellNo,';
        c := 'Function.WorkNo,Function.FaxNo, Customer.Cell, Customer.Work, Customer.Home, Customer.Fax, Invoice.InvoiceNumberNo,Quote.IDs,';
        d := 'Booking.TotalBookingCost, Invoice.Outstanding';
        e := ' from Function, Customer, DJ, Invoice, Quote, Booking, Confirmation WHERE Invoice.ConfirmationID = Confirmation.ConfirmationID';
        f := ' AND Confirmation.BookingID = Booking.BookingID';
        g := ' AND Booking.FunctionID = Function.FunctionID';
        h := ' AND Function.QuoteID = Quote.QuoteID';
        i := ' AND Quote.CustomerID = Customer.CustomerID OR(';

       

        ADOQuery.SQL.Text := a + b + c + d + e + f + g + h + i;
                if Trim( Edit2.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(VenueNameA Like ' + QuotedStr('%' + Edit2.text + '%') + ') OR';
                if Trim( Edit7.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.CoOrdinator1 Like ' + QuotedStr('%' + Edit7.text + '%') + ') OR';
                if Trim( Edit7.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Customer.FirstName Like ' + QuotedStr('%' + Edit7.text + '%') + ') OR';
                if Trim( Edit7.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.ContactFirstName Like ' + QuotedStr('%' + Edit7.text + '%') + ') OR';
                if Trim( Edit13.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Customer.Surname Like ' + QuotedStr('%' + Edit13.text + '%') + ') OR';
                if Trim( Edit13.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.ContactLastName Like ' + QuotedStr('%' + Edit13.text + '%') + ') OR';
                if Trim( Edit3.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.Date Like ' + QuotedStr('%' + Edit3.text + '%') + ') OR';
                if Trim( Edit3.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.Date Between ' + QuotedStr('%' + Edit3.text + '%') +  'AND'  + QuotedStr('%' + Edit6.text + '%') + ' OR';
                if Trim( Edit1.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.FunctionType Like ' + QuotedStr('%' + Edit1.text + '%') + ') OR';
                if Trim( Edit14.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.FunctionType Like ' + QuotedStr('%' + Edit14.text + '%') + ') OR';
                if Trim( Edit15.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.FunctionType Like ' + QuotedStr('%' + Edit15.text + '%') + ') OR';
                if Trim( Edit16.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.FunctionType Like ' + QuotedStr('%' + Edit16.text + '%') + ') OR';
                if Trim( Edit17.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.FunctionType Like ' + QuotedStr('%' + Edit17.text + '%') + ') OR';
                if Trim( Edit11.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(DJ.FirstName Like ' + QuotedStr('%' + Edit11.text + '%') + ') OR';
                if Trim( Edit8.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.CompanyName Like ' + QuotedStr('%' + Edit8.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.TelA1 Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.TelA2 Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.TelA3 Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.CellNo Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.WorkNo Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.FaxNo Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Customer.Cell Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Customer.Work Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Customer.Home Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit12.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Customer.Fax Like ' + QuotedStr('%' + Edit12.text + '%') + ') OR';
                if Trim( Edit5.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Invoice.InvoiceNumberNo Like ' + QuotedStr('%' + Edit5.text + '%') + ') OR';
                if Trim( Edit4.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Quote.IDs Like ' + QuotedStr('%' + Edit4.text + '%') + ') OR';
                if Trim( Edit10.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Booking.TotalBookingCost Between ' + QuotedStr('%' + Edit10.text + '%') + ' AND ' + QuotedStr('%' + Edit9.text + '%') + ') OR';
                if Trim( Edit9.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Invoice.Outstanding Like ' + QuotedStr('%' + Edit10.text + '%') + ' AND ' + QuotedStr('%' + Edit9.text + '%') + '))';
 
                DBGrid1.DataSource := dsDataSource;

                ADOQuery.open;
end;

------------------------------
2.  no i just for short for me.....so i wont need to type out everything here

4.  Made it as follows (dont think size of the strings will matter)?
VenueNameA - String - 75
Function.CoOrdinator1 - String - 75
Customer.FirstName - String - 50
Function.ContactFirstName - String -50
Customer.Surname - String - 50
Function.ContactLastName - String - 50
Function.Date - Date
Function.FunctionType - String - 15
DJ.FirstName - String - 50
Function.CompanyName - String - 75
Function.TelA1, A2, A3 - String - 15
Function.CellNo - String - 25
Function.WorkNo - String - 25
Function.FaxNo - String -25
Customer.Cell - String - 25
Customer.Work - String - 25
Customer.Home - String - 25
Customer.Fax - String - 25
Invoice.InvoiceNumberNo - String - 5
Quote.IDs - String - 5
Booking.TotalBookingCost - double
Invoice.Outstanding - double

------------------------
5.  No it wont cause problem....Edit10 is Price1 and Edit9 is Price2......just how it was named.
0
 
mokuleCommented:
It's very hard to read and maintain.
As I stated earlier try to build simpler query and test if You achieve good results

Surely there is no join for DJ Table

seems that shouldn't be OR at the end
i := ' AND Quote.CustomerID = Customer.CustomerID OR(';

if Edit9 will be empty the query won't have closing brackets.

I think there are also more errors there

mokule

0
 
aikimarkCommented:
6. What about the [DJ] table-joining constraint?

7. Depending on the database, you need different delimiters around your non-numeric literals.  For instance, "%" is not a date literal delimiter.  So the following SQL building statement would be invalid:

if Trim( Edit3.text) <> '' then
  ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.Date Between ' + QuotedStr('%' + Edit3.text + '%') +  'AND'  + QuotedStr('%' + Edit6.text + '%') + ' OR';

8. In #7, your resulting SQL string is missing some required space between keywords.  Your 'AND' needs to be ' AND '

9. You should check for valid date values.  
Here is a function from http://www.swissdelphicenter.ch/torry/showcode.php?id=637 that may prove helpful:

function IsDate(str: string): Boolean;
var
  dt: TDateTime;
begin
  Result := True;
  try
    dt := StrToDate(str);
  except
    Result := False;
  end;
end;

MSAccess example:
if IsDate(Trim(Edit3.text)) And IsDate(Trim(Edit6.text)) then
  ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.Date Between ' + '#' + Trim(Edit3.text) + '#' +  ' AND '  + '#' + Trim(Edit6.text) + '#') + ' OR';

10. Your closing parenthesis is part of a conditional statement and may not occu when it needs to occur.  You might try
ADOQuery.SQL.Text := ADOQuery.SQL.Text + ' 0=1)';
0
 
whythetormentAuthor Commented:
ok did all that....now when i run it....and lets say i leave all the fields blank but type something in venuename, it gives me a syntax error Invoice.ConfirmationID = Confirmation.ConfirmationID AND Confirmation.BookingID = Booking.BookingID AND Booking.FunctionID = Function.FunctionID AND Function.QuoteID = Quote.QuoteID AND Quote.CustomerID = Customer.CustomerID OR Function.VenueNamea Like'

it happens with every field.  if i leave the trim(Edit2.text) <> '' out, it still does that.  if i dont fill in any fields....then it gives me some records.

ive made it ADOQuery.SQL.Text := ADOQuery.SQL.Text + ' OR (Function.VenueNamea Like ' + QuotedStr('%'+Edit2.text+'%')+')';
0
 
aikimarkCommented:
11. What database are you using?


12. If I've done this correctly, then your a-through-i string concatenation would end with
' AND Quote.CustomerID = Customer.CustomerID OR('

and you have some text ('Aiki Home') in Edit2, the following statement:
if Trim( Edit2.text) <> '' then
                ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(VenueNameA Like ' + QuotedStr('%' + Edit2.text + '%') + ') OR';

Should make your SQL string end with:
' AND Quote.CustomerID = Customer.CustomerID OR((VenueNameA Like "%Aiki Home%") OR'

It would probably help us to see the SQL string after your code has built it.

<<if i leave the trim(Edit2.text) <> '' >>
13. Why would you leave this out?

14. Just to be sure you have the first part of the SQL correct, submit a version of the SQL with no WHERE clause.

15. You could greatly simplify your SQL (unless you want to use wildcards) by doing something like the following:

if Trim( Edit12.text) <> '' then
Begin
   ADOQuery.SQL.Text := ADOQuery.SQL.Text + '( ' + QuotedStr(Edit12.text) + ' IN (Function.TelA1 , Function.TelA2 , Function.TelA3 , Function.CellNo , Function.WorkNo , Function.FaxNo , Customer.Cell , Customer.Work , Customer.Home , Customer.Fax ) OR ';

End;

16. The ' OR' connectors should also have a trailing space, similar to my earlier comment on the lack of 'AND' space separators.
0
 
whythetormentAuthor Commented:
im using access
0
 
aikimarkCommented:
<<im using access>>

17. All date literals should be pound sign (#) delimited.

18. The LIKE operator expects an asterisk (*) wildcard.
0
 
aikimarkCommented:
19. Where do you want to use wildcard matching and where do you want exact matching?
0
 
whythetormentAuthor Commented:
every field should be wildcard matching
0
 
aikimarkCommented:
Even date and numeric fields?
0
 
whythetormentAuthor Commented:
yep...client wants it like that.  maybe date dont have to be
0
 
aikimarkCommented:
20. please show me an example of where a user would want to do a wildcard search on your two numeric fields.

<<maybe date dont have to be>>
Especially since we are validating the date entered into the textbox as a valid date.

15 (redux)
var
   sPhone : String;

if Trim( Edit12.text) <> '' then
Begin
   sPhone := '''*'' + Trim(Edit12.text) + ''*'''
   ADOQuery.SQL.Text := ADOQuery.SQL.Text + '( Function.TelA1 Like ' + sPhone + ' OR Function.TelA2 Like ' + sPhone + ' OR Function.TelA3 Like ' + sPhone + ' OR Function.CellNo Like ' + sPhone + ' OR Function.WorkNo Like ' + sPhone + ' OR Function.FaxNo Like ' + sPhone + ' OR Customer.Cell Like ' + sPhone + ' OR Customer.Work Like ' + sPhone + ' OR Customer.Home Like ' + sPhone + ' OR  Customer.Fax Like ' + sPhone + ' ) OR ';

End;
0
 
aikimarkCommented:
Maybe
sPhone := QuotedStr('*' + Trim(Edit12.text) + '*');
0
 
RYasinCommented:
hi ,
how did u define ur params ??
maybe there's something wrong in ur code
so could u write down over here that code ?
 
0
 
whythetormentAuthor Commented:
its working fine now....but ive deleted all my data in my tables....and only left about 5 records for each table.....and i previously had about 100 records in each table.....will my program freeze up when my data gets more and more in all my tables?  because my client is going to add about 10 - 20 records each day....
0
 
aikimarkCommented:
21. Why did you delete your table data?  

22. Did the query run successfully with 100 records in your tables before you deleted the data?

23. What does your SQL-building code look like now?
0
 
whythetormentAuthor Commented:
it was just sample data.....stuff i played around with to test it.  no it froze when i had that much data in it.  think that is what caused it in first place.  

i have

a := 'Select Function.VenueNamea, Function.CoOrdinator1, Function.ContactFirstName, Customer.FirstName, Function.ContactLastName, Customer.Surname,';
        b := 'Function.FunctionDate, Function.FunctionType, DJ.FirstName, Function.CompanyName, Function.TelA1, Function.TelA2, Function.TelA3, Function.CellNo,';
        c := 'Function.WorkNo, Function.FaxNo, Customer.Cell, Customer.Work, Customer.Home, Customer.Fax, Invoice.InvoiceNumberNo, Quote.IDs,';
        d := 'Booking.TotalBookingCost, Invoice.Outstanding, Quote.QuoteAccepted, Quote.DateSent, Quote.QuoteDate';
        e := 'FROM Customer, Quote, Function, Booking, Confirmation, Invoice, DJ';
        j := 'WHERE Invoice.ConfirmationID = Confirmation.ConfirmationID';
        f := 'AND Confirmation.BookingID = Booking.BookingID';
        g := 'AND Booking.FunctionID = Function.FunctionID';
        h := 'AND Function.QuoteID = Quote.QuoteID AND Function.DJID = DJ.DJID';
        i := 'AND Quote.CustomerID = Customer.CustomerID OR(';

        ADOQuery.SQL.Clear;

        ADOQuery.SQL.Text := a + ' ' + b + ' ' + c + ' ' + d + ' ' + e + ' ' + j + ' ' + f + ' ' + g + ' ' + h + ' ' + i;

        if Trim(Edit2.text) <> '' then
        ADOQuery.SQL.Text := ADOQuery.SQL.Text + '(Function.VenueNamea Like ' + QuotedStr('%' + Trim(Edit2.text) + '%') + ') OR ';

*all my other or's and conditions, not going to list everything again*

DBGrid1.DataSource := dsDataSource;

        ADOQuery.open;

end;

ive set my indexname on my tables in my datamodule to the primary key just now....dont know if that will help.
0
 
aikimarkCommented:
As I've already posted, the wildcard character for MSAccess is and asterisk (*), not a percent sign (%).
0
 
aikimarkCommented:
<<no it froze when i had that much data in it.  think that is what caused it in first place.>>

I disagree with your conclusion.  If the tables had appropriate data in the joining columns (Where clause pieces j,f,g,h,i) there is no reason for 100 rows in each of these tables to cause MSAccess to even slow down, much less freeze.  For that matter, MSAccess should handle 100 times that number of rows in the tables with no problem.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 11
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now