TechWorld - @h!: March 2019

Thursday, 28 March 2019

If u care for others....

If you care for your brain - sleep for 8hours
If you care for your eyes - massage your feet with oil before going to bed
If you care for your stomach - avoid cold food
If you care for your liver - avoid excessive fatty food
If you care for your intestine - replace junk food with vegetables
If you care for your kidney - drink a lot of water during the day
If you care for your kidney - drink less water at night
If you care for your kidney - empty your bladder before going to bed
If you care for your urinary tract - use raw onion regularly
If you care for your menstruation - use green gram regularly
If you care for your appendix - use lemon juice frequently
If you care for your throat - use pepper frequently
If you care for your lungs - avoid smoking
If you care for your heart - avoid excess salt
If you care for your mouth - gargle frequently with gingelly(sesame) oil
If you care for your nose - eat mint regularly
If you care for your ears - pour garlic mixed oil in ears frequently

How to take backup of a table with data in it

Right click on the database.

select the tables you want to take the backup

Next => Next => Finish

Then you will get a .sql file. When ever you need the data you can execute this file in SQL management studio.

Wednesday, 13 March 2019

Setting Tab in Gridview row wise

protected void Gridview1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DropDownList ddl = e.Row.FindControl("txtdes") as DropDownList;
TextBox txt1 = e.Row.FindControl("txtquan") as TextBox;
TextBox txt2 = e.Row.FindControl("ddlDay") as TextBox;
TextBox txt3 = e.Row.FindControl("txtTotal") as TextBox;
int i = e.Row.RowIndex * 4;
ddl.Attributes.Add("tabindex", (i + 1).ToString());
txt1.Attributes.Add("tabindex", (i + 2).ToString());
txt2.Attributes.Add("tabindex", (i + 3).ToString());
txt3.Attributes.Add("tabindex", (i + 4).ToString());
if (e.Row.RowIndex == 0)
ddl.Focus();
}
}

Static Billing model (5 rows)

Design page :

<asp:gridview ID="Gridview1" runat="server" ShowFooter="True" Width="600px" AutoGenerateColumns="False" BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellPadding="4" ForeColor="Black" CellSpacing="2" OnRowDataBound="Gridview1_RowDataBound">
<Columns>
<%--<asp:TemplateField HeaderText="Quantity">
<ItemTemplate>
<asp:ImageButton ID="ImageButton1" Visible="false" runat="server" />
</ItemTemplate>
</asp:TemplateField>--%>
<asp:TemplateField HeaderText="Product Name">
<ItemTemplate>
<asp:DropDownList ID="txtdes" runat="server" AutoPostBack="True" ValidationGroup="g">
</asp:DropDownList>
 
<asp:RequiredFieldValidator ID="RequiredFieldValidator14" runat="server" ControlToValidate="txtdes" ErrorMessage="*" ForeColor="#CC3300" InitialValue="Select Product" ValidationGroup="g"></asp:RequiredFieldValidator>
</ItemTemplate>
<ControlStyle Width="150px" />
<ItemStyle Width="10px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Quantity">
<ItemTemplate>
<asp:TextBox ID="txtquan" onkeypress="return validate(event)" runat="server" AutoPostBack="True" OnTextChanged="txtquan_TextChanged" ValidationGroup="g">0</asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator11" runat="server" ControlToValidate="txtquan" ErrorMessage="*" ForeColor="#CC3300" ValidationGroup="g"></asp:RequiredFieldValidator>
</ItemTemplate>
<ControlStyle Width="150px" />
<ItemStyle Width="10px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Price">
<ItemTemplate>
<asp:TextBox ID="ddlDay" runat="server" ValidationGroup="g" OnTextChanged="ddlDay_TextChanged" AutoPostBack="True">0</asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator12" runat="server" ControlToValidate="ddlDay" ErrorMessage="*" ForeColor="#CC3300" ValidationGroup="g"></asp:RequiredFieldValidator>
</ItemTemplate>
<ControlStyle Width="150px" />
<ItemStyle Width="10px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Total">
<ItemTemplate>
<asp:TextBox ID="txtTotal" runat="server" ValidationGroup="g" AutoPostBack="True" ReadOnly="True">0</asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server" ControlToValidate="txtTotal" ErrorMessage="*" ForeColor="#CC3300" ValidationGroup="g"></asp:RequiredFieldValidator>
</ItemTemplate>
<ControlStyle Width="150px" />
<FooterStyle HorizontalAlign="Right" />
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCCCC" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
<RowStyle BackColor="White" />
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#808080" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#383838" />
</asp:gridview>

C# page :

Namespace :

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

Paste in page load:

