locked
Filtering results in Report.rdlc RRS feed

  • Question

  • User-851246726 posted

    I am displaying data in a report.

    I want to filter the data in the report based on the selected value from ta dropdown menu.

    This now I have done this but seems like data is not getting affected in the report and "Amount Paid" field is empty for all t he field even though there are value saved in the database for this field.

     

    <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <asp:ObjectDataSource ID="ObjectDataSource3" runat="server"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
            TypeName="DropDownTableAdapters.YearTableTableAdapter">
        </asp:ObjectDataSource>
        <br />
        <br />
        Select Year
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
            DataSourceID="ObjectDataSource3" DataTextField="Year_Of_Payment"
            DataValueField="Year_Of_Payment">
        </asp:DropDownList>
        <br />
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
            Font-Size="8pt" Height="11in" InteractiveDeviceInfos="(Collection)"
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="8.5in">
            <LocalReport ReportPath="ReportTestDrop.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource2" Name="DataSet1" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
            TypeName="DataSet2TableAdapters.DataTable1TableAdapter">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" DefaultValue="-1"
                    Name="YearPaayment" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            SelectMethod="GetData" TypeName="DataSet2TableAdapters.DataTable1TableAdapter">
        </asp:ObjectDataSource>

     

    My ObjectDataSource3, I am using this SQL Command:

    SELECT DISTINCT YearTable.Year_Of_Payment
    FROM         MemberTable INNER JOIN
                          Payment_Table ON MemberTable.ID = Payment_Table.ID INNER JOIN
                          YearTable ON Payment_Table.YearID = YearTable.YearID
    ORDER BY YearTable.Year_Of_Payment DESC

     

    My ObjectDataSource2, I am using this SQL Command:

    SELECT DISTINCT MemberTable.FirstName, MemberTable.LastName, MemberTable.City, MemberTable.State, Payment_Table.AmountPaid
    FROM         MemberTable INNER JOIN
                          Payment_Table ON MemberTable.ID = Payment_Table.ID INNER JOIN
                          YearTable ON Payment_Table.YearID = YearTable.YearID
    WHERE     (YearTable.Year_Of_Payment = @YearPaayment)

     

    PLEASE HELP

    Tuesday, May 24, 2011 12:37 PM

Answers

  • User-1828494216 posted

    Hi,

    step 1: In Visual Studio open Server Explorer or in SQL Server Management Studio, navigate to your DataBase and create a stored procedure.

    CREATE PROCEDURE dbo.getReportData
    	(
    	@Status bit,
    	@Year int
    	)
    AS
    	SELECT MemberTable.ID, MemberTable.FirstName, MemberTable.LastName, MemberTable.Address,
    		   MemberTable.POBox, MemberTable.City, MemberTable.State, Payment_Table.AmountPaid
    		   FROM MemberTable INNER JOIN Payment_Table ON MemberTable.ID = Payment_Table.ID
    		   INNER JOIN YearTable ON Payment_Table.YearID = YearTable.YearID
    		   WHERE (Payment_Table.Status = @Status) AND (YearTable.YearID = @Year)

    Execute it and make sure that it returns data. I created the tables based on the information you provided in your previous post and they should match. BUT please do check if they really do!

    step 2: Create a new EMPTY Web Application and add a new Form. Add the code below to your new form and make sure that it's matching! Then open your Web.Config file in SolutionExplorer and add a connection string.

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <div>
            <asp:DropDownList ID="DropDownList1" runat="server" 
                DataSourceID="SqlDataSource1" DataTextField="Year_Of_Payment" 
                DataValueField="YearID">
            </asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                SelectCommand="SELECT * FROM [YearTable]"></asp:SqlDataSource>
            <asp:DropDownList ID="DropDownList2" runat="server" 
                DataSourceID="SqlDataSource2" DataTextField="Status" DataValueField="Status">
            </asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                SelectCommand="SELECT DISTINCT [Status] FROM [Payment_Table]">
            </asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    <?xml version="1.0"?>
    <!--
      For more information on how to configure your ASP.NET application, please visit
      http://go.microsoft.com/fwlink/?LinkId=169433
      -->
    <configuration>
      <connectionStrings>
        <add name="testConnectionString" connectionString="Data Source=./SQLEXPRESS;Initial Catalog=YOURDBNAME;Integrated Security=True" providerName="System.Data.SqlClient" />
      </connectionStrings>
      <system.web>

    now run the application and make sure that your DropDownLists hold the data from your DataBase.

    step 3: Add ReportViewer control to your form and from Solution Explorer add new report.

    step 4: Drag Table from Toolbox into the report and a wizard will open.

    Step 5: Click New to create Data source, use the connection from your WebConfig and select ONLY STORED PROCEDURE you created in first step. Click OK until you see your report.

    Step 5: You should be now back on your report. In table click into the Data (not Header) row and a small table icon will apear in upper right corner (see 2nd image). Click on the image and add ur fields. When u're done, dont forget to save!

    Step 6: Go back to the the Form (Design view) and click on the arrow in upper right corner of reportViewer and select the report you just created.

    Step 7: Look below the ReportViewer, there should be a box named ObjectDataSource, click on it, again use the arrow, click configure datasource, click next (If you'll see some error about not able to find datatable, just reBuild your project and get back to this step), Choose Method GET DATA blah blah blah... (that's the name of the stored procedure you created in first step). Click Next. Now we need to set parameters. Select Contol and ID DropDownList2, repeat the same for the year but choose DropDownList1! Finish.

    Step 7: Now go in the code behind (press F7 in you report) of your form and add this line of code in Page Load event:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
            ReportViewer1.LocalReport.Refresh();
    }

    now everything should work!

    Hope this helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 31, 2011 12:36 PM

