The connection was not closed. The connection's current stat

Aug 18 2017 11:18 AM

Hello all

i am trying to print bills that were not disaproved my database has a column APPROVE_RECIEVABLES where each bill disapproved is assign the value of 'No'

i keep getting error """""The connection was not closed. The connection's current state is open. """"""""

Below is my code and my html
***************************************************************************
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Reciepts.aspx.cs" Inherits="WebApplication5.WebForm1" %>

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1 {
width: 100%;
height: 42px;
}
.auto-style2 {
height: 23px;
}
.auto-style10 {
width: 100%;
height: 29px;
}
.auto-style3 {
width: 158px;
height: 32px;
font-weight: 700;
color: #FFFFFF;
}
.auto-style11 {
height: 71px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="height: 260px">

<div class = "form-group">
<table class="auto-style1">
<tr>
<td class="auto-style2" style="background-color: #000099">  
<asp:LinkButton ID="LinkButton4" runat="server" ForeColor="White" PostBackUrl="~/GeneralReports.aspx">Back</asp:LinkButton>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblmessage" runat="server" Visible="False" ForeColor="Red"></asp:Label>
<asp:Label ID="lblprintmessage" runat="server" ForeColor="Red"></asp:Label>
</td>
</tr>
</table>
<label for = "firstname" class = "col-sm-2 control-label">
<br />
Pin Number<br />
<asp:TextBox ID="txtpin" runat="server" Width="257px"></asp:TextBox>
<br />
Start Date:</label>

<div class = "col-sm-10" style="margin-top: 5px; margin-bottom: 5px; background-image: url('images/logo.png');">
<asp:TextBox ID="txtstartdate" runat="server" class = "form-control" placeholder = "select start date" Width="250px" ValidationGroup="regval"></asp:TextBox>
<ajaxToolkit:CalendarExtender ID="CalendarExtender1" TargetControlID="txtstartdate" runat="server" />
</div>
</div>
<div class = "form-group">
<label for = "firstname" class = "col-sm-2 control-label">End Date:</label>

<div class = "col-sm-10" style="margin-top: 5px; margin-bottom: 5px; ">
<asp:TextBox ID="txtEndDate" runat="server" class = "form-control" placeholder = "select start date" Width="250px"></asp:TextBox>
<ajaxToolkit:CalendarExtender ID="CalendarExtender2" TargetControlID="txtEndDate" runat="server" />
<asp:Button ID="Btnsearch" runat="server" Text="Search" OnClick="Btnsearch_Click1" />
<asp:Button ID="Button1" runat="server" Text="Exit" />
<asp:HyperLink ID="HyperLink3" runat="server" NavigateUrl="~/GeneralReports.aspx">Back</asp:HyperLink>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
</div>
</div>
<div class = "form-group" style="margin-top: 10px; margin-bottom: 5px">
<div class = "col-sm-offset-2 col-sm-10" style="padding-left: 80px; margin-top: 5px; margin-bottom: 5px;">
<br />
</div>
</div>

</div>

<div>

<br />
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="947px" Height="362px">
<LocalReport ReportEmbeddedResource="WebApplication5.recieptsReport3.rdlc">
<DataSources>
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="lwscDataSetTableAdapters.BILLING_TABTableAdapter"></asp:ObjectDataSource>
<br />

</div>
</form>
<table class="auto-style1">
<tr>
<td class="auto-style11" style="background-color: #000099">                                                                          
<table class="auto-style10">
<tr>
<td style="background-color: #000099">                                                                                       <span class="auto-style3">Application is copyrighted @2017 Nitoks Consultants Ltd</span> </td>
</tr>
</table>
 </td>
</tr>
</table>
</body>
</html>

*****************************************************************************
Below is my c# code
*****************************************************************************

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

namespace WebApplication5
{
public partial class WebForm1 : System.Web.UI.Page
{
SqlConnection repcon = new SqlConnection(ConfigurationManager.ConnectionStrings["dbs"].ConnectionString);
//ReportDocument rpdc = new ReportDocument();
SqlConnection objCon = new SqlConnection(ConfigurationManager.ConnectionStrings["dbs"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (Session["role"].ToString() != "Admin")
{
Response.Redirect("Login.aspx");
}
}

protected void cmdsearch_Click(object sender, EventArgs e)
{
runRptViewer();
}
public DataTable getData()
{
//string check=Session["APPROVE_RECIEVABLES"].ToString();

//try
// {
//if(check!="no")
{
//#region open connection
//sqlcon strConn = “Data Source=SQLSERVERNAME;Initial Catalog=DATABASENAME;User ID=USERID;password=PASSWORD;”;
IFormatProvider enUSDateFormat = new CultureInfo("en-US").DateTimeFormat;
Convert.ToDateTime(txtstartdate.Text, enUSDateFormat);
Convert.ToDateTime(txtEndDate.Text, enUSDateFormat);

//objCon = new SqlConnection(strConn);
objCon.Open();
SqlCommand cmd;
String sql = "select PIN_NUM,CUST_NAME,CUST_ADDRESS,BUI_COD,PROP_TYPE,TARIFF_COD,BILL_DAT,ARREARS,PREPAY,cast(MONTHLY_BILL_DUE as decimal(10,2)) AS MONTHLY_BILL_DUE ,OTH_NAM,ZONE_NAME,CONN_STAT,DISC_STAT,CLSSFCTION_STATUS,LWSC_REG_STATUS,AJUSTMENT_FIGURE,AJUSTMENT_TYPE,BILLING_CATEGORY,OTHER_NAME,CONNECTION_STATUS,BUI_TYP,SUR_NAME,CUR_SNA,CUS_CLS,CUS_REG,APPROVE_RECIEVABLES,cast(AMOUNT_PAYABLE as decimal(10,2))AS AMOUNT_PAYABLE,SEWER_CHARGE,FIXED_CHARGE,GST_CHARGE,WATER_CHARGE,STAFF_NAME,login_time,logout_time FROM [lwsc].[dbo].[BILLING_TAB] where PIN_NUM LIKE @pin AND BILL_DAT between @startdate AND @enddate ";
//String sql = "select convert(varchar,BILL_DAT,103) BILL_DAT,PIN_NUM,CUST_NAME,CUST_ADDRESS,MONTHLY_BILL_DUE,ZONE_NAME,AJUSTMENT_FIGURE,AJUSTMENT_TYPE FROM BILLING_TAB WHERE PIN_NUM LIKE '@pinnum' AND BILL_DAT between @startdate AND @enddate";
//String sql = "select * from BILLING_TAB";
cmd = new SqlCommand(sql, objCon);
cmd.Parameters.Add("@pin", SqlDbType.VarChar).Value = txtpin.Text;
cmd.Parameters.Add("@startdate", SqlDbType.DateTime).Value = Convert.ToDateTime(txtstartdate.Text, enUSDateFormat);
cmd.Parameters.Add("@enddate", SqlDbType.DateTime).Value = Convert.ToDateTime(txtEndDate.Text, enUSDateFormat).AddDays(1);
// cmd.Parameters.Add("@approve",SqlDbType.VarChar).Value=Session["APPROVE_RECIEVABLES"].ToString();
SqlDataAdapter dta = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
dta.SelectCommand = cmd;
dta.SelectCommand.Connection = objCon;
dta.Fill(ds, "DataSet1");
Session["Count"] = ds.Tables[0].Rows.Count;
return ds.Tables[0];
}
}

// catch(Exception ex)
// {
//lblmessage.Text=ex.ToString();
// }
//}

//}
//}
private void runRptViewer()
{

this.ReportViewer1.Reset();
this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("recieptsReport3.rdlc");
ReportDataSource rds = new ReportDataSource("DataSet1", getData());
this.ReportViewer1.LocalReport.DataSources.Clear();
this.ReportViewer1.LocalReport.DataSources.Add(rds);
this.ReportViewer1.DataBind();
this.ReportViewer1.LocalReport.Refresh();
objCon.Close();
objCon.Dispose();
}

protected void cmdsearch_Click1(object sender, EventArgs e)
{
try
{
//// based on the scenerio of the check button disable the pinnumber button then call the various classes for the reports
//IFormatProvider enGBDateFormat = new CultureInfo("en-GB").DateTimeFormat;
//Convert.ToDateTime(txtstartdate.Text, enGBDateFormat);
//Convert.ToDateTime(txtEndDate.Text, enGBDateFormat);
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbs"].ConnectionString);
//con.Open();
//SqlCommand cmd;
////SqlCommand cmd3;
//String sql = "select * from BILLING_TAB where PIN_NUM LIKE '@pinnum' AND BILL_DAT between @startdate AND @enddate";

//cmd = new SqlCommand(sql, con);
//// cmd.CommandType = CommandType.StoredProcedure;
////string branchcode = drpbranch.SelectedValue;

//cmd.Parameters.Add("@pinnum", SqlDbType.VarChar).Value = txtpin.Text;
//cmd.Parameters.Add("@startdate", SqlDbType.DateTime).Value = Convert.ToDateTime(txtstartdate.Text, enGBDateFormat);
//cmd.Parameters.Add("@enddate", SqlDbType.DateTime).Value = Convert.ToDateTime(txtEndDate.Text, enGBDateFormat).AddDays(1);
//cmd.ExecuteNonQuery();
//SqlDataAdapter adap = new SqlDataAdapter(cmd);
//DataSet ds = new DataSet();
//adap.Fill(ds, "DataSet1");
//DataTable dt = new DataTable();
//dt = ds.Tables[0];
//if (dt.Rows.Count > 0)
//{
// lblmessage.Text = "Loading.....";
//}
//else
//{
// lblmessage.Text = "No records found...";
// //btnUpload.Visible = false;
// // ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('No record found ');", true);
//}
//ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report3.rdlc");
//ReportViewer1.ProcessingMode = ProcessingMode.Local;
//ReportViewer1.LocalReport.DataSources.Clear();
//ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", ds.Tables[0]));
////ReportParameter startdate = new ReportParameter("startDate", Convert.ToDateTime(txtStartDate.Text, enGBDateFormat).ToString());
////tryBalancerpt.LocalReport.SetParameters(new ReportParameter[] { startdate });
////ReportParameter enddate = new ReportParameter("enddate", Convert.ToDateTime(txtEndDate.Text, enGBDateFormat).ToString());
////tryBalancerpt.LocalReport.SetParameters(new ReportParameter[] { enddate });
//ReportViewer1.LocalReport.Refresh();
// con.Close();
runRptViewer();
}
catch (Exception ex)
{
}
}

protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("GeneralReports.aspx");
}

protected void Btnsearch_Click(object sender, EventArgs e)
{

}

protected void Btnsearch_Click1(object sender, EventArgs e)
{
if ((txtEndDate.Text == "") && (txtpin.Text == "") && (txtstartdate.Text == ""))
{
lblmessage.Text = "You have not Entered data";
lblprintmessage.Text = "You have not Entered your full data";

}
else
{
string test = getData().ToString();
if (Convert.ToInt16(Session["Count"]) == 0)
{
lblmessage.Text = "Cannot print";
lblprintmessage.Text = "This customer is not approved for Printing";

}
else
{
runRptViewer();
}
}
}
}
}

Regards
Chinedu Nwankwo

Answers (4)

Asp.Net Globalization and Localization

Adding multiple images to blog post