Solved

Help needed with Query and listbox

Posted on 2011-02-23
23
554 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
  • 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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 500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 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