Solved

SQL Query issue

Posted on 2013-06-06
13
406 Views
Last Modified: 2013-06-08
Goal
Show All invoice records that belong to the matching Customer
Im stuck on some code.
procedure TForm1.lvCustomersItemClick(const Sender: TObject;
   const AItem: TListViewItem);
begin
   TabControl1.ActiveTab:= tiInvoice;
   InvoiceQuery.Active:= True;
   while not  InvoiceQuery.Eof do
  begin
    InvoiceQuery.SQL.Text:=('Select Customers.Name,Customers.ID,Invoice.CusID From Customers Inner Join Invoice On Invoice.CusID = Customers.ID Where Customers.ID = :lvcusDetail');
    InvoiceQuery.ParamByName('lvcusDetail').asinteger := strToInt(lvCustomers.Items.Item[lvCustomers.ItemIndex].Detail);
    InvoiceQuery.ExecSQL();
    InvoiceQuery.Open;
    InvoiceQuery.Next;
  end;
end;

Open in new window

With the code above I can click on any customer name and when it shows the next listview it has the same customer no matter which custmer I choose from the lvCustomers.
CusCLickWronge

------------------------------------------------------------------------------------------------------------------------------------------------
If I take out the EOF code then  it will show the correct Invoices that matches the customers ID, BUT I throws and error on the last record I click on.
   InvoiceQuery.Active:= True;
  // while not  InvoiceQuery.Eof do
 //begin
    InvoiceQuery.SQL.Text:=('Select Customers.Name,Customers.ID,Invoice.CusID From Customers Inner Join Invoice On Invoice.CusID = Customers.ID Where Customers.ID = :lvcusDetail');
    InvoiceQuery.ParamByName('lvcusDetail').asinteger := strToInt(lvCustomers.Items.Item[lvCustomers.ItemIndex].Detail);
    InvoiceQuery.ExecSQL();
    InvoiceQuery.Open;

 // end;
  InvoiceQuery.Next;
  TabControl1.ActiveTab:= tiInvoice;

Open in new window

