Mohamed Hussain: April 2013

Tuesday 30 April 2013

Multiple data insert from one table to another table

INSERT INTO SaveDoc ( DocName )
SELECT empname
FROM EmpDetails

How to remove left row headers

click datagridview --> Properties.

Click a RowHeadersVisible Property and set to false

Output:

Delete Confirmation Message Box

if (MessageBox.Show("Are you sure you want to delete this row?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//Delete Query
}

Convert string into double values

string qw1 = txtWeight.Text.ToString().Trim();
if (qw1 != "")
{
double qw = Convert.ToDouble(qw1);
}

Join query in Windows Application

Example 1

query = "select Distinct IBS_ITEM_MST.Item_Cde from IBS_ITEM_MST,IBS_SALESORDER,IBS_SALESORDER_DETAILS where IBS_SALESORDER.SO_No = IBS_SALESORDER_DETAILS.SO_No AND IBS_SALESORDER_DETAILS.Item_Code = IBS_ITEM_MST.Item_Code AND IBS_SALESORDER.Cust_Id = '" + sid + "' AND IBS_ITEM_MST.Item_Cde Like '" + str + "%' and IBS_SALESORDER_DETAILS.Comp_Code ='" + compid + "' and IBS_SALESORDER_DETAILS.Year_Code = '" + yearid + "' and IBS_SALESORDER_DETAILS.SO_Billed = 'N' and (IBS_SALESORDER_DETAILS.SO_Bill_Type = 'N' or IBS_SALESORDER_DETAILS.SO_Bill_Type = 'I')";

Example 2

string query = "select IBS_ITEM_MST.Item_Name,IBS_ITEM_MST.Item_Cde,IBS_ITEM_MST.Item_UOM,IBS_ITEM_MST.Item_UOM1,IBS_ITEM_MST.Item_Srate,IBS_ITEM_MST.Item_Code from IBS_ITEM_MST where Item_Cde = '" + a + "'";

Like Query in windows application

query = "select * from IBS_ITEM_MST where Item_Cde Like '" + str + "%'";

In datagridview temporarily remove a row

dgvDescription.Rows.RemoveAt(dgvDescription.SelectedRows[0].Index);

How to find a values from database using customer id

string find = "select * from IBS_DC where DC_No = '" +a+ "'";
SqlCommand findquery = new SqlCommand(find, con);
SqlDataReader dr = findquery.ExecuteReader();
if (dr.Read())
{
cid = dr["Cust_Id"].ToString();
//dtpDate.Text = dr["Date"].ToString();
txtPono.Text = dr["PO_No"].ToString();
txtRemarks.Text = dr["Remarks"].ToString();
txtModeoftrans.Text = dr["Trans_Mode"].ToString();
txtVechileregno.Text = dr["Vechile_No"].ToString();
dtpDate.Value = Convert.ToDateTime(dr["DC_Date"]);
dtpPodate.Value = Convert.ToDateTime(dr["PO_Date"]);
sid = dr["Ship_Id"].ToString();
ssid = Convert.ToInt32(sid);
nodc = dr["DC_No"].ToString();
butInsert.Text = "UPDATE";
butDelete.Enabled = true;
butDcprint.Enabled = true;
}
else
{
butInsert.Text = "INSERT";
butDelete.Enabled = false;
butDcprint.Enabled = false;
}
dr.Close();

delete query in windows forms

string dquery = "delete from IBS_DC_DTLS where Sl_No = '" + desid + "' and DC_No = '" + dcno + "'";
SqlCommand dcmd = new SqlCommand(dquery, con);
dcmd.ExecuteNonQuery();

how to insert values from datagridview values to database

for (int rows = 0; rows < dgvDescription.Rows.Count; rows++)
{
string description = dgvDescription.Rows[rows].Cells["description"].Value.ToString();
string size = dgvDescription.Rows[rows].Cells["size"].Value.ToString();
string materialname = dgvDescription.Rows[rows].Cells["materialname"].Value.ToString();
string noofcolor = dgvDescription.Rows[rows].Cells["colorprinting"].Value.ToString();
string mode = dgvDescription.Rows[rows].Cells["mode"].Value.ToString();
string qty = dgvDescription.Rows[rows].Cells["qty"].Value.ToString();
string qty_uom = dgvDescription.Rows[rows].Cells["quom"].Value.ToString();
string weight = dgvDescription.Rows[rows].Cells["weight"].Value.ToString();
string weight_uom = dgvDescription.Rows[rows].Cells["wuom"].Value.ToString();
string rate = dgvDescription.Rows[rows].Cells["rate"].Value.ToString();
string amount = dgvDescription.Rows[rows].Cells["amount"].Value.ToString();
string option = dgvDescription.Rows[rows].Cells["option"].Value.ToString();
int r = rows + 1;
int o = 0;
if (option == "Qty")
{
o = 2;
}
else if (option == "Wt")
{
o = 1;
}

string mquery = "insert into IBS_DC_DTLS(DC_No,Sl_No,Description,Size,Material_Name,Noof_Color,Mode,Quantity,Qty_UOM,Weight,Weight_UOM,Multi_Opt,Rate,Amount) values('" + dcno + "','" + r + "','" + description + "','" + size + "','" + materialname + "','" + noofcolor + "','" + mode + "','" + qty + "','" + qty_uom + "','" + weight + "','" + weight_uom + "','" + o + "','" + rate + "','" + amount + "')";
SqlCommand mcmd = new SqlCommand(mquery, con);
mcmd.ExecuteNonQuery();
}

Bind a Datagridview

SqlDataAdapter da = new SqlDataAdapter("select * from IBS_SHIP_ADDRESS where Cust_Id ='" + cid + "'",con);
DataSet ds = new System.Data.DataSet();
da.Fill(ds);
dgvShipAddress.DataSource = ds.Tables[0];

How to clear a datagridview

dgvDescription.Rows.Clear();

How to find a maximum number from database

string find = "select max(DC_No) from IBS_DC_DTLS";
SqlCommand findquery = new SqlCommand(find, con);
SqlDataReader dr = findquery.ExecuteReader();
if (dr.Read())
{
string s = dr[0].ToString();
if (s != "")
{
int ss = Convert.ToInt32(s);
int sss = ss + 1;
string sd = Convert.ToString(sss);
txtDcno.Text = sd;
}
else
{
txtDcno.Text = "1";
}
}

Temporaily add in datagridview

dgvDescription.Rows.Add(txtDescription.Text, txtSize.Text, txtMaterialname.Text, txtColorprinting.Text, txtMode.Text, txtQuantity.Text, comboQuom.Text, txtWeight.Text, comboWuom.Text, txtRate.Text, txtAmount.Text);

How to add column names on form load in datagridview

private void GridDesign()
{
dgvDescription.Columns.Add("dno", "DNO");
dgvDescription.Columns.Add("sno", "SNO");
dgvDescription.Columns.Add("description", "Description");
dgvDescription.Columns.Add("size", "Size");
dgvDescription.Columns.Add("materialname", "Material Name");
dgvDescription.Columns.Add("colorprinting", "No of Color Printing");
dgvDescription.Columns.Add("mode", "Mode");
dgvDescription.Columns.Add("qty", "Quantity");
dgvDescription.Columns.Add("quom", "UOM");
dgvDescription.Columns.Add("weight", "Weight");
dgvDescription.Columns.Add("wuom", "UOM");
dgvDescription.Columns.Add("rate", "Rate");
dgvDescription.Columns.Add("amount", "Amount");
dgvDescription.Columns.Add("option", "Option");

dgvDescription.Columns[0].Width = 100;
dgvDescription.Columns[1].Width = 100;
dgvDescription.Columns[2].Width = 200;
dgvDescription.Columns[3].Width = 100;
dgvDescription.Columns[4].Width = 100;
dgvDescription.Columns[5].Width = 100;
dgvDescription.Columns[6].Width = 85;
dgvDescription.Columns[7].Width = 50;
dgvDescription.Columns[8].Width = 85;
dgvDescription.Columns[9].Width = 50;
dgvDescription.Columns[10].Width = 85;
dgvDescription.Columns[11].Width = 100;
dgvDescription.Columns[12].Width = 100;

comboQuom.SelectedIndex = 0;
comboWuom.SelectedIndex = 0;
this.dgvDescription.Columns["DNO"].Visible = false;
this.dgvDescription.Columns["SNO"].Visible = false;
this.dgvDescription.Columns["Option"].Visible = false;

this.dgvDescription.Columns["qty"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
this.dgvDescription.Columns["weight"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
this.dgvDescription.Columns["rate"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
this.dgvDescription.Columns["amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

dgvDeletedescription.Columns.Add("sno", "SNO");
dgvDeletedescription.Columns.Add("sid", "SID");

dgvDeletedescription.Columns[0].Width = 100;
dgvDeletedescription.Columns[0].Width = 100;
}

Output:

and Finally call a load function

If type a text box related values from database in datagridview

private void txtFYear_TextChanged(object sender, EventArgs e)
{
if (txtFYear.Text.Length > 0)
{
string str = txtFYear.Text.ToString().Trim();
SqlConnection con = new SqlConnection(conn);
con.Open();

SqlDataAdapter da1 = new SqlDataAdapter("select * from YEARMASTER Where FromYear Like '" + str + "%'", con);
DataSet ds1 = new System.Data.DataSet();
da1.Fill(ds1);
dgvYear.DataSource = ds1.Tables[0];

dgvYear.Columns["AccountYear"].Width = 200;
dgvYear.Columns["Year_Code"].Width = 125;

if (dgvYear.Rows.Count > 0)
{
dgvYear.Visible = true;
}
else
{
dgvYear.Visible = false;
}

this.dgvYear.Columns["FromYear"].Visible = false;
this.dgvYear.Columns["ToYear"].Visible = false;
}
else
{
dgvYear.Visible = false;
}
}

Update Query in Windows Forms

string query1 = "update YEARMASTER set FromYear = '" + ff + "',ToYear = '" + tt + "',AccountYear = '" + accyear + "' where Year_Code = '" + yid + "'";
SqlCommand cmd1 = new SqlCommand(query1, con);
cmd1.ExecuteNonQuery();
MessageBox.Show("Updated Successfully");

Insert Command in Windows Form

string query = "insert into YEARMASTER(FromYear,ToYear,AccountYear) values('" + ff + "','" + tt + "','" + accyear + "')";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Saved Successfully");

In Text Box only allowed numbers and allowed only characters

//Allowed Only Numbers
private void txtRegKey_KeyPress(object sender, KeyPressEventArgs e)
{
if ((!Char.IsDigit(e.KeyChar)) && !(e.KeyChar == (char)8))
{
//MessageBox.Show("only numbers are allowed");
e.Handled = true;
}
}

//Allowed Only Characters

private void txtRegKey_KeyPress(object sender, KeyPressEventArgs e)
{
int ascii = Convert.ToInt16(e.KeyChar);
if ((ascii >= 97 && ascii <= 122) || (ascii == 8) || (ascii >= 65 && ascii <= 90))
{
e.Handled = false;
}
else
{
e.Handled = true;
}
}

//Allowed Numbers and Charcters

private void txtRegKey_KeyPress(object sender, KeyPressEventArgs e)
{
int ascii = Convert.ToInt16(e.KeyChar);
if ((ascii >= 97 && ascii <= 122) || (ascii == 8) || (ascii >= 65 && ascii <= 90) || (ascii >= 48 && ascii <= 57))
{
e.Handled = false;
}
else
{
e.Handled = true;
}
}

//Allowed Numbers,Charcters and Spaces

private void txtRegKey_KeyPress(object sender, KeyPressEventArgs e)
{
int ascii = Convert.ToInt16(e.KeyChar);
if ((ascii >= 97 && ascii <= 122) || (ascii == 8) || (ascii >= 65 && ascii <= 90) || (ascii >= 48 && ascii <= 57) || (ascii == 32) )
{
e.Handled = false;
}
else
{
e.Handled = true;
}
}

//ASCII Values

Google Map Integration

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title>Google Maps JavaScript API Example</title>
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAmnOQlz4O2XMui-auz2pa9BQCy-*&sensor=false" type="text/javascript"></script>
<script type="text/javascript">
var map;
var geocoder;
function initialize() {
if (GBrowserIsCompatible()) {
map = new GMap2(document.getElementById("map"));
map.setCenter(new GLatLng(51.5, -0.1), 10);
map.setUIToDefault();

geocoder = new GClientGeocoder();
showAddress();
}
}
function showAddress() {
var txtAddress = document.getElementById("<%=txtAddress.ClientID %>");
var address = txtAddress.value;

geocoder.getLatLng(
address,
function (point) {
if (!point) {
alert(address + " not found");
}
else {
map.setCenter(point, 15);
var marker = new GMarker(point);
map.addOverlay(marker);
marker.openInfoWindow(address);
}
}
);
}
</script>
</head>
<body onload="initialize()" onunload="GUnload()">
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtAddress" runat="server" />
<input type="button" value="Find" onclick="showAddress();" />
</div>

<div id="map" style="width: 500px; height: 500px"></div>
</form>
</body>
</html>

country values based state values(casecading dropdown function)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["masterConnectionString"].ToString());
con.Open();
SqlCommand com = new SqlCommand("select cname,cid from countryname", con);
SqlDataReader dr = com.ExecuteReader();

//TextBox1.Text = "";

DropDownList1.Items.Clear();
while (dr.Read())
{
//TextBox1.Text = dr.GetString(0);
DropDownList1.Items.Add(new ListItem(dr[0].ToString()));
}
}
}

private void SearchText(string v)
{
//string v = DropDownList1.SelectedValue;

SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["masterConnectionString"].ToString());
con1.Open();

SqlCommand com1 = new SqlCommand("select sname from statename1 where cname='" + v + "'", con1);
SqlDataReader dr1 = com1.ExecuteReader();
DropDownList2.Items.Clear();
while (dr1.Read())
{
DropDownList2.Items.Add(new ListItem(dr1[0].ToString()));
}
con1.Close();
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string v = DropDownList1.SelectedValue.ToString().Trim();
SearchText(v);
}
}

