Solved

Help needed with Query and listbox

Posted on 2011-02-23
23
553 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

929 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

14 Experts available now in Live!

Get 1:1 Help Now