Solved

SQL Query issue

Posted on 2013-06-06
13
398 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
Comment Utility
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 25

Assisted Solution

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

Author Comment

by:Grant1842
Comment Utility
@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
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 240 total points
Comment Utility
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
Comment Utility
@Geert
Tried this. I works on all customers i click on except the LAST one.
Error
0
 
LVL 12

Author Comment

by:Grant1842
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 12

Author Comment

by:Grant1842
Comment Utility
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 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 240 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@Geert
That little > in the image is and Accessory in the FMX.Listview.
0
 
LVL 12

Author Comment

by:Grant1842
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now