[SQLDatasource]Dynamic selection of column

Hey, I have a search form with a selectbox. This selectbox contains the columnnames.
I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.

If you see I have put in the querystring as columnname @.sescolumn which I have initialised as asp:sessionparameter.
But it gives no results. When I put @.sescolumn between [] like normal columnnames are it doesn't work also.

Can someon put my on the right path?

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring%>" SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @.sescolumn like @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" > <SelectParameters> <asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" /> <asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" /> <asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" /> <asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" /> </SelectParameters> </asp:SqlDataSource>

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where ((@.sescolumn='AutoID' AND [AutoID] LIKE @.seskeyword) OR (@.sescolumn='Merk' AND [Merk] LIKE @.seskeyword) OR (@.sescolumn='Kleur' AND [Kleur] LIKE @.seskeyword) OR (@.sescolumn='Type' AND [Type] LIKE @.seskeyword) OR (@.sescolumn='Autotype' AND [Autotype] LIKE @.seskeyword) OR (@.sescolumn='prijs' AND [prijs] LIKE @.seskeyword) OR (@.sescolumn='Zitplaatsen' AND [Zitplaatsen] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding1' AND [Afbeelding1] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding2' AND [Afbeelding2] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding3' AND [Afbeelding3] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding4' AND [Afbeelding4] LIKE @.seskeyword)) and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

Or...

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where CASE @.sesscolumn WHEN 'AutoID' THEN [AutoID] WHEN 'Merk' THEN [Merk] WHEN 'Kleur' THEN [Kleur] WHEN 'Type' THEN [Type] WHEN 'Autotype' THEN [Autotype] WHEN 'prijs' THEN [prijs] WHEN 'Zitplaatsen' THEN [Zitplaatsen] WHEN 'Afbeelding1' THEN [Afbeelding1] WHEN 'Afbeelding2' THEN [Afbeelding2] WHEN 'Afbeelding3' THEN [Afbeelding3] WHEN 'Afbeelding4' THEN [Afbeelding4] END LIKE @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

|||

Damn didn't knew that this was possible. Thanks a lot mate.

Now I have just to convert some columns in where clause to type of string because my session is a string.
Going to find this out

Thanks again

[rsRuntimeErrorInExpression] The Value expression for the textbox