Output:

Animation in aspx page

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

<!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>jQuery animation example</title>
<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
<style>
#plane{
position:relative;
top:0px;
background-image: url(aeroplane1.jpg);
background-repeat: no-repeat;
display:block;
height:200px;
width:300px;
}
</style>
</head>

<body style="overflow:hidden">
<span id="plane"></span>

<script>
$(document).ready(function () {

function flyPlane() {
plane.css('left', startPos);
plane.animate({ left: -235 }, 12000, 'linear')
};

var screenWidth = $(document).width();
var startPos = screenWidth;
var plane = $('#plane')
flyPlane();
setInterval(function () {
flyPlane();
}, 15500);
});
</script>
</body>
</html>

The Image is save and use

Image stroed and reterived database

protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection connection = null;
try
{
FileUpload img = (FileUpload)imgUpload;
Byte[] imgByte = null;
if (img.HasFile && img.PostedFile != null)
{
//To create a PostedFile
HttpPostedFile File = imgUpload.PostedFile;
//Create byte Array with file len
imgByte = new Byte[File.ContentLength];
//force the control to load data in array
File.InputStream.Read(imgByte, 0, File.ContentLength);
}
// Insert the employee name and image into db
string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
connection = new SqlConnection(conn);

connection.Open();
string sql = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg) SELECT @@IDENTITY";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim());
cmd.Parameters.AddWithValue("@eimg", imgByte);
int id = Convert.ToInt32(cmd.ExecuteScalar());
lblResult.Text = String.Format("Employee ID is {0}", id);
Image1.ImageUrl = "~/ShowImage.ashx?id=" + id;
}
catch
{
lblResult.Text = "There was an error";
}
finally
{
//connection.Close();
}

}
protected void Button1_Click(object sender, EventArgs e)
{
string id = TextBox1.Text;

Image2.ImageUrl = "~/ShowImage.ashx?id=" + id;
Image1.ImageUrl = "";
}

