Programming Virus in my DNA: July 2014

Saturday, 19 July 2014

SQL using GROUP BY .

Table which is used for GROUP BY :-

1. select MIN(score) AS 'total' from test1 GROUP BY id

2. select COUNT(score) AS 'total' from test1 GROUP BY id

3. select SUM(score) AS 'total' from test1 GROUP BY id

4. select MAX(score) AS 'total' from test1 GROUP BY id

Friday, 18 July 2014

Sql Join query

Table name : - atim

Table name :- asignup

1. select dat,name,email from atim a1 INNER JOIN asignup a2 ON a1.eid=a2.eid

2. select atim.eid , asignup.name FROM atim FULL OUTER JOIN asignup ON atim.eid=asignup.eid

3. select dat,name,email from atim a1 LEFT OUTER JOIN asignup a2 ON a1.eid=a2.eid

4. select dat,name,email from atim a1 RIGHT OUTER JOIN asignup a2 ON a1.eid=a2.eid

5. select dat,name,email from atim a1 FULL OUTER JOIN asignup a2 ON a1.eid=a2.eid

Sql query of create,check,Distinct and order by.

Check that Table is exist in current database or not.

SELECT * FROM "+x+" WHERE 1 = 0

Create new Table.

"create table " + x + " (" + "name nvarchar(50)," + "gender nvarchar(50)," + "email nvarchar(50)," + "mobile nvarchar(50))";

Check the person should be major using CHECK keyword.

create table test1
(
name varchar(50) not null ,
age int NOT NULL CHECK (age >=18)
)

Check condition using AND keyword.

select * from atim where eid='123' AND dtim ='00:00:27'

Check condition using OR keyword.

select * from atim where eid='123' OR dtim ='00:00:27'

Show data in Desecending order.

select * from atim ORDER BY eid desc

Show data in Asecending order.

select * from atim ORDER BY eid asec

Show data in case order (own order).

select * from atim ORDER BY (case eid when 123 then 1
when 321 then 2
else 100 end )

Show data in unrepeated form.

select DISTINCT eid from atim ORDER BY eid

Thursday, 10 July 2014

Change the colour of textbox Onclick event

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style>
        .textbox
        {
            background-color:RED;
        }

        .textbox:focus
        {
            background-color: Silver;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
   <table>
<tr>
        <td>
            <asp:TextBox ID="TextBox2" runat="server" Height="61px" TextMode="MultiLine"
                Width="160px" CssClass="textbox"></asp:TextBox>
        </td>
   <tr>
</table>
    </form>
</body>
</html>

Wednesday, 9 July 2014

Pass the selected single row from gridview to another page.

// First page in which select the chekbox for passing value to another page .

protected void Gridvaluepass_Click(object sender, EventArgs e)
    {
        ArrayList selectedValues = new ArrayList();
        foreach (GridViewRow row in GridView1.Rows)
        {
            bool result = ((CheckBox)row.FindControl("CheckBox1")).Checked;      // finds the selected checkbox control
            if (result)
            {
                int categoryID = Convert.ToInt32(((Label)row.FindControl("Label10")).Text);        // find the selected row value control which is unique
                selectedValues.Add(categoryID);
            }
        }
        Session["SELECTEDVALUES"] = selectedValues;         // pass the selected value with the help of session
        Session["view"] = "view";
        Response.Redirect("complaint.aspx");
    }

// Second page which displays the selected value of previous page

protected void Page_Load(object sender, EventArgs e)
    {

if (Session["SELECTEDVALUES"] != null )                       // check that session is available or not

            {
                ArrayList selectedValues = (ArrayList)Session["SELECTEDVALUES"];

                foreach (int intValue in selectedValues)
                {
                    maincall(intValue);
                }
      }
}
public void maincall(int intValue)
    {
        con.Open();
        string s = " select cno,name,type,zone,ward,pno,lmark,mob,ticket,tstatus from complaint1 where id='"+intValue.ToString()+"' ";
        SqlDataAdapter da=new SqlDataAdapter(s,con);
        DataSet ds=new DataSet();
        da.Fill(ds);
        TextBox1.Text=ds.Tables[0].Rows[0]["cno"].ToString();
        TextBox2.Text=ds.Tables[0].Rows[0]["name"].ToString();
        TextBox8.Text = ds.Tables[0].Rows[0]["mob"].ToString();
        TextBox9.Text = ds.Tables[0].Rows[0]["ticket"].ToString();
        DropDownList1.SelectedItem.Text = ds.Tables[0].Rows[0]["tstatus"].ToString();
        DropDownList2.SelectedItem.Text = ds.Tables[0].Rows[0]["zone"].ToString();
        DropDownList3.SelectedItem.Text = ds.Tables[0].Rows[0]["ward"].ToString();
        DropDownList4.SelectedItem.Text = ds.Tables[0].Rows[0]["type"].ToString();
        con.Close();
    }

Show city name in dropdownlist after selecting the country name.

Database for fetching city name

protected void Page_Load(object sender, EventArgs e)
    {
        drop2();
        drop();

    }

public void drop2()
    {
        if (DropDownList4.SelectedItem.Text == "--Selectcountry--")
        {
            DropDownList5.Enabled = false;
            DropDownList5.SelectedValue = null;
        }
        else
            DropDownList5.Enabled = true;

    }

Autopost enabled in Dropdownlist of country

public void drop()
    {
        con.Open();
        string s = "select ward from country where cont='" + DropDownList4.SelectedValue.ToString() + "'";
        SqlCommand cmd = new SqlCommand(s, con);
        SqlDataReader dr = cmd.ExecuteReader();
        DropDownList5.DataSource = dr;
        DropDownList5.DataTextField = "city";
        DropDownList5.DataValueField = "city";
        DropDownList5.DataBind();
        con.Close();
    }

Export Gridview data in Excel sheet.

protected void Excel_exportButon_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
           // GridView1.AutoGenerateColumns = false;
        using (StringWriter sw = new StringWriter())
        {
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            //To Export all pages
            GridView1.AllowPaging = false;
            this.search();

            GridView1.HeaderRow.BackColor = Color.White;
            foreach (TableCell cell in GridView1.HeaderRow.Cells)
            {
                cell.BackColor = GridView1.HeaderStyle.BackColor;
            }
            foreach (GridViewRow row in GridView1.Rows)
            {
                row.BackColor = Color.White;
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = GridView1.RowStyle.BackColor;
                    }
                    cell.CssClass = "textmode";
                }
            }

            GridView1.RenderControl(hw);

            //style to format numbers to string
            string style = @"<style> .textmode { } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();        }
    }

Pages

Saturday, 19 July 2014

Friday, 18 July 2014

Thursday, 10 July 2014

Wednesday, 9 July 2014