if (!IsPostBack)
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add(new DataColumn("Cname", typeof(String)));
dt.Columns.Add(new DataColumn("Qty", typeof(String)));
dt.Columns.Add(new DataColumn("Price", typeof(String)));
dt.Columns.Add(new DataColumn("Total", typeof(String)));
dr = dt.NewRow();
dr["Cname"] = string.Empty;
dr["Qty"] = string.Empty;
dr["Price"] = string.Empty;
dr["Total"] = string.Empty;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["Cname"] = string.Empty;
dr["Qty"] = string.Empty;
dr["Price"] = string.Empty;
dr["Total"] = string.Empty;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["Cname"] = string.Empty;
dr["Qty"] = string.Empty;
dr["Price"] = string.Empty;
dr["Total"] = string.Empty;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["Cname"] = string.Empty;
dr["Qty"] = string.Empty;
dr["Price"] = string.Empty;
dr["Total"] = string.Empty;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["Cname"] = string.Empty;
dr["Qty"] = string.Empty;
dr["Price"] = string.Empty;
dr["Total"] = string.Empty;
dt.Rows.Add(dr);
Gridview1.DataSource = dt;
Gridview1.DataBind();
ddl();
GrandTotal();
}
}

public void ddl() //to bind product in dropdown
{
foreach (GridViewRow row in Gridview1.Rows)
{
DropDownList ddl = (DropDownList)row.FindControl("txtdes");

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcn"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select distinct(Cname) from Table3", con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
ddl.Items.Clear();
ddl.Items.Add("-Select Share-");

SqlDataReader rd = cmd.ExecuteReader();

while (rd.Read())
{
ddl.Items.Add(rd[0].ToString());

}
string product = ddl.Text;
}
}

protected void Button1_Click(object sender, EventArgs e)
{
string name = DropDownList4.Text;
foreach (GridViewRow row in Gridview1.Rows)
{
DropDownList ddl = (DropDownList)row.FindControl("txtdes");
string product = ddl.Text;
if (product == "-Select Share-")
{
}
else
{
TextBox txt1 = (TextBox)row.FindControl("txtquan");
string quantity = txt1.Text;
TextBox txt2 = (TextBox)row.FindControl("ddlDay");
string price = txt2.Text;
TextBox txt3 = (TextBox)row.FindControl("txtTotal");
string total = txt3.Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcn"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("insert into Table4 values('" + name + "','" + product + "','" + quantity + "','" + price + "','" + total + "')", con);
cmd.ExecuteNonQuery();
con.Close();
}

}
Response.Redirect("AddTransactions.aspx");
}

private void GrandTotal() //to find total dynamically in footer
{
int grandtotal = 0;
int n;
foreach (GridViewRow row in Gridview1.Rows)
{
//n = Convert.ToInt32(row.Cells[3].Text);
TextBox tb = (TextBox)row.FindControl("txtTotal");
n = Convert.ToInt32(tb.Text);
grandtotal = grandtotal + n; //Where Cells is the column. Just changed the index of cells
}
Gridview1.FooterRow.Cells[2].Text = "Grand Total";
Gridview1.FooterRow.Cells[3].Text = grandtotal.ToString();
}

protected void txtquan_TextChanged(object sender, EventArgs e)
{
foreach (GridViewRow row in Gridview1.Rows)
{
int quantity = 0;
int price = 0;
int total = 0;
TextBox txt1 = (TextBox)row.FindControl("txtquan");
quantity = Convert.ToInt32(txt1.Text);
TextBox txt2 = (TextBox)row.FindControl("ddlDay");
price = Convert.ToInt32(txt2.Text);
TextBox txt3 = (TextBox)row.FindControl("txtTotal");
total = (Convert.ToInt32(quantity) * Convert.ToInt32(price));
txt3.Text = total.ToString();
GrandTotal();
}
}

protected void ddlDay_TextChanged(object sender, EventArgs e)
{
foreach (GridViewRow row in Gridview1.Rows)
{
//TextBox txt1 = (TextBox)row.FindControl("txtquan");
//string quantity = txt1.Text;
//TextBox txt2 = (TextBox)row.FindControl("ddlDay");
//string price = txt2.Text;
//TextBox txt3 = (TextBox)row.FindControl("txtTotal");
//string total = (Convert.ToInt32(quantity) * Convert.ToInt32(price)).ToString();
//txt3.Text = total;
////GrandTotal();
int quantity = 0;
int price = 0;
int total = 0;
TextBox txt1 = (TextBox)row.FindControl("txtquan");
quantity = Convert.ToInt32(txt1.Text);
TextBox txt2 = (TextBox)row.FindControl("ddlDay");
price = Convert.ToInt32(txt2.Text);
TextBox txt3 = (TextBox)row.FindControl("txtTotal");
total = (Convert.ToInt32(quantity) * Convert.ToInt32(price));
txt3.Text = total.ToString();
GrandTotal();
}
}

