whythetorment
asked on
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,
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,
:EditSearch27 = Edit5.text
:EditSearch28 = Edit4.text
:EditSearch29 = Edit10.text
:EditSearch30 = Edit9.text
:EditSearch31 = Edit10.text
:EditSearch32 = Edit9.text
Thanx
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,
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,
:EditSearch27 = Edit5.text
:EditSearch28 = Edit4.text
:EditSearch29 = Edit10.text
:EditSearch30 = Edit9.text
:EditSearch31 = Edit10.text
:EditSearch32 = Edit9.text
Thanx
Or you could do
SQL.Text :=
'Select Function.VenueNameA, Function.CoOrdinator1, Function.ContactFirstName,
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
SQL.Text :=
'Select Function.VenueNameA, Function.CoOrdinator1, Function.ContactFirstName,
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
ASKER
im not sure how set them up as string inputs.....ive used Parameters.ParamByName('Ed
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.
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.
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
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
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
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
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.
If your SQL is dynamic, then remove from ADOQuery object and build all SQL and Parameters at runtime.
ASKER
i guess thats why its freezing....
ASKER
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.
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.
What is it 'yada' ?
Some kind of joke :) ?
mokule
Some kind of joke :) ?
mokule
ASKER
ummm........ my sql....was not going to retype everything
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
sql.text := 'SELECT * FROM Booking, Function, Quote WHERE Booking.FunctionID = Function.FunctionID and Function.QuoteID = Quote.QuoteID';
mokule
Try this first then add some additional conditions
mokule
mokule
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
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)
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
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)
ASKER
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,
b := 'Function.Date, Function.FunctionType, DJ.FirstName,Function.Comp
c := 'Function.WorkNo,Function.
d := 'Booking.TotalBookingCost,
e := ' from Function, Customer, DJ, Invoice, Quote, Booking, Confirmation WHERE Invoice.ConfirmationID = Confirmation.ConfirmationI
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.ContactFirstNam
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
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
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.
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,
b := 'Function.Date, Function.FunctionType, DJ.FirstName,Function.Comp
c := 'Function.WorkNo,Function.
d := 'Booking.TotalBookingCost,
e := ' from Function, Customer, DJ, Invoice, Quote, Booking, Confirmation WHERE Invoice.ConfirmationID = Confirmation.ConfirmationI
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.ContactFirstNam
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
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
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.
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
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
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)';
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)';
ASKER
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.ConfirmationI
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+'
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+'
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.
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.
ASKER
im using access
<<im using access>>
17. All date literals should be pound sign (#) delimited.
18. The LIKE operator expects an asterisk (*) wildcard.
17. All date literals should be pound sign (#) delimited.
18. The LIKE operator expects an asterisk (*) wildcard.
19. Where do you want to use wildcard matching and where do you want exact matching?
ASKER
every field should be wildcard matching
Even date and numeric fields?
ASKER
yep...client wants it like that. maybe date dont have to be
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;
<<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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi ,
how did u define ur params ??
maybe there's something wrong in ur code
so could u write down over here that code ?
how did u define ur params ??
maybe there's something wrong in ur code
so could u write down over here that code ?
ASKER
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....
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?
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?
ASKER
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,
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,
e := 'FROM Customer, Quote, Function, Booking, Confirmation, Invoice, DJ';
j := 'WHERE Invoice.ConfirmationID = Confirmation.ConfirmationI
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.
i have
a := 'Select Function.VenueNamea, Function.CoOrdinator1, Function.ContactFirstName,
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,
e := 'FROM Customer, Quote, Function, Booking, Confirmation, Invoice, DJ';
j := 'WHERE Invoice.ConfirmationID = Confirmation.ConfirmationI
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.
As I've already posted, the wildcard character for MSAccess is and asterisk (*), not a percent sign (%).
<<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.
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.
You could always do
where (
(VenueNameA Like QuotedStr('%' + Edit2.text + '%')) OR
(Function.CoOrdinator1 Like QuotedStr('%' + Edit7.text + '%')) OR
... etc
)