• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

Help needed with Query and listbox

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
DigitalNam
Asked:
DigitalNam
  • 12
  • 11
1 Solution
 
jimyXCommented:
Can you show the part of the code of the query please.
0
 
DigitalNamAuthor Commented:
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
 
jimyXCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
DigitalNamAuthor Commented:
JimyX, seems like it would work but I am getting a access violation error.
0
 
jimyXCommented:
line 14 above, change it to:
  Delete(CID,Length(CID)-2,2);

Open in new window

0
 
jimyXCommented:
Pardon me working without Delphi here. It should be:
  Delete(CID,Length(CID)-1,2);

Open in new window

0
 
DigitalNamAuthor Commented:
Changed but still get the access violation error.
0
 
DigitalNamAuthor Commented:
Tried both changes. :-(
0
 
DigitalNamAuthor Commented:
Get the error even if I comment out line  Delete(CID,Length(CID)-1,2);
0
 
jimyXCommented:
Please show me your final code.
0
 
DigitalNamAuthor Commented:
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
 
jimyXCommented:
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
 
DigitalNamAuthor Commented:
JimyX, my Listview have a published property Checked so it is not selected it is checked. Maybe that helps.

 Report Screen
0
 
jimyXCommented:
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
 
DigitalNamAuthor Commented:
Still no luck. I have attached a screenshot
report.jpg
0
 
jimyXCommented:
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
 
jimyXCommented:
Also what is the DataType of ClientID in your Database?
0
 
DigitalNamAuthor Commented:
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
 
jimyXCommented:
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
 
DigitalNamAuthor Commented:
Error problem is sorted now but I don't get any results.
0
 
DigitalNamAuthor Commented:
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
 
DigitalNamAuthor Commented:
Excellent help!!!!
0
 
jimyXCommented:
Any time :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now