search

DNNCommunity / DNN.Reports

DNN Reports module provides a simple, flexible, view on your database, any table, any view and display results with plugin visualizers
MIT License
21 stars 25 forks source link

What's wrong with my Query ? #69

Closed Attelageinfos closed 4 years ago

Attelageinfos commented 4 years ago

Hello, I'm not sure I understand the URL parameters settings because that does not work !

The URL with querry is like this : http://www.courscuisine.be/EspaceEtudiants/Recettes/tabid/327/Default.aspx?RECETTESNOM=$veau$

In report module parameters are : Allowed Url Parameters: @url_RECETTESNOM, Active data source : DNN Data source

SQL Querry is :

SELECT DISTINCT RecettesKey, RECETTESNOM AS Nom, RECETTESPLACEMENU AS Type, RECETTESSAISON AS Saison , RECETTESPAYS AS Pays, RECETTESCREATIONDATE AS Date, RECETTECHAUDFROID, RECETTESCONTINENT, RECETTESREGION, RECETTESOUSCATEGORIE, POURLESCOURS, RECETTESTYPEALIMENT FROM Recettes WHERE RECETTESNOM LIKE @url_RECETTESNOM ORDER BY RecettesKey DESC

Without " WHERE RECETTESNOM LIKE @url_RECETTESNOM ", the querry works fine and give me all entries

with the WHERE condition, I have an erro message : There was an error while executing the configured data source: There is an error in your SQL at line 2: Must declare the scalar variable "@url_RECETTESNOM".

I've tried to modify a lot of thinks but nothing works.

Could you please help me and tell me what is wrong and how to do for the request works ?

I am on DNN 7.4.2 and report 6.0

Thanks for your help

Michel

jncraig commented 4 years ago

I think the @url... needs to be in quotes. You should be able toncheck the query using SQL Server Management Studio.

On Sat, Apr 18, 2020 at 4:38 PM Attelageinfos notifications@github.com wrote:

Hello, I'm not sure I understand the URL parameters settings because that does not work !

The URL with querry is like this :

http://www.courscuisine.be/EspaceEtudiants/Recettes/tabid/327/Default.aspx?RECETTESNOM=$veau$

In report module parameters are : Allowed Url Parameters: @url_RECETTESNOM, Active data source : DNN Data source

SQL Querry is :

SELECT DISTINCT RecettesKey, RECETTESNOM AS Nom, RECETTESPLACEMENU AS Type, RECETTESSAISON AS Saison , RECETTESPAYS AS Pays, RECETTESCREATIONDATE AS Date, RECETTECHAUDFROID, RECETTESCONTINENT, RECETTESREGION, RECETTESOUSCATEGORIE, POURLESCOURS, RECETTESTYPEALIMENT FROM Recettes WHERE RECETTESNOM LIKE @url_RECETTESNOM ORDER BY RecettesKey DESC

Without " WHERE RECETTESNOM LIKE @url_RECETTESNOM ", the querry works fine and give me all entries

with the WHERE condition, I have an erro message : There was an error while executing the configured data source: There is an error in your SQL at line 2: Must declare the scalar variable "@url_RECETTESNOM".

I've tried to modify a lot of thinks but nothing works.

Could you please help me and tell me what is wrong and how to do for the request works ?

I am on DNN 7.4.2 and report 6.0

Thanks for your help

Michel

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/DNNCommunity/DNN.Reports/issues/69, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABEDMROAW3BJVCHANVFRJVDRNIFWDANCNFSM4MLP45RQ .

valadas commented 4 years ago

I have not used that feature at all, but I am guessing maybe something like:

WHERE RECETTESNOM LIKE '%@url_RECETTESNOM%'

Single quotes to enclose the string and the % sings as a wildcard if you want to check that it contains your string.

Attelageinfos commented 4 years ago

Hello, Thanks @jncraig and @valadas for their answer.

I just test your suggestions. When I put @url_RECETTESNOM under single quotes whith or whithout %, I have no more error message BUT the request give me 0 answer.

I've just made a test. I put a row in my database where I put the text "@url_RECETTESNOM" in the field "RECETTESNOM" Whith the request
WHERE RECETTESNOM LIKE '%@url_RECETTESNOM%' I have one answer : the row I've just create. Thats means that the request considers "@url_RECETTESNOM" as a chaintext and not as a token.

Any other suggestions to make my request work ?

Thanks for your help.

Michel

jncraig commented 4 years ago

Yes, you are going to need to build a string for the where clause. Putting quotes around RECETTESNOM causes that to be treated as a string.

Perhaps something like: "%@url_" + RECETTESNOM + "%" ?

Like I said, testing with SSMS will probably help you figure it out. If your query won't work in SSMS, it won't work in Reports.

Attelageinfos commented 4 years ago

Thanks for your suggest but that does not work :o(

stale[bot] commented 4 years ago

We have detected this issue has not had any activity during the last 180 days. That could mean this issue is no longer relevant and/or nobody has found the necessary time to address the issue. We are trying to keep the list of open issues limited to those issues that are relevant to the majority and to close the ones that have become 'stale' (inactive). If no further activity is detected within the next 21 days, the issue will be closed automatically. If new comments are are posted and/or a solution (pull request) is submitted for review that references this issue, the issue will not be closed. Closed issues can be reopened at any time in the future. Please remember those participating in this open source project are volunteers trying to help others and creating a better DNN Platform for all. Thank you for your continued involvement and contributions!

stale[bot] commented 4 years ago

This issue has been closed automatically due to inactivity (as mentioned 21 days ago). Feel free to re-open the issue if you believe it is still relevant.