Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help needed with Query and listbox

Posted on 2011-02-23
23
Medium Priority
?
563 Views
Last Modified: 2012-05-11
I have trouble doing the following. I have a form with a listview which is populated from my database with the Client ID and the Client Name. I need to select multiple items and pass the selected Client ID's to a query as a parameter. I am using Delphi XE with TMS AdvListview.
0
Comment
Question by:DigitalNam
[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
  • 12
  • 11
23 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34968088
Can you show the part of the code of the query please.
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968106
Hi JimyX:

The attached code is the previous query I had when I did the report one by one according to date but I would like to choose more than one client and print the report for each client from start date to end date. The problem is I don't know how to pass more than one client id to the query as a parameter.
With DM.SearchQry do
  begin
    Active := False;
    Parameters.Clear;
    SQL.Clear;
    SQL.Add('SELECT * FROM Alarm WHERE (AlarmDate >= :Date1)  AND (AlarmDate <= :Date2) AND (ClientName = :Client)');
    Parameters[0].Value := FromDatePicker.DateTime;
    Parameters[1].Value := ToDatePicker.DateTime;
    Parameters[2].Value := ClientCombo.Text;
    Active := True;

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968176
Hi Jan,
You can use "in" instead of "=":
procedure TForm1.Button1Click(Sender: TObject);
var
  i:integer;
  CID : String;
begin
  CID := '';
  for i:= 0 to ListView1.Items.Count -1 do
    begin
      if ListView1.Items[i].Selected then
        begin
          CID := CID + ListView1.Items[i].SubItems[x] +', '
        end;
    end;
  Delete(CID,Length(CID)-2,Length(CID));
With DM.SearchQry do
  begin
    Active := False;
    Parameters.Clear;
    SQL.Clear;
    SQL.Add('SELECT * FROM Alarm WHERE (AlarmDate >= :Date1)  AND (AlarmDate <= :Date2) AND (ClientName = :Client) AND (ClientID in ('+ CID +'))');
    Parameters[0].Value := FromDatePicker.DateTime;
    Parameters[1].Value := ToDatePicker.DateTime;
    Parameters[2].Value := ClientCombo.Text;
    Active := True;
  end;
end;

Open in new window

Is this what you are after, if not please explain a bit.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:DigitalNam
ID: 34968384
JimyX, seems like it would work but I am getting a access violation error.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968398
line 14 above, change it to:
  Delete(CID,Length(CID)-2,2);

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968411
Pardon me working without Delphi here. It should be:
  Delete(CID,Length(CID)-1,2);

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968414
Changed but still get the access violation error.
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968421
Tried both changes. :-(
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968426
Get the error even if I comment out line  Delete(CID,Length(CID)-1,2);
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968428
Please show me your final code.
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968441
This is the complete procedure.
procedure TMonthlyReportForm.Button1Click(Sender: TObject);
var
  i:integer;
  CID : String;
begin
  CID := '';
  for i:= 0 to LV.Items.Count -1 do
    begin
      if LV.Items[i].Selected then
        begin
          CID := CID + LV.Items[i].SubItems[i] +', '
        end;
    end;
// Delete(CID,Length(CID)-1,2);


 With MainForm.SearchQry do
 begin
   Active := False;
   Params.Clear;
   SQL.Clear;
   SQL.Add('SELECT * FROM Alarms WHERE (AlarmDate >= :Date1)  AND (AlarmDate <= :Date2) AND (ClientName = :Client) AND (ClientID in ('+ CID +'))');
   Params[0].Value := FromDatePicker.DateTime;
   Params[1].Value := ToDatePicker.DateTime;
   Active := True;
   ExecSQL;
  end;

//  Mainform.R_AlarmSource.DataSet := MainForm.SearchQry;
//  Mainform.R_ClientSource.DataSet := MainForm.ClientTable;

//  MainForm.Report.LoadFromFile(ExtractFilePath(Application.ExeName)+'\Reports\MonthlyReport.fr3');
//  MainForm.Report.PrepareReport(True);
//  MainForm.Report.ShowPreparedReport;
end;

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968545
Line 11:
   CID := CID + LV.Items[i].SubItems[i] +', ';

Open in new window


Should be:
   CID := CID + LV.Items[i].SubItems[x] +', ';
   //Where x is the Client ID column number -2

Open in new window


If you attach a screen shot of the ListView showing the column I will be able to tell you the exact number.
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968573
JimyX, my Listview have a published property Checked so it is not selected it is checked. Maybe that helps.

 Report Screen
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968601
OK let's test this one please:
procedure TMonthlyReportForm.Button1Click(Sender: TObject);
var
  i:integer;
  CID : String;
begin
  CID := '';
  for i:= 0 to LV.Items.Count -1 do
    begin
      if LV.Items[i].Checked then
        begin
          CID := CID + LV.Items[i].Caption +', '
        end;
    end;
// Delete(CID,Length(CID)-1,2);


 With MainForm.SearchQry do
 begin
   Active := False;
   Params.Clear;
   SQL.Clear;
   SQL.Add('SELECT * FROM Alarms WHERE (AlarmDate >= :Date1)  AND (AlarmDate <= :Date2) AND (ClientName = :Client) AND (ClientID in ('+ CID +'))');
   Params[0].Value := FromDatePicker.DateTime;
   Params[1].Value := ToDatePicker.DateTime;
   Active := True;
   ExecSQL;
  end;

//  Mainform.R_AlarmSource.DataSet := MainForm.SearchQry;
//  Mainform.R_ClientSource.DataSet := MainForm.ClientTable;

//  MainForm.Report.LoadFromFile(ExtractFilePath(Application.ExeName)+'\Reports\MonthlyReport.fr3');
//  MainForm.Report.PrepareReport(True);
//  MainForm.Report.ShowPreparedReport;
end;

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968624
Still no luck. I have attached a screenshot
report.jpg
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968673
Where do you pass the value for the third parameter?

SELECT * FROM Alarms WHERE (AlarmDate >= :Date1)  AND (AlarmDate <= :Date2) AND (ClientName = :Client) AND (ClientID in ('+ CID +'))
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968709
Also what is the DataType of ClientID in your Database?
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968736
I removed the third parameter, saw I didn't need it but still gives the error. The ClientID is a string in my database.

I have the following code that puts all the selected ClientID's in a listbox. Can't we maybe take all the items in the listbox then which will only be the ClientID and pass that as the parameters to the query?
procedure TMonthlyReportForm.LVItemChecked(Sender: TObject; Item: TListItem);
begin
// if Item.Checked <> (ListBox1.Items.IndexOf(Item.Caption) <> -1) then
//    if Item.Checked then
//         ListBox1.Items.Add(Item.Caption)
//    else ListBox1.Items.Delete(ListBox1.Items.IndexOf(Item.Caption));
end;

Open in new window

report.jpg
0
 
LVL 24

Accepted Solution

by:
jimyX earned 2000 total points
ID: 34968841
I assumed ClientID is a Number. Since it's a String then the ClientID must be 'quoted':
procedure TMonthlyReportForm.Button1Click(Sender: TObject);
var
  i:integer;
  CID : String;
begin
  CID := '''';
  for i:= 0 to LV.Items.Count -1 do
    begin
      if LV.Items[i].Checked then
        begin
          CID := CID + LV.Items[i].Caption + QuotedStr(', ');
        end;
    end;
 Delete(CID,Length(CID)-2,4);

Open in new window


It can be also copied from the ListBox:
procedure TMonthlyReportForm.Button1Click(Sender: TObject);
var
  i:integer;
  CID : String;
begin
  CID := '''';
  for i:= 0 to ListBox1.Count -1 do
    begin
      CID := CID + ListBox1.Items.Strings[i] + QuotedStr(', ');
    end;
 Delete(CID,Length(CID)-2,4);

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968875
Error problem is sorted now but I don't get any results.
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 34968893
False alarm. I get results, but I didn't take the time into account :-) Thank you so much for the excellent help as always.
0
 
LVL 1

Author Closing Comment

by:DigitalNam
ID: 34968897
Excellent help!!!!
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34968912
Any time :-)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

609 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