All replies

  • User-851246726 posted

    anyone ? plz help ?

    Wednesday, May 25, 2011 6:56 AM
  • User2105670541 posted
    while calling the report, set the report parameters with the dropdown selected value as a parameter of the report
    Wednesday, May 25, 2011 11:23 PM
  • User-851246726 posted

    how ? could you please show me, I have posted my code above. I am new with reports.

    Wednesday, May 25, 2011 11:34 PM
  • User2105670541 posted
    use the namespace first in your codebehind file that will be : using Microsoft.Reporting.WebForms; Now declare a variable in your code: ReportParameter[] parameters; now set your report parameters values with code as below : parameters = new ReportParameter[1]; parameters[0] = new ReportParameter("EmployeeCode", _stringEmployeeCode); Now set the parameter to the reportview object like :: ReportViewer1.LocalReport.SetParameters(parameters); Im assuming you've already set the path of reports and something :)
    Thursday, May 26, 2011 12:21 AM
  • User-851246726 posted

    Sorry ashutosh, i am a newbie so haven't configured any path (don't know how to)

    Whatever I did  - I posted above in t he code.

    After following your directions This is how my .cs file looks like: (PLEASE ADVISE- how to correct this ?)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Reporting.WebForms;

    public partial class ReportTest : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            ReportParameter[] parameters;
            parameters = new ReportParameter[1]; parameters[0] = new ReportParameter("EmployeeCode", _stringEmployeeCode);
            ReportViewer1.LocalReport.SetParameters(parameters);
        }
    }

     

    I will deeply appreciate if you can help me with the code since I am a newbiew in .NET.

    Thursday, May 26, 2011 12:31 AM
  • User2105670541 posted
    okay, first of all on page load set the local report path if your report is locally created in page load, ReportViewer.LocalReport.ReportPath="~/YourSubDirectory/Reportname.RDL"; else if you've deployed your reports on ReportServer than you should use: ReportViewer.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerPath"].ToString()); ReportViewer.ServerReport.ReportPath = "/YourReportsFolderInReportServer/Reportname.RDL" ;
    Thursday, May 26, 2011 12:41 AM
  • User-851246726 posted

    like this ?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Reporting.WebForms;

    public partial class ReportTest : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            ReportParameter[] parameters;
            parameters = new ReportParameter[1]; parameters[0] = new ReportParameter("EmployeeCode", _stringEmployeeCode);
            ReportViewer1.LocalReport.SetParameters(parameters);
            ReportViewer.LocalReport.ReportPath = "ReportTestDrop.rdlc";
        }
    }

     

    it is giving errors and 2nd / 4th lines are underlined which means they are incorrect, right ?

    Thursday, May 26, 2011 12:49 AM
  • User2105670541 posted
    okay pass it like :: ReportParameter[] parameters = new ReportParameter[1]; parameters[0] = new ReportParameter("EmployeeCode", _stringEmployeeCode); ReportViewer1.LocalReport.SetParameters(parameters); please make sure the name of your report viewer control :)
    Thursday, May 26, 2011 12:54 AM
  • User-851246726 posted

    now ?

    Thursday, May 26, 2011 1:31 AM
  • User2105670541 posted
    it should run if everything is fine... if its still not working, tell me the complete structure of your web application and report projects, plus whether your reports are deployed on ReportServer or in local solution in your web site directory, everything.
    Thursday, May 26, 2011 1:47 AM
  • User-851246726 posted

    Hello Ashutosh, it is not working and I am getting this error:

    Compilation Error

    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Compiler Error Message: CS0103: The name '_stringEmployeeCode' does not exist in the current context

    Source Error:


    Line 12:     protected void Page_Load(object sender, EventArgs e)
    Line 13: {
    Line 14: ReportParameter[] parameters = new ReportParameter[1]; parameters[0] = new ReportParameter("EmployeeCode", _stringEmployeeCode); ReportViewer1.LocalReport.SetParameters(parameters);
    Line 15: }
    Line 16: }

    Thursday, May 26, 2011 11:48 AM
  • User-851246726 posted

    Here is the complete description of my report.

    Here is the structure of my tables:

    This is the structure of my 3 tables:

    CREATE TABLE [dbo].[MemberTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](max) NULL,
    [LastName] [nvarchar](max) NULL,
    [Address] [nvarchar](max) NULL,
    [City] [nvarchar](max) NULL,
    [State] [nvarchar](max) NULL,
    [Zip] [nvarchar](max) NULL,
    [POBox] [nvarchar](max) NULL,
    CONSTRAINT [PK_MemberTable] PRIMARY KEY CLUSTERED

    ----------------------
    CREATE TABLE [dbo].[Payment_Table](
    [PaymentID] [int] IDENTITY(1,1) NOT NULL,
    [ID] [int] NOT NULL,
    [YearID] [int] NOT NULL,
    [SuggestedDonation] [money] NULL,
    [AmountPaid] [money] NULL,
    [PaymentDate] [datetime] NULL,
    [Status] [bit] NULL,
    CONSTRAINT [PK_Payment_Table] PRIMARY KEY CLUSTERED

    ------------------

    CREATE TABLE [dbo].[YearTable](
    [YearID] [int] IDENTITY(1,1) NOT NULL,
    [Year_Of_Payment] [nvarchar](50) NULL,
    CONSTRAINT [PK_YearTable] PRIMARY KEY CLUSTERED

     

    I want to create a report in which I should be able display:

    ID, First Name, Last Name, Address, POBox, State, City, AmountPaid

    Want to add filters with 2 or 3 dropdown menus:

    Filter Option Based on "Year": Choose Year (for which I want to display the report - year will be coming from database from yearTable)

    In the select statment I will display, select fields where status = false (means unpaid) where year = selected from dropdown.

    Similar 2nd report I will create for "Paid" status

     

    Right now I am developing project on my local machine but database is still on the server and as soon as I am able to create  this report I will transfer all the pages and reports on the server.

    Please let me know if you need more information. thank you


    Thursday, May 26, 2011 11:55 AM
  • User2105670541 posted

    fair enough...not a big problem....

     

    first of all create a report that fetches data based on your YearPayment parameter...pretty simple, just create a report parameter in your report, where you will allow a user to select or enter year of payment. i'm assuming your parameter name is Payment_Year.

    now create a dataset in your report project, that will fetch the data from your database based on the above create parameter.

    save your report project, host it in your report server.

    report server path could be like http://yourservername/reports

    now create an aspx page that will have a reportviewer page, plus the dropdown from where you can select the Payment year value.

    now set the report viewer server path and report viewer report name in page_load event like below :

     

    ReportViewer.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerPath"].ToString()); 
    
    ReportViewer.ServerReport.ReportPath = "/YourReportsFolderInReportServer/Reportname.RDL" ; 

     

    now create a report parameter array from where you will pass your dropdown selected value as parameter to this report like below:

     

    ReportParameter[] parameters = new ReportParameter[1]; 
    parameters[0] = new ReportParameter("Payment_Year", DropDownList1.SelectedValue);

     

    here Payment_Year will be your report project parameter, and the value will get passed to this by DropDownList1 from where you will select your Payment_Year..

     

    hope it will help you :)

     

     

    Thursday, May 26, 2011 1:37 PM
  • User-851246726 posted

    Hello Ashutosh, i am little confused since I don't have much experience with reports.

    I am telling you what I have done, please correct my code if I am wrong somewhere.

    ----------------------------------------------------------------

    The is what I am configured in my Dataset: DropDown.xsd

    SELECT DISTINCT YearTable.Year_Of_Payment
    FROM         MemberTable INNER JOIN
                          Payment_Table ON MemberTable.ID = Payment_Table.ID INNER JOIN
                          YearTable ON Payment_Table.YearID = YearTable.YearID
    ORDER BY YearTable.Year_Of_Payment DESC

    ----------------------------------------------------------------

    In my report I am just displaying like this: ReportTestDrop.rdlc

    FirstName     LastName      Address        POBox      City      State    ZipCode      Amount

     

    .........Data should be displayed here..........


    ----------------------------------------------------------------

    This is my ReportView.aspx page

    <asp:ObjectDataSource ID="ObjectDataSource3" runat="server"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
            TypeName="DropDownTableAdapters.YearTableTableAdapter">
        </asp:ObjectDataSource>
        <br />
        <br />
        Select Year
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
            DataSourceID="ObjectDataSource3" DataTextField="Year_Of_Payment"
            DataValueField="Year_Of_Payment">
        </asp:DropDownList>
        <br />
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
            Font-Size="8pt" Height="11in" InteractiveDeviceInfos="(Collection)"
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="8.5in">
            <LocalReport ReportPath="ReportTestDrop.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource2" Name="DataSet1" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
            TypeName="DataSet2TableAdapters.DataTable1TableAdapter">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" DefaultValue="-1"
                    Name="YearPaayment" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            SelectMethod="GetData" TypeName="DataSet2TableAdapters.DataTable1TableAdapter">
        </asp:ObjectDataSource>

    ----------------------------------------------------------------

    This is my ReportView.aspx.CS page

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Reporting.WebForms;

    public partial class ReportView : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            ReportViewer.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerPath"].ToString());

            ReportViewer.ServerReport.ReportPath = "ReportTestDrop.rdlc";
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ReportParameter[] parameters = new ReportParameter[1];
            parameters[0] = new ReportParameter("Payment_Year", DropDownList1.SelectedValue);
        }
    }

     

    My report is under the project folder, since I am working on the project that is why in the .CS file I just gave the name of the report as location.

    Please help me with the CODE. Anything you want me to add or remove or change ?

     

    I am getting this ERROR:

    Compilation Error

    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Compiler Error Message: CS0120: An object reference is required for the non-static field, method, or property 'Microsoft.Reporting.WebForms.ReportViewer.ServerReport.get'

    Source Error:


    Line 12:     protected void Page_Load(object sender, EventArgs e)
    Line 13: {
    Line 14: ReportViewer.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerPath"].ToString());
    Line 15:
    Line 16: ReportViewer.ServerReport.ReportPath = "ReportTestDrop.rdlc";


     

    Thursday, May 26, 2011 4:16 PM
  • User-851246726 posted

    I designed a demo to show what I am looking for (what filters):

    I actually want 2 kinds of filters to work with the report,

    1) Select Year

    2) Status (Paid / Un-paid / All)

    Here's the screenshot:

    Thursday, May 26, 2011 5:50 PM
  • User2105670541 posted

    Jeff, em saying, why dont you create a report using BIDS because you later want to deploy on the server, and when you will deploy your report on your report server you again have to change your code.

    okay lets do it in steps,

    first create a report using BIDS, create two parameters there, the payment year and the other one, populate their values from the dataset, and based on the user selection of those parameters populate your recod in the report table. you can visit the link below if you're having some trouble using this.

    http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-1/

    now, once you are done with your serverreport deploy it on your development server or in your local machine, if the reports server is configured locally, then you can deploy them on your local server. the report server path would be like http://yourservername/reports

    once you are done deploying report there, now do the code portions.

    now use the code below:

     

    protected void Page_Load(object sender, EventArgs e)
        {
         }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {

    ReportViewer.ServerReport.ReportServerUrl = new Uri(_yourReportServer);
    //the report path below should be the rdl file name not rdlc, for rdlc file and viewing report locally use the link at the end of the post

            ReportViewer.ServerReport.ReportPath = "ReportTestDrop.rdlc";

    ReportParameter[] parameters = new ReportParameter[1];
            parameters[0] = new ReportParameter("Payment_Year", DropDownList1.SelectedValue);


        }

    if you do not want to configure report on server and do the processing on your local machine use link below

    http://chrispar.blogspot.com/2008/02/blog-post.html

    hope it will help you a bit more :)

    Thursday, May 26, 2011 11:16 PM
  • User-851246726 posted

    Hello Ashutosh,

    The only thing that is confusing me is that what is BIDS. I know it is Business Intelligence Development Studio .

    The server on which I will be deploying the reports will have SSMS Express and in the link you provided, they mentioned that it comes with SQL Server 2005 (and other versions).

    Does reporting working this way only ? I created a sample report without any filter option, just like the way I posted code above.

    It is working on local machine and server too. Why we needs to follow BIDS?

    What I have done till now. On my server I have installed MS Reporting Services plus on my local machine too.

    I deeply appreciate that you are helping me. Please advise how I should go about these reporting now ?

    Friday, May 27, 2011 12:48 AM
  • User2105670541 posted

    BIDS is just and IDE to create BI solutions like Analysis project, SSIS project or SSRS projects.

    if everything lying on your local server then you must not set the report server path or other thing i've mentioned. you should directly set their path, you can set it at the right top site of your report viewer control. now you can use them.

    best part is go through the URL below, it will definitely help:

    http://www.codeproject.com/KB/webforms/ReportViewer.aspx

    Friday, May 27, 2011 7:05 AM
  • User-851246726 posted

    I have done thru this tutorial ashutosh, which is indeed helpful.

    I am not very experienced with the coding so could you please atleast help me with the codebehind file ? I explained you my situation above (with all requirements), can you please provide me code for my situation ? and SQL statements that I need to use ?

    PLEASE

    Friday, May 27, 2011 7:51 AM
  • User-851246726 posted

    please help this newbie ?

    Monday, May 30, 2011 8:17 AM
  • User-1828494216 posted

    Hi,

    step 1: In Visual Studio open Server Explorer or in SQL Server Management Studio, navigate to your DataBase and create a stored procedure.

    CREATE PROCEDURE dbo.getReportData
    	(
    	@Status bit,
    	@Year int
    	)
    AS
    	SELECT MemberTable.ID, MemberTable.FirstName, MemberTable.LastName, MemberTable.Address,
    		   MemberTable.POBox, MemberTable.City, MemberTable.State, Payment_Table.AmountPaid
    		   FROM MemberTable INNER JOIN Payment_Table ON MemberTable.ID = Payment_Table.ID
    		   INNER JOIN YearTable ON Payment_Table.YearID = YearTable.YearID
    		   WHERE (Payment_Table.Status = @Status) AND (YearTable.YearID = @Year)

    Execute it and make sure that it returns data. I created the tables based on the information you provided in your previous post and they should match. BUT please do check if they really do!

    step 2: Create a new EMPTY Web Application and add a new Form. Add the code below to your new form and make sure that it's matching! Then open your Web.Config file in SolutionExplorer and add a connection string.

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <div>
            <asp:DropDownList ID="DropDownList1" runat="server" 
                DataSourceID="SqlDataSource1" DataTextField="Year_Of_Payment" 
                DataValueField="YearID">
            </asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                SelectCommand="SELECT * FROM [YearTable]"></asp:SqlDataSource>
            <asp:DropDownList ID="DropDownList2" runat="server" 
                DataSourceID="SqlDataSource2" DataTextField="Status" DataValueField="Status">
            </asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                SelectCommand="SELECT DISTINCT [Status] FROM [Payment_Table]">
            </asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    <?xml version="1.0"?>
    <!--
      For more information on how to configure your ASP.NET application, please visit
      http://go.microsoft.com/fwlink/?LinkId=169433
      -->
    <configuration>
      <connectionStrings>
        <add name="testConnectionString" connectionString="Data Source=./SQLEXPRESS;Initial Catalog=YOURDBNAME;Integrated Security=True" providerName="System.Data.SqlClient" />
      </connectionStrings>
      <system.web>

    now run the application and make sure that your DropDownLists hold the data from your DataBase.

    step 3: Add ReportViewer control to your form and from Solution Explorer add new report.

    step 4: Drag Table from Toolbox into the report and a wizard will open.

    Step 5: Click New to create Data source, use the connection from your WebConfig and select ONLY STORED PROCEDURE you created in first step. Click OK until you see your report.

    Step 5: You should be now back on your report. In table click into the Data (not Header) row and a small table icon will apear in upper right corner (see 2nd image). Click on the image and add ur fields. When u're done, dont forget to save!

    Step 6: Go back to the the Form (Design view) and click on the arrow in upper right corner of reportViewer and select the report you just created.

    Step 7: Look below the ReportViewer, there should be a box named ObjectDataSource, click on it, again use the arrow, click configure datasource, click next (If you'll see some error about not able to find datatable, just reBuild your project and get back to this step), Choose Method GET DATA blah blah blah... (that's the name of the stored procedure you created in first step). Click Next. Now we need to set parameters. Select Contol and ID DropDownList2, repeat the same for the year but choose DropDownList1! Finish.

    Step 7: Now go in the code behind (press F7 in you report) of your form and add this line of code in Page Load event:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
            ReportViewer1.LocalReport.Refresh();
    }

    now everything should work!

    Hope this helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 31, 2011 12:36 PM
  • User-851246726 posted

    Hi,

    thank you very much for helping me with this problem.

    I created SP and Report page with dropdowns after that I got little bit confused so Yes i would need test project.

    I PMed you my email address so that you can email me the code.

    thank you

    Tuesday, May 31, 2011 1:06 PM
  • User-1828494216 posted

    Hi,

    thank you very much for helping me with this problem.

    I created SP and Report page with dropdowns after that I got little bit confused so Yes i would need test project.

    I PMed you my email address so that you can email me the code.

    thank you

    Hi, please check my last post, i updated it and created you a step by step tutorial with pictures.

    Wednesday, June 1, 2011 6:13 AM
  • User-851246726 posted

    Hi,

    Thank you for the nice tutorial. I followed all the steps but when I try to run the page I get this error:

    .............................

    • A data source instance has not been supplied for the data source 'DataSet1'.

    .............................

    I also rebuilt my project still getting the same error, what could be the problem ?

    I also noticed on thing, when I try to "Execute" the stored procedure, I get no result of any kind of values, this is what I get in output window:

    Running [dbo].[getReportData] ( @Status = True, @Year = 2006 ).

    ID                                                                                                                                                                                                                                                               FirstName                                                                                                                                                                                                                                                        LastName                                                                                                                                                                                                                                                         Address                                                                                                                                                                                                                                                          POBox                                                                                                                                                                                                                                                            City                                                                                                                                                                                                                                                             State                                                                                                                                                                                                                                                            AmountPaid                                                                                                                                                                                                                                                       ----------------
    No rows affected.
    (0 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[getReportData].

    As far as I can see the in query builder the table mapping is correct.

    For I don't know what reason I see duplicate controls here: (I only used your code which means one dropdown menu for year and one for status)



    Wednesday, June 1, 2011 10:03 AM
  • User-1828494216 posted

    .............................

    • A data source instance has not been supplied for the data source 'DataSet1'.

    .............................

    I also rebuilt my project still getting the same error, what could be the problem ?

    Try Building the project again.

    I also noticed on thing, when I try to "Execute" the stored procedure, I get no result of any kind of values, this is what I get in output window:

    Running [dbo].[getReportData] ( @Status = True, @Year = 2006 ).

    for @Year you have to input YearID of the Year not the actual year ;)

    For I don't know what reason I see duplicate controls here: (I only used your code which means one dropdown menu for year and one for status)

    Yeah, that's not a problem, select whichever you want, just make sure the ID is correct.

    Wednesday, June 1, 2011 11:04 AM
  • User-851246726 posted

    I rebuilt my project once again but now I am not getting dataset in the drop down. Instead of that I am getting ajaxtoolkit values

    Here's my screenshots:

    MyDataset:

    My Report Viewer:

    When I try to configure ObjectDataSource, I get this:

    Wednesday, June 1, 2011 11:50 AM
  • User-1828494216 posted

    When I try to configure ObjectDataSource, I get this:

    click show only data components...

    Wednesday, June 1, 2011 11:59 AM
  • User-851246726 posted

    If I mark the checkbox, I don't get anything:

    Wednesday, June 1, 2011 12:01 PM
  • User-1828494216 posted

    double click your dataset in soultion explorer and make sure that there's a TableAdapter present.

    If it is, i really don't know what to say, it should work. Try closing VS and then build it again :/

    Wednesday, June 1, 2011 12:10 PM
  • User-851246726 posted

    wow, it working - THANK YOU SO MUCH

    The only thing that is left is, when I change the value in dropdown menu, everytime I have to press the refresh button to reload the report for the new selected values.

    I did add the code in .cs page but still I need to refresh the report. what should I do to fix it ?

    I also tried AutoPostBack="True" for dropdown menus but it doesn't reload the report with new values.

    Wednesday, June 1, 2011 12:37 PM
  • User-1828494216 posted

    Hi,

    not a problem, please return the favour and mark the posts that helped you as answer.

    The DropDownControls AutoPostBack must be enabled and in your Page_Load event you must add the code i posted to refresh the ReportViewer on every postback. It's not the most elegant solution but it will work. In adition to my code, you can capture the id of the control that cauzed postBack and compare it to the ID's of your DropDowns. If they match refresh the ReportViewer.

    If you follow my example step by step, everything should work as expected.

    Wednesday, June 1, 2011 12:49 PM
  • User-851246726 posted

    thank you VERY much for helping me. You spent your time on my problem i deeply appreciate it.

    Wednesday, June 1, 2011 12:56 PM
  • User-851246726 posted

    Hi, just reporting a small bug - By this code, report paging feature stops working and even if we try to click on "next page", it displays the same page.

    when we use this code with AutoPostBack="true" in dropdown menu

    if (IsPostBack)
           
    ReportViewer1.LocalReport.Refresh();



    Wednesday, June 1, 2011 1:12 PM
  • User-1828494216 posted

    In adition to my code, you can capture the id of the control that cauzed postBack and compare it to the ID's of your DropDowns. If they match refresh the ReportViewer.

    Cool

    If that is not done, obviously the ReportViewer will refresh itselfe on everyPostback and when you click refresh guess what happenes... postback :) If you don't know how to do it, i sugest you search this forum or open up a new threat.

    EDIT:

    The best solution is to create a SelectedIndexChanged Event and bind it do both of the DropDowns and in that event refresh ReportViewer.

    Wednesday, June 1, 2011 1:18 PM
  • User-851246726 posted

    yes, i am familiar with selectIndex

    I did this and it works fine now with paging feature:

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ReportViewer1.LocalReport.Refresh();

        }
        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            ReportViewer1.LocalReport.Refresh();

        }

    Thank you so much for putting me in the right direction.

    Wednesday, June 1, 2011 1:28 PM
  • User-1828494216 posted

    You don't need 2 events, 1 would do ;)

        <div>
            <asp:DropDownList ID="DropDownList1" runat="server" 
                DataSourceID="SqlDataSource1" DataTextField="Year_Of_Payment" 
                DataValueField="YearID" AutoPostBack="True" 
                onselectedindexchanged="DropDownList1_SelectedIndexChanged">
            </asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                SelectCommand="SELECT * FROM [YearTable]"></asp:SqlDataSource>
            <asp:DropDownList ID="DropDownList2" runat="server" 
                DataSourceID="SqlDataSource2" DataTextField="Status" 
                DataValueField="Status" AutoPostBack="True"
                onselectedindexchanged="DropDownList1_SelectedIndexChanged">
            </asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                SelectCommand="SELECT DISTINCT [Status] FROM [Payment_Table]">
            </asp:SqlDataSource>
        </div>
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        ReportViewer1.LocalReport.Refresh();
    }
    Wednesday, June 1, 2011 1:32 PM
  • User-851246726 posted

    oops yea

    thanks again :)

    Wednesday, June 1, 2011 1:42 PM
  • User-851246726 posted

    Hi,

    I have another problem in similar situation.

    Since I mentioned earlier that my MemberTable is like this:

    CREATE TABLE [dbo].[MemberTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](max) NULL,
    [LastName] [nvarchar](max) NULL,
    [Address] [nvarchar](max) NULL,
    [City] [nvarchar](max) NULL,
    [State] [nvarchar](max) NULL,
    [Zip] [nvarchar](max) NULL,
    [POBox] [nvarchar](max) NULL,
    CONSTRAINT [PK_MemberTable] PRIMARY KEY CLUSTERED

    so now i want to generate a report in which I want to display member list based on a dropdown filter for state.

    By dropdown, I should be able to filter records based on the states. (In my database there are few records for which STATE is saved as NULL)

    I followed same steps and this is my code: (I can see all the states in dropdown but report is BLANK)

    My stored procedure:

    ALTER PROCEDURE dbo.getStateReport
            (
           
            @State nvarchar
            )
    AS
            SELECT *
                       FROM MemberTable
                       WHERE (State =@State)
                       

            

    My ASPX page:

    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
       
        <div>

        
            Select State:
            <asp:DropDownList ID="DropDownList2" runat="server"
                onselectedindexchanged="DropDownList2_SelectedIndexChanged" AutoPostBack="True"
                DataSourceID="SqlDataSource2" DataTextField="State" DataValueField="State">
            </asp:DropDownList>
               
              
            
            <asp:SqlDataSource ID="SqlDataSource2" runat="server"
                ConnectionString="<%$ ConnectionStrings:MyDatabaseConString %>"
                SelectCommand="SELECT DISTINCT State  FROM MemberTable">
            </asp:SqlDataSource>
        </div><br />


         <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
            Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="11in"
            Width="8.5in">
             <LocalReport ReportPath="Account\dropbyfilterstate.rdlc">
                 <DataSources>
                     <rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
                         Name="dropbyfilterstate" />
                 </DataSources>
             </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
            TypeName="dropfilterbystateTableAdapters.getStateReportTableAdapter">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList2" Name="State"
                    PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
          
        
    </asp:Content>

    Rest I followed the same steps, but still my report is being displayed as BLANK and filter changing dropdown values is not doing anything in the report.

    NEED HELP

    Tuesday, June 28, 2011 12:54 PM
  • User2105670541 posted

    make your report main dataaset to take one parameter that is the dropdown value of your state dropdown, and pass it to the .rdlc report as below:

     reportViewer1.LocalReport.SetParameters(new ReportParameter("state", ddlstate.selectedvalue));
                reportViewer1.RefreshReport();

    Tuesday, June 28, 2011 1:18 PM
  • User-851246726 posted

    How to do that ?

    I tried your code like this;

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Reporting.WebForms;

    protected void Page_Load(object sender, EventArgs e)
        {
            ReportViewer1.LocalReport.SetParameters(new ReportParameter("State", DropDownList2.SelectedValue));

            ReportViewer1.RefreshReport();        
        }

    It gives this error:

    The event 'Microsoft.Reporting.WebForms.ReportViewer.ReportRefresh' can only appear on the left hand side of += or -=

    If I try without   "ReportViewer1.RefreshReport(); " then I get this:


    An attempt was made to set a report parameter 'State' that is not defined in this report.
    Description: An unhandled exception occurred during the execution of the current web req

    uest. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownReportParameterException: An attempt was made to set a report parameter 'Cart' that is not defined in this report.

    Source Error:


    Line 12:     protected void Page_Load(object sender, EventArgs e)
    Line 13:     {
    Line 14:         ReportViewer1.LocalReport.SetParameters(new ReportParameter("State", DropDownList2.SelectedValue));
    Line 15:         
    Line 16:     }
     

    Tuesday, June 28, 2011 1:38 PM
  • User2105670541 posted

    it is because you have to create a parameter callled state in your report when you are just trying to bind the data from this page to the report.

    just try to create a parameter in your reports main data source and pass this parameter value in your query to get the filtered data for your reports.

    Tuesday, June 28, 2011 3:05 PM
  • User-851246726 posted

    But if you see my code, I did that already:

      <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
            TypeName="dropfilterbystateTableAdapters.getStateReportTableAdapter">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList2" Name="State"
                    PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>

    I hope this the same thing that you're talking about ?

    Tuesday, June 28, 2011 3:12 PM
  • User-851246726 posted

    anyone ? help ?

    Thursday, June 30, 2011 7:29 AM
  • User2105670541 posted

    Hi Jeff!! please post the complete code of your aspx and aspx.cs file so that we try to find a solution.

    Thursday, June 30, 2011 8:29 AM
  • User-851246726 posted

    ok I did everything from starting to show all the steps I followed:

    Step 1:

    Created a stored procedure:

    CREATE PROCEDURE dbo.getStateReport
            (
           
            @State nvarchar
            )
    AS
            SELECT *
                       FROM MemberTable
                       WHERE (State =@State)
                       

            

    Step2:

    Added new "DataSet" in "App_Code" named as "dropfilterState.XSD"

    Then I added "TableAdapter" in which I added the storedprocedure like this:

    Step 3:

    Now I added a "Report" like this which I named as "dropfilterstate.RDLC"

    Step4:

    In my report I added "Data Set" like this:

    Step 5:

    I added columns in my report like this:

    Step 6:

    Now I added a webpage named as "dropfilterbystate.ASPX" to display the report

    Step 7:

    Now I added the report viewer in my page and the drop down menu.

    In my report viewer I selected the report that I created above "dropfilterstate.RDLC"

    Step 8:

    Now after selecting report, I choose "Data Source" > NEW DATA SOURCE like this:

    Here is my output in which when I select State from dropdown menu, my report doesn't show the member in that state:

    This is the code of my "dropfilterbystate.aspx" page

    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <h2> Generate Report - Please make your selections</h2><br />
        <div>

        
            Select State:
            <asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True"
                DataSourceID="SqlDataSource1" DataTextField="State" DataValueField="State" >
            </asp:DropDownList>
               
               
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
                SelectCommand="SELECT DISTINCT [State] FROM [MemberTable]"></asp:SqlDataSource>

            
            
        </div><br />


         <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
            Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="11in"
            Width="8.5in">
             <LocalReport ReportPath="Account\dropfilterstate.rdlc">
                 <DataSources>
                     <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="state" />
                 </DataSources>
             </LocalReport>
       
        </rsweb:ReportViewer>
        
       
        
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"
            TypeName="dropfilterbystateTableAdapters.getStateReportTableAdapter">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList2" Name="State"
                    PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
        
       
        
    </asp:Content>

    NEED HELP

    Friday, July 1, 2011 10:25 AM
  • User2105670541 posted

    Jeff can you create an event for dropdown list (state), i mean double click on your dropdown list on design view.

    then do the things below:

    reportViewer1.Visible = true;
                reportViewer1.LocalReport.ReportPath = "~/Account/dropfilterstate.rdlc";
                reportViewer1.LocalReport.SetParameters(new ReportParameter("State",DropDownList2.SelectedValue.ToString()));

                reportViewer1.RefreshReport();

    Friday, July 1, 2011 12:06 PM
  • User-851246726 posted

    This is my .CS code right now and still I am not getting any output (blank report) on selecting any state from dropdown menu:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Reporting.WebForms;


    public partial class Account_dropfilterbystate : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            ReportViewer1.Visible = true;
            ReportViewer1.LocalReport.ReportPath = "~/Account/dropfilterstate.rdlc";
            ReportViewer1.LocalReport.SetParameters(new ReportParameter("State", DropDownList2.SelectedValue.ToString()));

            ReportViewer1.LocalReport.Refresh();
            
        }
    }

    FYI: When I tried this with "ReportViewer1.RefreshReport();"

    I was getting error saying:

    "Microsoft Reporting.webform.report viewer does not contain a definition for RefreshReport"

    So I changed it to "ReportViewer1.LocalReport.Refresh();"

    But as I said, still my report is blank.

    I tried my best above to explain you each and every step I did in my above post using screenshots. I hope it will give you the exact idea about all the steps I performed for this report.

    Please Help Me.

    Friday, July 1, 2011 12:25 PM
  • User-851246726 posted

    Ok I re-built my project this time and corrected the path of rdlc report.

    When I run the project, view the webpage in browser> as soon as I select any state in dropdown menu,

    I get this error:

    When I press F11, then I get this error:

    An attempt was made to set a report parameter 'State' that is not defined in this report.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownReportParameterException: An attempt was made to set a report parameter 'State' that is not defined in this report.

    Source Error:

    Line 18:         ReportViewer1.Visible = true;
    Line 19: ReportViewer1.LocalReport.ReportPath = "Account/dropfilterstate.rdlc";
    Line 20: ReportViewer1.LocalReport.SetParameters(new ReportParameter("State", DropDownList2.SelectedValue.ToString()));
    Line 21:
    Line 22: ReportViewer1.LocalReport.Refresh();
    Friday, July 1, 2011 12:45 PM
  • User2105670541 posted

    please try to check whether the parameter "State" exists in your .RDLC as report parameter.

    Friday, July 1, 2011 2:53 PM
  • User-851246726 posted

    Yes, you can see in the steps I postec above (in the screenshot), please see STEP 5 above.

    Friday, July 1, 2011 3:04 PM
  • User2105670541 posted

    that is the reason, your report dataset must take the State as a parameter to show records based on the state only from the database. hence you need to create a parameter clicking on the parameter folder and say add parameter.

    give this parameter name as State, in your dataset query make sure it takes the parameter State and then run the application once again, in case of any problem or doubt plese revert

    Saturday, July 2, 2011 12:33 AM
  • User-851246726 posted

    hi Ashutosh

    so I did what you mentioned above, but still I don't see anything in thr report on selection of any state from the dropdown menu.

    It's just that I am not getting any error message.

    This is what I did: PLEASE CORRECT ME IF I AM WRONG.

    Sunday, July 3, 2011 11:10 AM
  • User2105670541 posted

    Jeff can you please provide me more screenshots on your main datasets and the parameter its taking, and can you please test to run the query from the report designer and pass the state parameter, i just wanted to check whether you are getting any values or not. thanks

    Sunday, July 3, 2011 2:52 PM
  • User-851246726 posted

    hi Ashutosh,

    sorry but I have no idea what you are talking about. Above are all the screenshots for the parameter I added in the report designer.

    I don't see any option in report designer to run any query.

    On page 5, I posted all the screenshots- how I created the report and displaying it.

    One page 6 (above), I posted all the steps for adding parameter.

    still strugling with the same problem.

    Tuesday, July 5, 2011 1:05 PM
  • User-851246726 posted

    Is it possible for you to create a table schema, just like mine and test it and then we can discuss it so that we both are on the same page and you will be able to test it however you want.

    thanks

    Tuesday, July 5, 2011 4:36 PM
  • User2105670541 posted

    Jeff .. please see this space after 12 hrs arround 9.00 PM (GMT +5.30), i'll create a sample for you meeting your requirement. as I'm not in home right now to crate it. thanks :)

    Tuesday, July 5, 2011 11:08 PM
  • User-851246726 posted

    thanks Ashutosh, I will keep an eye here for your reply.

    Tuesday, July 5, 2011 11:46 PM
  • User2105670541 posted

    thanks Ashutosh, I will keep an eye here for your reply.

    Jeff, i've created a sample project matching your scenario, the db im using is Northwind, so you make sure to attach in your database server before using the project before, Northwind is a free datasource which you can download from internet. and the project, please download it from below link:

    https://skydrive.live.com/?cid=2A348EA2F152DA00&id=2A348EA2F152DA00!112&sc=documents

    please revert if you have any doubts :)

    Wednesday, July 6, 2011 11:12 AM
  • User-851246726 posted

    Hi ashutosh, if possible could you please post the screenshot so that I can get an idea for now and for future that what needs to be done first etc.

    And what if database is in .\SQLEXPRESS instance with no password and userID ?

    thanks

    Wednesday, July 6, 2011 11:27 AM
  • User2105670541 posted

    Jeff.. Im soo sorry i wont be able to post the screen-shots here, if you have any problem let me know i'll tell you..sorry again.

    and yes for the connection string stuff if you dont want to use password and all, use it like below:

    Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
    or
    erver=myServerAddress;Database=myDataBase;Trusted_Connection=True;

    Wednesday, July 6, 2011 11:42 AM
  • User-851246726 posted

    I am getting this error:

    Login failed for user 'sa'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'sa'.

    This is what I am using in "eportViewerPage.aspx"

    private void GetCityData()
            {
                DataSet _dset = new DataSet();
                SqlDataAdapter _sda = null;
                SqlConnection _con = new SqlConnection("data source=localhost; database=NorthWind;");
                _sda = new SqlDataAdapter("select distinct City from Employees", _con);
                _sda.Fill(_dset);
                ddlCity.DataSource = _dset;
                ddlCity.DataTextField = "City";
                ddlCity.DataValueField = "City";
                ddlCity.DataBind();
            }

            private void PopulateEmployeeReport(string _city)
            {
                ReportViewer1.ProcessingMode = ProcessingMode.Local;
                DataSet _dset = new DataSet();
                SqlDataAdapter _sda = null;
                SqlConnection _con = new SqlConnection("data source=localhost; database=NorthWind;");
                _sda = new SqlDataAdapter("GetEmployeeDetails", _con);
                _sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                _sda.SelectCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50)).Value = _city;
                _sda.Fill(_dset);
                ReportViewer1.LocalReport.DataSources.Clear();
                ReportDataSource _source = new ReportDataSource();
                _source.Name = "TestDset";
                _source.Value = _dset.Tables[0];

                ReportParameter _param = new ReportParameter("City", _city);
                ReportViewer1.LocalReport.DataSources.Add(_source);
                ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report1.rdlc");
                ReportViewer1.LocalReport.SetParameters(_param);
                ReportViewer1.LocalReport.Refresh();
                ReportViewer1.Visible = true;
            }

    This is my webconfig:

    <add name="NorthwindConnectionString" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\Northwind.mdf;User Instance=true" providerName="System.Data.SqlClient" />


    Wednesday, July 6, 2011 12:00 PM
  • User2105670541 posted

    ohh...instead of doing the connection manually try to replace the below lines from the code:

              SqlConnection _con = new SqlConnection("data source=localhost; database=NorthWind;");

    With:

    SqlConnection _con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString.ToString());

    Wednesday, July 6, 2011 12:26 PM
  • User-851246726 posted

    I did, still getting this:

    Login failed for user 'sa'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'sa'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


    Stack Trace:

    Wednesday, July 6, 2011 12:41 PM
  • User2105670541 posted

    did you do it on both the sql connection ??

    Wednesday, July 6, 2011 12:44 PM
  • User-851246726 posted

    yes, here's my code and I did re-compile the project:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.Reporting.WebForms;

    namespace ReportProjectASPNet
    {
        public partial class eportViewerPage : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!Page.IsPostBack)
                    GetCityData();
                ReportViewer1.Visible = true;
                ReportViewer1.LocalReport.Refresh();
            }

            protected void btnReport_Click(object sender, EventArgs e)
            {
                PopulateEmployeeReport(ddlCity.SelectedValue.ToString());
            }

            private void GetCityData()
            {
                DataSet _dset = new DataSet();
                SqlDataAdapter _sda = null;
                SqlConnection _con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString.ToString());
                _sda = new SqlDataAdapter("select distinct City from Employees", _con);
                _sda.Fill(_dset);
                ddlCity.DataSource = _dset;
                ddlCity.DataTextField = "City";
                ddlCity.DataValueField = "City";
                ddlCity.DataBind();
            }

            private void PopulateEmployeeReport(string _city)
            {
                ReportViewer1.ProcessingMode = ProcessingMode.Local;
                DataSet _dset = new DataSet();
                SqlDataAdapter _sda = null;
                SqlConnection _con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString.ToString());
                _sda = new SqlDataAdapter("GetEmployeeDetails", _con);
                _sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                _sda.SelectCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50)).Value = _city;
                _sda.Fill(_dset);
                ReportViewer1.LocalReport.DataSources.Clear();
                ReportDataSource _source = new ReportDataSource();
                _source.Name = "TestDset";
                _source.Value = _dset.Tables[0];

                ReportParameter _param = new ReportParameter("City", _city);
                ReportViewer1.LocalReport.DataSources.Add(_source);
                ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report1.rdlc");
                ReportViewer1.LocalReport.SetParameters(_param);
                ReportViewer1.LocalReport.Refresh();
                ReportViewer1.Visible = true;
            }
        }
    }

    This is my webconfig:

    <connectionStrings>
        <add name="NorthwindConnectionString" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\NORTHWND.MDF;User Instance=true" providerName="System.Data.SqlClient" />

      </connectionStrings>

    Wednesday, July 6, 2011 12:52 PM
  • User2105670541 posted

    it should run ideally, would you mind if you pass the user name and password and then check.

    Wednesday, July 6, 2011 1:37 PM
  • User-851246726 posted

    I will try that and definetly report back the output.

    Wednesday, July 6, 2011 2:03 PM
  • User2105670541 posted

    I will try that and definetly report back the output.

    Jeff, is it working fine?

    Thursday, July 14, 2011 12:12 AM
  • User-851246726 posted

    Sorry Ashutosh, I didn't reply back because suddenly there was some problem in my database. I will post back the result today or may be tommorow. Thanks

    Thursday, July 14, 2011 9:19 AM