Solved

SQL Query issue

Posted on 2013-06-06
13
404 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
  • 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 26

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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