Retrieve several entries from an Excel file

[Jennypi]

Hi,

One of my reports uses an Excel file as a datasource.
This Excel file contains Product IDs:
ID
1
523
18
412
etc.

In a user form, the users can specify which IDs to print stickers for.
Today they can specify only one single ID only -> I have set a filter for the databand, filtering on this ID:

Code: Select all

Products.Id==int.Parse(TextBoxControl1.Text)

How can I do so that they can enter several IDs, separated by a coma? For example: "1, 412".

Thanks for your help.

[Edward]

Hi Jennypi,

Could you please confirm that you are trying to implement the filtering that is similar to the following approach?

https://stackoverflow.com/questions/340 ... -using-net

Thank you,
Edward

[Jennypi]

Hi,

The code described on this link looks Chinese to me , but basically yes, I would like users to be able to type a string "1, 3, 8" in the form and the report to filter on the values contained in the string.

[Edward]

Hi Jennypi,

Yes, that is possible with using a Variable (Variable1) in the Dictionary that will have a list of values.

Then a Databand will have a filter defined as follows:
Variable1.Contains(Products.Id)

Please check the following links:
https://www.stimulsoft.com/en/documenta ... iables.htm
https://www.youtube.com/watch?v=rb5Um9tlpWU

And this is a sample report for you that can be opened in the Demo.exe sample application

DataBandFilterAndAVariableAsAList.mrt

(24.25 KiB) Downloaded 332 times

And this is the video of creation of that report above:
https://www.youtube.com/watch?v=6UyxoLqIkz8

Thank you,
Edward

[Jennypi]

Hi,

Thanks for your explanations.
This solution doesn't seem OK for my needs, as I already have a userform asking some other parameters. I don't know how to deal with this form + the variable user form.

So I've tried something else.
Instead of defining my source as an Excel file, I used an OLEDB connection string and used the following query for the datasource:

Code: Select all

select * 
from [Produit$]
where Id in (@liste)

@liste being retrieved from TextBoxControl1.Text.

But there is a problem: TextBoxControl1.Text is a string (ex: "515,203"), while I need to send numbers (ex: 515,203) to the SQL query.
I'm joining the report and the file.
Could you please help me?

Thank you!

[Edward]

Hi Jennypi,

Thank you for the sample report and the data. I see what you are trying to achieve now. The 2 ways are possible actually there. I have implemented the simplest approach in the attached report template that is using variable values from the datasource. The datasource in your case is an Excel spreadsheet with its OleDb connection.
Variable id1 is of type string. I also have changed the Produit.ID column in the datasource to be of the string type. Some of the values in that column are strings, so this type will work for all the values you would have for that report without an error.

Please see it in action.

If you still prefer to use a custom form, I am sure that way is achivable as well, please let us know and we would help you to tweak the report template as well.

Thank you,
Edward

[Jennypi]

Thanks a lot Edward for your help.
One problem: with my personal application (that a in-house developer had developed some years ago), the user form is not showing up. I don't know why. I'm joining the zip containing the app.

One other comment : what might be not user friendly is the fact that users need to tick the checkboxes of the IDs, instead of simply typing them, separated with comas. As they know the IDs in advance, I think it's better for them to type them instead of going through this big list of IDs.

All this to say, yes, I think using a custom form would be better for my needs

Can you please help me to tweak the original report template with the custom form?
Thanks again for your help!

[HighAley]

Hello.

You could try to use the inserted value in your query without the SQL parameter.
Just add the value in {}.
I am not sure that this will work for the text of the Text control

Code: Select all

select *
from [Produit$]
where Id in ({TextBoxControl1.Text})

But I am sure that it will work for a variable if you pass the value to the variable.

Code: Select all

select *
from [Produit$]
where Id in ({VariableText})

Thank you.

[Jennypi]

Hello!

Thanks for your answer.
I tried both options, after changing Produit.ID column in the datasource to be of the string type.

If I use:

Code: Select all

select *
from [Produit$]
where Id in ({TextBoxControl1.Text})

I'm getting an error "no value given for one or more required parameters"

If I use the other option:

Code: Select all

select *
from [Produit$]
where Id in ({VariableText})

I'm getting a targetinvocationexception error. It seems it's linked to the "TextBoxControl1.Text" I have set in the variable.

I'm joining the mrt files and the excel datasource.
Can you please help me debugging this?

Thanks!

[Alex K.]

Hello,

You can use the following code:

Code: Select all

VariableText = TextBoxControl1.Text;
Produit.ConnectOnStart = true;
Produit.Connect();

Thank you.