Hey All,
I am hoping that I will get a speedy response to this problem. I am working
in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
multivalue. They work fine, with the following exception:
I'm trying to set an expression in a text box on the report, to display back
to the users which parameters they selected prior to viewing the report.
This works fine for non-multivalue parameters. But, with the multivalue
parameters, I keep getting an error about the '&' not being valid. The
report builds fine, it only errors when I try to preview the report.
Here's the expression in my textbox:
="Report Parameters Entered:" & vbCrLf & "Retailer: " &
Parameters!Retailer.Value & vbCrLf & "Month/Year: " & Parameters!Month.Value
& "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
Parameters!Managed.Value
If I change the parameters in this to .IsMultiValue (for the ones that are
multivalue), the report runs fine. But, the actual values selected are not
returned. What shows up in the report is 'True' for each of the multivalue
parameters (because they are multivalue and I'm using the IsMultiValue?).
Anyway, if anyone knows of a work around for this, or if I am just way off
base...PLEASE let me know as soon as possible.
Thanks so much for your response!
SandiMaybe this tip helps?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481987&SiteID=1
Kaisa M. Lindahl Lervik
"SandiB" <SandiB@.discussions.microsoft.com> wrote in message
news:C87FE985-C985-4C3F-B047-53338EFF7A41@.microsoft.com...
> Hey All,
> I am hoping that I will get a speedy response to this problem. I am
> working
> in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
> multivalue. They work fine, with the following exception:
> I'm trying to set an expression in a text box on the report, to display
> back
> to the users which parameters they selected prior to viewing the report.
> This works fine for non-multivalue parameters. But, with the multivalue
> parameters, I keep getting an error about the '&' not being valid. The
> report builds fine, it only errors when I try to preview the report.
> Here's the expression in my textbox:
> ="Report Parameters Entered:" & vbCrLf & "Retailer: " &
> Parameters!Retailer.Value & vbCrLf & "Month/Year: " &
> Parameters!Month.Value
> & "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
> Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
> Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
> Parameters!Managed.Value
> If I change the parameters in this to .IsMultiValue (for the ones that are
> multivalue), the report runs fine. But, the actual values selected are
> not
> returned. What shows up in the report is 'True' for each of the
> multivalue
> parameters (because they are multivalue and I'm using the IsMultiValue?).
> Anyway, if anyone knows of a work around for this, or if I am just way off
> base...PLEASE let me know as soon as possible.
> Thanks so much for your response!
> Sandi|||Thanks so much! This worked perfectly!
"Kaisa M. Lindahl Lervik" wrote:
> Maybe this tip helps?
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481987&SiteID=1
> Kaisa M. Lindahl Lervik
> "SandiB" <SandiB@.discussions.microsoft.com> wrote in message
> news:C87FE985-C985-4C3F-B047-53338EFF7A41@.microsoft.com...
> > Hey All,
> >
> > I am hoping that I will get a speedy response to this problem. I am
> > working
> > in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
> > multivalue. They work fine, with the following exception:
> >
> > I'm trying to set an expression in a text box on the report, to display
> > back
> > to the users which parameters they selected prior to viewing the report.
> > This works fine for non-multivalue parameters. But, with the multivalue
> > parameters, I keep getting an error about the '&' not being valid. The
> > report builds fine, it only errors when I try to preview the report.
> >
> > Here's the expression in my textbox:
> >
> > ="Report Parameters Entered:" & vbCrLf & "Retailer: " &
> > Parameters!Retailer.Value & vbCrLf & "Month/Year: " &
> > Parameters!Month.Value
> > & "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
> > Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
> > Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
> > Parameters!Managed.Value
> >
> > If I change the parameters in this to .IsMultiValue (for the ones that are
> > multivalue), the report runs fine. But, the actual values selected are
> > not
> > returned. What shows up in the report is 'True' for each of the
> > multivalue
> > parameters (because they are multivalue and I'm using the IsMultiValue?).
> >
> > Anyway, if anyone knows of a work around for this, or if I am just way off
> > base...PLEASE let me know as soon as possible.
> >
> > Thanks so much for your response!
> >
> > Sandi
>
>|||'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\ISOJC111\ISOJC\bin\Debug\ISOJC.vshost.exe', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\10.2.3600.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\CrystalDecisions.Enterprise.Framework\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Enterprise.Framework.dll', No symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\CrystalDecisions.Enterprise.InfoStore\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Enterprise.InfoStore.dll', No symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.ReportSource\10.2.3600.0__692fbea5521e1304\CrystalDecisions.ReportSource.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Shared\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Shared.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Windows.Forms\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.Common\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.WinForms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic.PowerPacks\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.PowerPacks.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Deployment\2.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Design\2.0.0.0__b03f5f7f11d50a3a\System.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread 0x12ac has exited with code 0 (0x0).
The thread 0x1650 has exited with code 0 (0x0).
The thread 0xdc has exited with code 0 (0x0).
'ISOJC.vshost.exe' (Managed): Loaded 'C:\ISOJC111\ISOJC\bin\Debug\ISOJC.exe', Symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Runtime.Remoting\2.0.0.0__b77a5c561934e089\System.Runtime.Remoting.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.ProcessingObjectModel\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.ProcessingObjectModel.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'expression_host_10e7fe2ca2834f48ab7700ebecf77268', No symbols loaded.
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0x1730 has exited with code 0 (0x0).
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0x380 has exited with code 0 (0x0).
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0xd24 has exited with code 0 (0x0).
The thread 0x17ec has exited with code 0 (0x0).
The thread 0x133c has exited with code 0 (0x0).
The program '[5820] ISOJC.vshost.exe: Managed' has exited with code 0 (0x0).
From http://www.google.co.in/search?q=rsRuntimeErrorInExpression&hl=en&start=10&sa=N
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregat

I put this into a textbox, why isn't this possible?

=SUM(ReportItems!GrossGoal1.Value)

I need to do this!!!!

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsMissingAggregateScope] The Value expression for the textbox ‘textbox6’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set.

Build complete -- 2 errors, 0 warnings

I also need to be able to accomplish the same thing. Is there any way to get this done? I have been searching for an answer for a while now, and I can't seem to find anything.

Thanks

|||

Anyone find a solution to this problem?

thanks..

dd

|||There isn't one, Microsoft said they are working on a fix for it in future editions of SSRS, rather an enhancement. We'll see if they ever get to it.|||

Is there another way to get real time row totals in excel without ReportItems? So, if the user changed a value in an excel cell it updates to the total in the spreadsheet (not in the database of course). I got it working for column totals but it's not working for rows. Partly because of the aggregation issue; but, also due to the fact this would have to be more dynamic than the columns one. Anyways, here is an example of what I am talking about:

t < got this working

1 1 1 | 3

1 2 3 | 5

2 3 4 | 8

^^isnt working

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggr

I put this into a textbox, why isn't this possible?

=SUM(ReportItems!GrossGoal1.Value)

I need to do this!!!!

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsMissingAggregateScope] The Value expression for the textbox ‘textbox6’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set.

Build complete -- 2 errors, 0 warnings

I also need to be able to accomplish the same thing. Is there any way to get this done? I have been searching for an answer for a while now, and I can't seem to find anything.

Thanks

|||

Anyone find a solution to this problem?

thanks..

dd

|||There isn't one, Microsoft said they are working on a fix for it in future editions of SSRS, rather an enhancement. We'll see if they ever get to it.|||

Is there another way to get real time row totals in excel without ReportItems? So, if the user changed a value in an excel cell it updates to the total in the spreadsheet (not in the database of course). I got it working for column totals but it's not working for rows. Partly because of the aggregation issue; but, also due to the fact this would have to be more dynamic than the columns one. Anyways, here is an example of what I am talking about:

t < got this working

1 1 1 | 3

1 2 3 | 5

2 3 4 | 8

^^isnt working

[Multiline Textbox]

Hello,

How do set the textbox in a tablereport, so that it regonize a CR (chr(13)) and LF(chr(10)) in the data and jumps to the next line?

John

Problem solved it needs to see a LF chr(10) instead of a CR asc(13).

Thanks John