?
Solved

ADOQuery Help

Posted on 2005-03-22
32
Medium Priority
?
1,562 Views
Last Modified: 2012-06-21
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
Comment
Question by:whythetorment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 11
  • 5
  • +2
32 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13599015
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
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13599031
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
 

Author Comment

by:whythetorment
ID: 13599064
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Expert Comment

by:mokule
ID: 13599108
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
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13599365
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
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13599371
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
 

Author Comment

by:whythetorment
ID: 13599613
i guess thats why its freezing....
0
 

Author Comment

by:whythetorment
ID: 13600473
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
 
LVL 17

Expert Comment

by:mokule
ID: 13600860
What is it 'yada' ?
Some kind of joke :) ?

mokule
0
 

Author Comment

by:whythetorment
ID: 13600871
ummm........ my sql....was not going to retype everything
0
 
LVL 17

Expert Comment

by:mokule
ID: 13600902
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
 
LVL 17

Expert Comment

by:mokule
ID: 13600922
Try this first then add some additional conditions

mokule
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13601104
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
 

Author Comment

by:whythetorment
ID: 13601529
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
 
LVL 17

Expert Comment

by:mokule
ID: 13601998
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
 
LVL 46

Expert Comment

by:aikimark
ID: 13602284
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
 

Author Comment

by:whythetorment
ID: 13603689
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
 
LVL 46

Expert Comment

by:aikimark
ID: 13603985
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
 

Author Comment

by:whythetorment
ID: 13604134
im using access
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13604179
<<im using access>>

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

18. The LIKE operator expects an asterisk (*) wildcard.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13604229
19. Where do you want to use wildcard matching and where do you want exact matching?
0
 

Author Comment

by:whythetorment
ID: 13604240
every field should be wildcard matching
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13604317
Even date and numeric fields?
0
 

Author Comment

by:whythetorment
ID: 13604362
yep...client wants it like that.  maybe date dont have to be
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13604584
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
 
LVL 46

Accepted Solution

by:
aikimark earned 500 total points
ID: 13604610
Maybe
sPhone := QuotedStr('*' + Trim(Edit12.text) + '*');
0
 
LVL 1

Expert Comment

by:RYasin
ID: 13609863
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
 

Author Comment

by:whythetorment
ID: 13611018
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
 
LVL 46

Expert Comment

by:aikimark
ID: 13611376
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
 

Author Comment

by:whythetorment
ID: 13611456
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
 
LVL 46

Expert Comment

by:aikimark
ID: 13611589
As I've already posted, the wildcard character for MSAccess is and asterisk (*), not a percent sign (%).
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13611839
<<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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question