Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query issue

Posted on 2013-06-06
13
Medium Priority
?
418 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 960 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 28

Assisted Solution

by:Sinisa Vuk
Sinisa Vuk earned 80 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 960 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 960 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 960 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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 video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

783 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