Solved

SQL Query issue

Posted on 2013-06-06
13
408 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
Industry Leaders: 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 38

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 38

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

Industry Leaders: 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!

Question has a verified solution.

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

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month5 days, 2 hours left to enroll

635 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