Mysql Filters Help

[mmurdock]

I'm having an issue getting user filters working in MySQL. I have watech this tutorial How_to_create_Report_with_Parameters.html and have followed it. It seems like my filter values are not being passed on. Here is my query:

SELECT
concat_ws(' ', `sis_student_info`.`first_name`,`sis_student_info`.`last_name`) as student_name,
`sis_course_semester`.`semester_year`,
`sis_course_semester`.`semester`,
`sis_course`.`course_name`,
`sis_course`.`course_description`,
`sis_classroom`.`classroom_number`,
`sis_classroom`.`classroom_name`,
concat_ws(' ',`sis_course_faculty`.`first_name`,`sis_course_faculty`.`last_name`) as faculty_name,
`sis_course_schedule`.`period_num`,
`sis_course_schedule`.`block_day_num`,
`sis_course_schedule`.`course_schedule_id`
FROM
`sis_course_schedule`
Inner Join `sis_course` ON `sis_course_schedule`.`course_id` = `sis_course`.`course_id`
Inner Join `sis_course_semester` ON `sis_course_schedule`.`semester_id` = `sis_course_semester`.`semester_id`
Inner Join `sis_course_enrollment` ON `sis_course_schedule`.`course_schedule_id` = `sis_course_enrollment`.`course_schedule_id`
Inner Join `sis_student_info` ON `sis_course_enrollment`.`student_id` = `sis_student_info`.`student_id`
Inner Join `sis_course_faculty` ON `sis_course_schedule`.`faculty_id` = `sis_course_faculty`.`faculty_id`
Left Outer Join `sis_classroom` ON `sis_course_schedule`.`classroom_id` = `sis_classroom`.`classroom_id`
WHERE
`sis_course_semester`.`semester_year` = '@semesteryear' and
`sis_course_semester`.`semester` = '@semester'
Order By
`sis_course_schedule`.`course_schedule_id`,
`sis_student_info`.`last_name`,
`sis_student_info`.`first_name`

Any help would be appreciated.

Thanks,

Mat

[Edward]

Please define parameters (@semester_year and others) in query not in quotes. Parameters definition should have the same name as when you use it (e.g. @semester_year)

Thank you.

[mmurdock]

That doesn't seem to work. It seems that the variable is not being replaced. When I just execute the query a box pops up asking me for data and I put something in and no rows are returned. You can see a screen shot here:



Any help would be appreciated. This is one of the last hurdles for me.

Thanks,

Mat

[Brendan]

The size of your parameter is set to 0 which could be the cause of the problem.
Try increasing the Size property to an appropriate size.

[Edward]

Please remove brackets from your parameters too.
The form of the parameter:

Code: Select all

@semesteryear

Thank you.

[mmurdock]

Ok, so here is what things look like now.



If I take out the parameters and just do a normal where clause I get data, so I know that is working. I turned on logging on my MySQL server and looked at the sql statement the designer was sending and it wasn't replacing the variable with my parameter. I have tried the "size" property at both 0 and 4.

Mat

[Edward]

If you are still no have success to use named parameters in your query, please use unnamed parameters in your report. Please see a flash video about using the unnamed parameters:

http://www.stimulsoft.com/livedemos/Rep ... ers_2.html

P.S. Using the named or unnamed parameters depends on the concrete server and some other reasons and this is not depends on .Net.

Thank you.

[mmurdock]

Well oddly enough using a ?filtername instead of @filtername worked. I thought I had tried that already. Anyway thanks for your help.

Mat

[Edward]

Thank you.

[Edward]

Please note that unnamed parameters works usually with any Server and it is not depends on the version of the OleDb driver.

If you need to use the named parameters, then please use the MySql provider for the StimulReport.Net.

http://www.stimulsoft.com/Downloads/MySQLDatabase.zip

This provider is not included in the StimulReport.Net because the versions of dlls from the MySQL is constantly updated. You need to rebuild the provider for the concrete
version of the MySQL and the concrete build of the StimulReport.Net dlls. Short instruction on building the provider you will find in the zip archive with provider sources.

After that the named parameters will be accessible to use.

Thank you.