Chart with Dynamic Values

Use namespace :

using System.Web.Services;

Paste this in design page

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">
google.load("visualization", "1", { packages: ["corechart"] });
google.setOnLoadCallback(drawChart);
function drawChart() {
var options = {
title: 'CHART',
width: 400,
height: 400,
bar: { groupWidth: "95%" },
legend: { position: "none" },
isStacked: true
};
var Lbl_Text = $('#<%=Label3.ClientID %>').text();
debugger;
$.ajax({
type: "POST",
url: "Chart.aspx/GetChartData",
data: "{Lbl_Text:'" + Lbl_Text + "'}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
debugger;
var data = google.visualization.arrayToDataTable(r.d);
//var chart = new google.visualization.LineChart($("#chart")[0]); //***BarChart***
var chart = new google.visualization.PieChart($("#chart")[0]); //***PieChart***
chart.draw(data, options);
},
failure: function (r) {
alert(r.d);
},
error: function (r) {
alert(r.d);
}
});
}
</script>

<div id="chart" style="width: 500px; height: 500px;">
</div>

Paste this in your c# page

public static List<object> GetChartData(string Lbl_Text)
{
string query = "select Share,Value from Table4 where Date='" + Lbl_Text + "';"
string constr = ConfigurationManager.ConnectionStrings["dbcn"].ConnectionString;
List<object> chartData = new List<object>();
chartData.Add(new object[]
{
"Share", "Value"
});
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
chartData.Add(new object[]
{
sdr["Share"], sdr["Value"]
});
}
}
con.Close();
return chartData;
}
}
}

Chart

Use namespace :

using System.Web.Services;

Paste this in design page.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">
google.load("visualization", "1", { packages: ["corechart"] });
google.setOnLoadCallback(drawChart);
function drawChart() {
var options = {
title: 'CHART',
width: 400,
height: 400,
bar: { groupWidth: "95%" },
legend: { position: "none" },
isStacked: true
};

$.ajax({
type: "POST",
url: "Chart.aspx/GetChartData", //Chart.aspx is the page name.
data: "{}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
debugger;
var data = google.visualization.arrayToDataTable(r.d);
//var chart = new google.visualization.BarChart($("#chart")[0]); //***BarChart***
var chart = new google.visualization.PieChart($("#chart")[0]); //***PieChart***
chart.draw(data, options);
},
failure: function (r) {
alert(r.d);
},
error: function (r) {
alert(r.d);
}
});
}
</script>

<div id="chart" style="width: 500px; height: 500px;"> //this is the div which shows the chart.
</div>

Paste this in your c# page.

[WebMethod]
public static List<object> GetChartData()
{
string query = "select Share,Value from Table";
string constr = ConfigurationManager.ConnectionStrings["dbcn"].ConnectionString;
List<object> chartData = new List<object>();
chartData.Add(new object[]
{
"Share", "Value"
});
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
chartData.Add(new object[]
{
sdr["Share"], sdr["Value"]
});
}
}
con.Close();
return chartData;
}
}
}

How to find Grand Total of gridview rows and display in footer rows dynamically?

private void GrandTotal()
{
int grandtotal = 0;
int n;
foreach (GridViewRow row in GridView1.Rows)
{
n = Convert.ToInt32(row.Cells[7].Text);
//TextBox tb = (TextBox)row.FindControl("txtTotal");
//n = Convert.ToInt32(tb.Text);
grandtotal = grandtotal + n; //Where Cells is the column. Just changed the index of cells
}
GridView1.FooterRow.Cells[6].Text = "Grand Total";
GridView1.FooterRow.Cells[7].Text = grandtotal.ToString();
}

call the function where you want to display the total.

Google Map API

Open GOOGLE MAP from your computer.
Type the address you want to show on the map.
Select Share or Embed Map from Menu(A pop up will appear).
Copy the <iframe> code from Embed a Map in your website where you want to display the Map.

Google calendar API

Open GOOGLE CALENDAR from your computer.
In top right, click settings > Settings for my calendar.
Copy the <iframe> code in your website where you want to display the google calendar.

NB : Your embedded calendar will only be visible to people you've shared it with. To allow all visitors to see your calendar, you'll need to make it public.

Sunday, 10 March 2019

How to upload excel fle to database

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

protected void Button1_Click(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);

string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;

}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();

//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[2] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)) });

using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();

string consString = ConfigurationManager.ConnectionStrings["dbcn"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.Table1";

//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Sl", "Id");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}

In web config

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

TechWorld - @h!