Another Dynamic query problem

Hi Jasper,
Well I have another error which I haven't been able to get around with a dynamic query. It's very similar to what I was doing yesterday, but I'm getting an error that I have never recieved before.

The original query was


SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM') AND (RESPONSIBLE_APP = ?) order by SUBMITTED_ON
this works fine.

But again I wanted to add the 'ALL' option, so I added an 'All' to my dropdown parameter.

My code now reads:


="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM')" & IIf(Parameters!ResponsibleApp.Value = "All", "", " AND RESPONSIBLE_APP = ?") & " order by SUBMITTED_ON"

The strange thing is, if I select 'ALL', everything works fine, but if I select any other fields, i get the error:
"Query execution failed for Dataset, No error information available: DB_E_PARAMNOTOPTIONAL(0x80040E10)."

I tried replacing the '?' with 'Parameters!ResponsibleApp.Value', but got another 'invalid column name' error.

See anything I'm missing ??
thanks again !

I've found the solution to the problem. thanks !

Hi, can you please share what your solution was? I have had this same error.

Thanks!

Kristi

I am having a similar problem with trying to create a dynamic query. According to RS-BOL you need to add this IIF... code using the generic query editor but the text string has a 128 character limit.

Anyone know of a solution to this?

I don't actually remember the slution i had to this particular problem, but as I recall, it was just a syntax problem. It really sucks that SQl Server rreporitng services doesn't allow for page breaks, etc in the SQL pane. it makes it much harder !!

="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM')" & IIf(Parameters!ResponsibleApp.Value = "All", "", " AND RESPONSIBLE_APP = ?") & " order by SUBMITTED_ON"


Don't use the IIF stmt in the dynamic query

use the if stmt

if (Parameters!ResponsibleApp.Value = "All")
begin
="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM') order by SUBMITTED_ON"
end
else
begin
="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM') AND RESPONSIBLE_APP = ? order by SUBMITTED_ON"
end

Did anyone try this approach and have it succeed?

I had been having trouble with using the IIF for multiple parameters because I was getting lost in the IIFs (but the IIF was working). I read this thread and attemped the IF option above but I am getting an error on my parameter which is pre-defined in Report Parameters.

Do you need to use a command type other than "text" in order for this to work?

Thanks,
Barbara

Hi,

I am facing error while retreiving from MS Sql server and inserting the same to MySql databse.

The following error it displays when i u error handling.

"DB_E_PARAMNOTOPTIONAL(0x80040E10)";


This type of error i am getting only for some case where

"What is the values of <em>t</em>?"

Thanks,
Ravi