To create ShowImage.ashx file

<%@ WebHandler Language="C#" Class="ShowImage" %>

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

public class ShowImage : IHttpHandler {

public void ProcessRequest(HttpContext context)
{
Int32 empno;
if (context.Request.QueryString["id"] != null)
empno = Convert.ToInt32(context.Request.QueryString["id"]);
else
throw new ArgumentException("No parameter specified");

context.Response.ContentType = "image/jpeg";
Stream strm = ShowEmpImage(empno);
byte[] buffer = new byte[4096];
int byteSeq = strm.Read(buffer, 0, 4096);

while (byteSeq > 0)
{
context.Response.OutputStream.Write(buffer, 0, byteSeq);
byteSeq = strm.Read(buffer, 0, 4096);
}
//context.Response.BinaryWrite(buffer);
}

public Stream ShowEmpImage(int empno)
{
string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection connection = new SqlConnection(conn);
string sql = "SELECT empimg FROM EmpDetails WHERE empid = @ID";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", empno);
connection.Open();
object img = cmd.ExecuteScalar();
try
{
return new MemoryStream((byte[])img);
}
catch
{
return null;
}
finally
{
connection.Close();
}
}

public bool IsReusable
{
get
{
return false;
}
}

}

Output:

Image Upload and Save in a Location

