Solved

Help needed with Query and listbox

Posted on 2011-02-23
23
551 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
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

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

10 Experts available now in Live!

Get 1:1 Help Now