C# School

Friday, April 3, 2015

Bulk edit and update in asp.net Gridview

Bulk edit and update in asp.net gridview

Here I am going to explain how to bulk edit update multiple rows or records in asp.net gridview using checkboxes on single click and c# sharp

ASP.NET

<asp:GridView ID="gvcallingstatus" runat="server" AutoGenerateColumns="False" CssClass="GridViewStyle"
DataKeyNames="ID" AllowPaging="True" PageSize="12"
onrowdatabound="gvcallingstatus_RowDataBound"
onpageindexchanging="gvcallingstatus_PageIndexChanging"
DataSourceID="sqlgvdatabind">
<pagerstyle backcolor="#336666" forecolor="White" horizontalalign="Center" />
<selectedrowstyle backcolor="#339966" font-bold="True" forecolor="White" />
<headerstyle backcolor="#336666" font-bold="True" forecolor="White" />
<alternatingrowstyle cssclass="AltRowStyle" />
<headerstyle wrap="false" />
<columns>

<asp:templatefield>
<itemtemplate>
<asp:CheckBox ID="chkedit" runat="server" AutoPostBack="true" OnCheckedChanged="ChkEdit_Checked" EnableViewState="true" />
</itemtemplate>
</asp:templatefield>

<asp:templatefield headertext="ID">
<itemtemplate>
<asp:TextBox ID="txtid" runat="server" ReadOnly="true" BackColor="Transparent" BorderStyle="None" Width="50px" Text='<%# Bind("ID") %>' CssClass="GVItemStyle"></asp:TextBox>
</itemtemplate>
</asp:templatefield>

<asp:templatefield headertext="Status">
<itemtemplate>
<asp:TextBox ID="txtremarks" runat="server" ReadOnly="true" BackColor="Transparent" BorderStyle="None" Text='<%# Bind("Remarks") %>' CssClass="GVItemStyle"></asp:TextBox>
<asp:DropDownList ID="ddlremarks" runat="server" AppendDataBoundItems="true" Visible="false" ToolTip='<%# Bind("Region") %>' AutoPostBack="true" OnSelectedIndexChanged="ddlremarks_SelectedIndexChanged">
<asp:listitem selected="True"></asp:listitem>
<asp:listitem>Busy / Engaged</asp:listitem>
<asp:listitem>Call Later</asp:listitem>
</asp:DropDownList>
</itemtemplate>
<itemstyle wrap="False" />
</asp:templatefield>

<asp:templatefield headertext="TC">
<itemtemplate>
<asp:TextBox ID="txttc" runat="server" ReadOnly="true" BackColor="Transparent" BorderStyle="None" Text='<%# Bind("TC") %>' Width="100%" CssClass="GVItemStyle"></asp:TextBox>
<asp:DropDownList ID="ddltc" runat="server" Visible="false" Width="150px" AppendDataBoundItems="true" EnableViewState="true">
</asp:DropDownList>
</itemtemplate>
<itemstyle width="10%" />
</asp:templatefield>

<asp:templatefield headertext="Rejection Reason">
<itemtemplate>
<asp:TextBox ID="txtrr" runat="server" Height="20px" ReadOnly="true" TextMode="MultiLine" BackColor="Transparent" BorderStyle="None" Text='<%# Bind("RReason") %>' Width="100%" CssClass="GVItemStyle"></asp:TextBox>
<asp:DropDownList ID="ddlrr" runat="server" Visible="false" Width="150px" AppendDataBoundItems="true" EnableViewState="true">
<asp:listitem selected="True"></asp:listitem>
<asp:listitem>Call were not recorded</asp:listitem>
<asp:listitem>Incomplete call</asp:listitem>
<asp:listitem>Language Problem</asp:listitem>
<asp:listitem>Permission not taken regarding recording of calls</asp:listitem>
<asp:listitem>Ratings not explained properly</asp:listitem>
<asp:listitem>Some questions skipped</asp:listitem>
</asp:DropDownList>
</itemtemplate>
<itemstyle width="20%" />
</asp:templatefield>

</columns>
<editrowstyle cssclass="EditRowStyle" />
<headerstyle cssclass="HeaderStyle" />
<pagerstyle cssclass="PagerStyle" />
<rowstyle cssclass="RowStyle" />
<selectedrowstyle cssclass="SelectedRowStyle" />

</asp:GridView>

C# SHARP

protected void Btnchkall_Click(object sender, EventArgs e)

{

if (btnchkall.Text.ToString() == "Check All")

{

for (int i = 0; i < gvcallingstatus.Rows.Count; i++)

{

CheckBox chkedit = (CheckBox)gvcallingstatus.Rows[i].Cells[0].FindControl("chkedit");

chkedit.Checked = true;

TextBox txtremarks = (TextBox)gvcallingstatus.Rows[i].FindControl("txtremarks");

TextBox txttc = (TextBox)gvcallingstatus.Rows[i].FindControl("txttc");

DropDownList ddlremarks = (DropDownList)gvcallingstatus.Rows[i].FindControl("ddlremarks");

DropDownList ddltc = (DropDownList)gvcallingstatus.Rows[i].FindControl("ddltc");

txtremarks.Visible = false;

ddlremarks.Visible = true;

txttc.Visible = false;

ddltc.Visible = true;

//Telecalelr Name

using (SqlConnection TCCon = new SqlConnection(QualitySQL))

{

ViewState["DDLTC"] = ddltc.SelectedValue.ToString();

TCCon.Close();

TCCon.Open();

//Telecaller name data bindings

string SQLTC = "Select * from TC where Branch = '" + Session["Zones"].ToString() + "'";

SqlCommand CMDTC = new SqlCommand(SQLTC, TCCon);

ddltc.Items.Clear();

ddltc.DataSource = CMDTC.ExecuteReader();

ddltc.DataTextField = "Telecaller";

ddltc.DataValueField = "Telecaller";

ddltc.DataBind();

ddltc.Items.Insert(0, new ListItem("", ""));

//End of telecaller name data bindings

TCCon.Close();

}

btnchkall.Text = "Clear All";

}

else if (btnchkall.Text.ToString() == "Clear All")

{

for (int i = 0; i < gvcallingstatus.Rows.Count; i++)

{

CheckBox chkedit = (CheckBox)gvcallingstatus.Rows[i].Cells[0].FindControl("chkedit");

chkedit.Checked = false;

TextBox txtremarks = (TextBox)gvcallingstatus.Rows[i].FindControl("txtremarks");

TextBox txttc = (TextBox)gvcallingstatus.Rows[i].FindControl("txttc");

DropDownList ddlremarks = (DropDownList)gvcallingstatus.Rows[i].FindControl("ddlremarks");

DropDownList ddltc = (DropDownList)gvcallingstatus.Rows[i].FindControl("ddltc");

txtremarks.Visible = true;

ddlremarks.Visible = false;

txttc.Visible = true;

ddltc.Visible = false;

btnchkall.Text = "Check All";

}

protected void BtnUpdate_Click(object sender, EventArgs e)

{

foreach(GridViewRow row in gvcallingstatus.Rows)

{

if (row.RowType == DataControlRowType.DataRow)

{

bool chkedit = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;

if (chkedit != null)

{

if (chkedit)

{

DropDownList ddlremarks = (DropDownList)row.FindControl("ddlremarks");

DropDownList ddltc = (DropDownList)row.FindControl("ddltc");

DateTime dateval = DateTime.Now;

SqlConnection DBCon = new SqlConnection(QualitySQL);

SqlCommand DBCmd = new SqlCommand();

DBCon.Close();

DBCon.Open();

//Remarks and Telecaller name updating

if (ddlremarks.SelectedValue.ToString() != "" & ddlremarks.SelectedValue.ToString() != "Rejected" & ddlremarks.SelectedValue.ToString() != "Call Later")

{

if (ddltc.SelectedValue.ToString() != "")

{

double id = Convert.ToDouble(((TextBox)row.FindControl("txtid")).Text.ToString());

DBCmd.Connection = DBCon;

DBCmd.CommandText = "Update JDPDB SET Remarks=@Remarks, TC=@TC, RReason=@RReason, RemarksDate=@RemarksDate where ID = " + id;

DBCmd.CommandType = CommandType.Text;

DBCmd.Parameters.Add(new SqlParameter("@Remarks", row.Cells[11].Controls.OfType<DropDownList>().FirstOrDefault().SelectedValue.ToString()));

DBCmd.Parameters.Add(new SqlParameter("@TC", row.Cells[13].Controls.OfType<DropDownList>().FirstOrDefault().SelectedValue.ToString()));

DBCmd.Parameters.Add(new SqlParameter("@RReason", ""));

DBCmd.Parameters.Add(new SqlParameter("@RemarksDate", dateval));

DBCmd.ExecuteNonQuery();

gvcallingstatus.DataBind();

btnchkall.Text = "Check All";

}

else { ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('Telecaller Name can not be blank');", true); ddltc.Focus(); }

}

DBCon.Close();

}

Thursday, April 2, 2015

Cascading Dropdownlist with viewstate

Here I am going explain about Cascading Dropdownlist with viewstate (which will retain the last value even on postback)

ASP.NET CODE

<asp:DropDownList ID="ddlcategory" runat="server" AutoPostBack="true"
CssClass="mydropdown" Width="120px" EnableViewState="true"
onselectedindexchanged="ddlcategory_SelectedIndexChanged">
<asp:ListItem Value="%">All</asp:ListItem>
</asp:DropDownList>

</asp:Label>
<asp:DropDownList ID="ddltodf" runat="server" AutoPostBack="true"
CssClass="mydropdown" Width="120px" EnableViewState="true">
<asp:ListItem Value="%">All</asp:ListItem>

</asp:DropDownList>

C# CODE

using (OleDbConnection DDLCon = new OleDbConnection(AZRKC))
{
//ViewStates
ViewState["DDLCategory"] = ddlcategory.SelectedValue.ToString();
ViewState["DDLDataFile"] = ddltodf.SelectedValue.ToString();
//End of View states

string SDDLCategory;
string SDDLDataFile;

SDDLCategory = "Select DISTINCT Category from AZRKC where TODF like '" + ddltodf.SelectedValue.ToString() + "' and SOData like '" + ddlsod.SelectedValue.ToString() + "' and Years like '" + ddlyear.SelectedValue.ToString() + "' and Months like '" + ddlmonth.SelectedValue.ToString() + "' and Uploaded like '" + ddluploaded.SelectedValue.ToString() + "'";

SDDLDataFile = "Select DISTINCT [TODF] from AZRKC where Category like '" + ddlcategory.SelectedValue.ToString() + "' and SOData like '" + ddlsod.SelectedValue.ToString() + "' and Years like '" + ddlyear.SelectedValue.ToString() + "' and Months like '" + ddlmonth.SelectedValue.ToString() + "' and Uploaded like '" + ddluploaded.SelectedValue.ToString() + "'";

DDLCon.Close();
DDLCon.Open();

//Category data bindings
OleDbCommand CMDCategory = new OleDbCommand(SDDLCategory, DDLCon);
ddlcategory.Items.Clear();
ddlcategory.DataSource = CMDCategory.ExecuteReader();
ddlcategory.DataTextField = "Category";
ddlcategory.DataValueField = "Category";
ddlcategory.DataBind();
ddlcategory.Items.Insert(0, new ListItem("All", "%"));
if (ViewState["DDLCategory"] != null)
{
ddlcategory.SelectedValue = ViewState["DDLCategory"].ToString();
}
//End of Category data bindings

//Type DataFile bindings
OleDbCommand CMDDataFile = new OleDbCommand(SDDLDataFile, DDLCon);
ddltodf.Items.Clear();
ddltodf.DataSource = CMDDataFile.ExecuteReader();
ddltodf.DataTextField = "TODF";
ddltodf.DataValueField = "TODF";
ddltodf.DataBind();
ddltodf.Items.Insert(0, new ListItem("All", "%"));
if (ViewState["DDLDataFile"] != null)
{
ddltodf.SelectedValue = ViewState["DDLDataFile"].ToString();
}

//End of DataFile bindings

DDLCon.Close();

}

HOPE THIS WILL HELP YOU, IF YOU HAVE ANY QUERY PLEASE REVERT

Send eMail in HTML format by using Gridview data

Here I am explaining about how to send eMail of Gridview data via ASP.NET and C#

C# CODE

CLASS LIBRARY

using System.Net;

using System.Net.Mail;

using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
gvcsbytime.RenderControl(hw);
StringReader sr = new StringReader(sw.ToString());
MailMessage mm = new MailMessage("from@mail.in", "to@mail.in");
mm.Subject = "Calls Status as on " + DateTime.Now.Date.Day + "-" + DateTime.Now.Date.Month + "-" + DateTime.Now.Date.Year + " " + DateTime.Now.TimeOfDay.Hours + "-" + DateTime.Now.TimeOfDay.Minutes + "-" + DateTime.Now.TimeOfDay.Seconds;
mm.Body = "Dear " + Session["Query"].ToString() + ", <br> <br> Please find below the Calls status as on " + DateTime.Now.Date.Day + "-" + DateTime.Now.Date.Month + "-" + DateTime.Now.Date.Year + " " + DateTime.Now.TimeOfDay.Hours + "-" + DateTime.Now.TimeOfDay.Minutes + "-" + DateTime.Now.TimeOfDay.Seconds + "<hr/>" + sw.ToString() + "<br> <br> This is a self generated mail. Please do not reply on this ID <br> <br> MIS";
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.bizmail.yahoo.com";
smtp.EnableSsl = true;
System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();
NetworkCred.UserName = "from@mail.in";
NetworkCred.Password = "password";
smtp.UseDefaultCredentials = true;
smtp.Credentials = NetworkCred;
smtp.Port = 587;
smtp.Send(mm);
}

}

Hope this will help you, if you have any query please revert.

Live search on gridview by selected fields

In my previous post I explained about selected fields to show in Gridview, now I am going to explain how to filter the data by selected fields using textbox.

asp.net code

<select id="selectfields2" name="selectfields" data-width="100%" multiple class="selectpicker show-tick" data-live-search="true" data-style="btn-danger" title="Select fields" enableviewstate="true">
<option value="*">Select All</option>
<option value="Address">Address</option>

</select>

<asp:TextBox ID="txtfilter" runat="server" OnTextChanged="txtfilter_TextChanged" AutoPostBack="true" CssClass="form-control" placeholder=" search here"></asp:TextBox>
<asp:HiddenField ID="hdfields" runat="server" />

<asp:HiddenField ID="hdfieldsquery" runat="server" />

C# CODE

protected void txtfilter_TextChanged(object sender, EventArgs e)
{
string FieldName2 =  Page.Request.Form["selectfields"].ToString();
string[] SplitFieldName2 = FieldName2.Split(',');
foreach(string st in SplitFieldName2)
{
hdfieldsquery.Value = hdfieldsquery.Value + st + " like '%" + txtfilter.Text.ToString() + "%' or ";
}

if (FieldName2.ToString() != "*")
{
if (hdfields.Value.ToString() != "")
{
string SQLQuery2 = "Select * from FinalData where " + hdfieldsquery.Value.ToString() + "IDS like '%" + txtfilter.Text.ToString() + "%' or University like '%" + txtfilter.Text.ToString() + "%' or State like '%" + txtfilter.Text.ToString() + "%' order by IDS ASC";
  SqlCommand CMD = new SqlCommand(SQLQuery2);
gv.DataSource = GetData(CMD);
gv.DataBind();

hdfieldsquery.Value = null;
}

}
}
}

Hope the above code will help you, if you have any query please revert.

Display only selected fields in Gridview using c#

Display only selected fields in Gridview

Here I am going to explain you, how to show only selected fields in Gridview using dropdown and command button

Here we go: Dropdown with field names

<asp:DropDownList ID="ddlsearch" runat="server" CssClass="selectpicker show-tick pull-right" data-live-search="true">
<asp:ListItem Text="Field 1" Value="Field 1"></asp:ListItem>
<asp:ListItem Text="Field 2" Value="Field 2"></asp:ListItem>
</asp:DropDownList>

<asp:Button ID="btnsf" runat="server" CssClass="btn btn-danger" Text="Search database" OnClick="btnsf_Click" />

C# CODE

string FieldName = Page.Request.Form["selectfields"].ToString();
string[] SplitFieldName = FieldName.Split(',');

if (FieldName.ToString() != "*")
{
if (SplitFieldName.Length < 11)
{
BindData();

if (FieldName.IndexOf("Field 1") != -1) { gv.Columns[3].Visible = true; } else { gv.Columns[3].Visible = false; }
if (FieldName.IndexOf("Field 2") != -1) { gv.Columns[5].Visible = true; } else { gv.Columns[5].Visible = false; }

}
else
{
string Alerts = "alert('Maximum fields can be select upto 10')";
ScriptManager.RegisterClientScriptBlock((sender as Control), this.GetType(), "alert", Alerts, true);
gv.DataBind();

}

I HOPE THIS WILL HELP YOU, IF YOU HAVE ANY QUERY PLEASE REVERT.