Solution location : To create a Folder Name is Images.

protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
try
{
if (FileUpload1.PostedFile.ContentType == "image/jpeg")
{
if (FileUpload1.PostedFile.ContentLength < 512000)
{
string filename = Path.GetFileName(FileUpload1.FileName);
FileUpload1.SaveAs(Server.MapPath("~/Images/") + filename);
Label1.Text = "File uploaded successfully!";
}
else
Label1.Text = "File maximum size is 500 Kb";
}
else
Label1.Text = "Only JPEG files are accepted!";
}
catch (Exception exc)
{
Label1.Text = "The file could not be uploaded. The following error occured: " + exc.Message;
}
}
}

How to Convert Numbers to Words(Rupees Format)

Coding:

private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text.Length > 0)
{
string a = textBox1.Text.ToString().Trim();
int b = Convert.ToInt32(a);
string c = NumberToWords(b);
label1.Text = "Amount :" + c + "Rupees Only";
}
else
{
MessageBox.Show("Enter Numbers and Click a Convert Buttton");
}
}

public static string NumberToWords(int number)
{
if (number == 0)
return "zero";

if (number < 0)
return "minus " + NumberToWords(Math.Abs(number));

string words = "";

if ((number / 1000000) > 0)
{
words += NumberToWords(number / 1000000) + " million ";
number %= 1000000;
}
if ((number / 100000) > 0)
{
words += NumberToWords(number / 100000) + " lakhs ";
number %= 100000;
}

if ((number / 1000) > 0)
{
words += NumberToWords(number / 1000) + " thousand ";
number %= 1000;
}

if ((number / 100) > 0)
{
words += NumberToWords(number / 100) + " hundred ";
number %= 100;
}

if (number > 0)
{
if (words != "")
words += "and ";

var unitsMap = new[] { "zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen" };
var tensMap = new[] { "zero", "ten", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety" };

if (number < 20)
words += unitsMap[number];
else
{
words += tensMap[number / 10];
if ((number % 10) > 0)
words += "-" + unitsMap[number % 10];
}
}
return words;
}

Output: