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 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