Last
Thanks for your help.
CusClick.jpg
Wrong-Record.jpg
last.jpg
0
Comment
Question by:Grant1842
[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
  • 8
  • 2
  • 2
  • +1
13 Comments
 
LVL 6

Assisted Solution

by:House_of_Dexter
House_of_Dexter earned 240 total points
ID: 39228047
First...why are we rewriting the sql each iteration?

  InvoiceQuery.Active:= True;//<--set this to false...
  // while not  InvoiceQuery.Eof do
 //begin
    InvoiceQuery.SQL.Text:=('Select Customers.Name,Customers.ID,Invoice.CusID From Customers Inner Join Invoice On Invoice.CusID = Customers.ID Where Customers.ID = :lvcusDetail');//<--set this on formcreate...
    InvoiceQuery.ParamByName('lvcusDetail').asinteger := strToInt(lvCustomers.Items.Item[lvCustomers.ItemIndex].Detail);//<-- you always want to check ItemIndex before passing it to Item... if lvCustomers.ItemIndex >= 0 then
    InvoiceQuery.ExecSQL();//have things changed in XE4?  You usually only call ExecSQL when your not returning a cursor...like Update/Insert/Deletes...
    InvoiceQuery.Open;

 // end;
  InvoiceQuery.Next;
  TabControl1.ActiveTab:= tiInvoice;

Open in new window


Here's the new code

  InvoiceQuery.Active:= False
//moved to FormCreate    InvoiceQuery.SQL.Text:=('Select Customers.Name,Customers.ID,Invoice.CusID From Customers Inner Join Invoice On Invoice.CusID = Customers.ID Where Customers.ID = :lvcusDetail');
if lvCustomers.ItemIndex >= 0 then
begin
    InvoiceQuery.ParamByName('lvcusDetail').asinteger := strToInt(lvCustomers.Items.Item[lvCustomers.ItemIndex].Detail);
    InvoiceQuery.Open;
    InvoiceQuery.Next;
end;

  TabControl1.ActiveTab:= tiInvoice;

Open in new window

0
 
LVL 27

Assisted Solution

by:Sinisa Vuk
Sinisa Vuk earned 20 total points
ID: 39228143
... Remove this line from upper example:
InvoiceQuery.Next;
0
 
LVL 12

Author Comment

by:Grant1842
ID: 39228682
@House_of_Dexter
This new code still gives the same record (First Record  0) no matter which customer name I click on

@sinisav
Same thing even if i remove the InvoiceQuery.Next;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 240 total points
ID: 39228753
open a query with detailed information based on customer number ?

procedure TForm1.lvCustomersItemClick(const Sender: TObject;
   const AItem: TListViewItem);
begin
   TabControl1.ActiveTab:= tiInvoice;
   InvoiceQuery.SQL.Text:=
      'Select Customers.Name,Customers.ID,Invoice.CusID From Customers Inner Join Invoice On Invoice.CusID = Customers.ID Where Customers.ID = :lvcusDetail';
    InvoiceQuery.ParamByName('lvcusDetail').asinteger := strToInt(lvCustomers.Items.Item[lvCustomers.ItemIndex].Detail);
    InvoiceQuery.Open;
  end;
end;

Open in new window

0
 
LVL 12

Author Comment

by:Grant1842
ID: 39229504
@Geert
Tried this. I works on all customers i click on except the LAST one.
Error
0
 
LVL 12

Author Comment

by:Grant1842
ID: 39229615
I was doing some research on this error and tried to turn Unicode support on and off but no help.
SQLConnection1.Params.Values['UseUnicode'] := 'False';

Open in new window

0
 
LVL 12

Author Comment

by:Grant1842
ID: 39229623
I am using the sqLite driver to connect to an sqLite database if this makes a differnce.
And Delphi XE4 and the listview is an FMX.listview.
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 240 total points
ID: 39230131
i don't have experience with sqlite
delphi should take care of the translation between unicode and ansi

there will be another problem with last customer
> sample should work on last customer too

how are selecting the last customer ?
you could test it with the id of the last customer instead of the value in the listview
> is the value filled in in the listview ?
0
 
LVL 6

Assisted Solution

by:House_of_Dexter
House_of_Dexter earned 240 total points
ID: 39230210
Try this...and click on each row...do the values match your data?  What happens when you click on the last record?

procedure TForm1.lvCustomersItemClick(const Sender: TObject;
   const AItem: TListViewItem);
begin
  ShowMessage(lvCustomers.Items.Item[lvCustomers.ItemIndex]);
  ShowMessage(lvCustomers.Items.Item[lvCustomers.ItemIndex].Detail);
end;

Open in new window

0
 
LVL 12

Author Comment

by:Grant1842
ID: 39230495
OK.
ShowMessage(lvCustomers.Items.Item[lvCustomers.ItemIndex].Text);
  ShowMessage(lvCustomers.Items.Item[lvCustomers.ItemIndex].Detail);

Open in new window

I can click on any record with the show message and it shows message with the corect info.

The Fist show-message (.text) shows the Name.
The second Show-message(.Detail) shows the correct numbert every record I click on.
So I wonder why it don't work on the ItemClick of the ListView.
0
 
LVL 12

Author Comment

by:Grant1842
ID: 39230498
@Geert
That little > in the image is and Accessory in the FMX.Listview.
0
 
LVL 12

Author Comment

by:Grant1842
ID: 39231565
I've requested that this question be closed as follows:

Accepted answer: 250 points for Geert_Gruwez's comment #a39228753
Assisted answer: 250 points for House_of_Dexter's comment #a39228047
Assisted answer: 0 points for Grant1842's comment #a39229615

for the following reason:

Your code worked guys.
The Problem was there was no record in the sqLite invoice table that matched the query.
I put one in and all works as should.
0
 
LVL 12

Author Closing Comment

by:Grant1842
ID: 39231566
Your guys code worked .
The problem is there was no record in invoice table that matched for the cust ID.
I add a record and it worked just fine.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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