C# ASP.net RDLC Report with DropdownList Parameter and Store

Question

I am working on RDLC report development with c# asp.net and SQl server Store procedure .While testing the report in the browser the Report viewer is not displaying only Dropdown list buttons are working fine with respective data but viewer is not showing any data even there is no any errors showing .My codeare as under:

 

aspx.cs code :--

 

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 Reportingtest

{

public partial class Report : System.Web.UI.Page

{

static DataTable DatTab { get; set; }

//DataSet dt

public static string StateName { get; set; }

public static bool flag { get; set; }

public static SqlConnection con = new SqlConnection(@"Data Source=GIS-SERVER;Initial Catalog=MIDB;Integrated Security=True;");

private static string DistrictName { get; set; }

private static string Date { get; set; }

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

loaddlStateName();

loadddldistrict();

loadddlDate();

GetData();

}

}

public void loaddlStateName()

{

try

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

SqlCommand com = new SqlCommand("Select StateName From MIREPORTFINAL GROUP BY StateName", con);

SqlDataAdapter da = new SqlDataAdapter(com);

DataSet ds = new DataSet();

da.Fill(ds);

ddlStateName.DataTextField = ds.Tables[0].Columns["StateName"].ToString();

ddlStateName.DataSource = ds.Tables[0];

ddlStateName.DataBind();

ddlStateName.Items.Insert(0, "All");

}

catch (Exception)

{

}

}

public void loadddldistrict()

{

try

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

SqlCommand com = new SqlCommand("Select DistrictName From MIREPORTFINAL GROUP BY DistrictName", con);

SqlDataAdapter da = new SqlDataAdapter(com);

DataSet ds = new DataSet();

da.Fill(ds); // fill dataset

ddldistrict.DataTextField = ds.Tables[0].Columns["DistrictName"].ToString(); //text field name of table dispalyed in dropdown

ddldistrict.DataSource = ds.Tables[0]; //assigning datas ource to the dropdownlist.

ddldistrict.DataBind(); //binding dropdownlist

ddldistrict.Items.Insert(0, "All");

}

catch (Exception)

{

}

}

public void loadddlDate()

{

try

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

SqlCommand com = new SqlCommand("Select Date From MIREPORTFINAL GROUP BY Date", con);

SqlDataAdapter da = new SqlDataAdapter(com);

DataSet ds = new DataSet();

da.Fill(ds);

ddlDate.DataTextField = ds.Tables[0].Columns["Date"].ToString(); //text field name of table dispalyed in dropdown

ddlDate.DataSource = ds.Tables[0]; //assigning data source to the dropdownlist.

ddlDate.DataBind(); //binding dropdownlist

ddlDate.Items.Insert(0, "All");

}

catch (Exception)

{

}

}

public static DataTable GetData()

{

try

{

DatTab = new DataTable();

if (!flag)

{

SqlCommand cmd = new SqlCommand("Select*FROM MIREPORTFINAL", con);

//cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adap = new SqlDataAdapter(cmd);

adap.Fill(DatTab);

con.Close();

}

else

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

using (SqlCommand cmd = new SqlCommand("DailyMIReportFinal1", con))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@StateName", SqlDbType.NVarChar)).Value = Convert.ToString(StateName) == "All" ? "-1" : Convert.ToString(StateName);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@DistrictName", SqlDbType.NVarChar)).Value = Convert.ToString(DistrictName) == "All" ? "-1" : Convert.ToString(DistrictName);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime)).Value = Convert.ToString(Date) == "All" ? "-1" : Convert.ToString(Date);

cmd.ExecuteNonQuery();

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(DatTab);

con.Close();

}

}

con.Close();

}

catch (Exception ex)

{

con.Close();

}

return DatTab;

}

protected void btnReport_Click(object sender, EventArgs e)

{

flag = true;

StateName = ddlStateName.Text.Trim();

DistrictName = ddldistrict.Text.Trim();

Date = ddlDate.Text.Trim();

GetData();

ReportViewer1.LocalReport.Refresh();

}

}

}

 

 

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

 

Aspx code :

 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Report.aspx.cs" Inherits="Reportingtest.Report" %>

<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div />

<div>

<label>State Name:</label>

<asp:DropDownList ID="ddlStateName" runat="server" AutoPostBack="True">

<asp:ListItem>All</asp:ListItem>

<asp:ListItem></asp:ListItem>

<asp:ListItem></asp:ListItem>

<asp:ListItem></asp:ListItem>

</asp:DropDownList>

<label>DistrictName</label>

<asp:DropDownList ID="ddldistrict" runat="server" Height="17px"

style="margin-left: 28px" Width="91px" AutoPostBack="True">

<asp:ListItem>All</asp:ListItem>

</asp:DropDownList>

<label>&nbsp;Date</label><asp:DropDownList ID="ddlDate" runat="server" Height="20px"

style="margin-left: 30px" Width="112px" AutoPostBack="True">

<asp:ListItem>All</asp:ListItem>

</asp:DropDownList>

<br />

&nbsp;<asp:Button ID="btnReport" runat="server" Text="Show Report" OnClick="btnReport_Click" Width="153px" />

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"

Font-Size="8pt" InteractiveDeviceInfos="(Collection)"

WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="987px" Visible="False">

<LocalReport ReportPath="Report.rdlc">

<DataSources>

<rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" />

</DataSources>

</LocalReport>

</rsweb:ReportViewer>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"

SelectMethod="GetData"

TypeName="Reportingtest.DataSet1TableAdapters.DailyMIReportFinal1TableAdapter"

OldValuesParameterFormatString="original_{0}">

<SelectParameters>

<asp:Parameter Name="StateName" Type="String" />

<asp:Parameter Name="DistrictName" Type="String" />

<asp:Parameter Name="Date" Type="DateTime" />

</SelectParameters>

</asp:ObjectDataSource>

<br />

</div>

</form>

</body>

</html>

Store procedure :

 

USE [MIDB]

GO

/****** Object: StoredProcedure [dbo].[DailyMIReportFinal1] Script Date: 20-05-2015 12:54:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure [dbo].[DailyMIReportFinal1]

@StateName NVARCHAR (50),

@DistrictName NVARCHAR (50),

@Date DateTime

As

Select [StateName],[DistrictName],[Date]

,SUM(Convert(int,[NoOfChildrenVaccinated])) AS [Total_No_of_Children_Vaccinated],SUM(Convert(int,[NoOfChildrenVaccinated])) As [Total_No_of_Session_Held],[StateName] As [StateName],[DistrictName] As [DistrictName]

FROM MIREPORTFINAL

Where (MIREPORTFINAL.[StateName]=@StateName or @StateName='-1')

and ([DistrictName]=@DistrictName or @DistrictName='-1')

and ([Date]=@Date or @Date='-1')

GROUP BY [StateName],[Date],DistrictName

GO