select * from Tbl_Users where USER_ID%2=0(for even rows)
select * from Tbl_Users where USER_ID%2=1(for odd rows)
select * from Tbl_Users where USER_ID%2=1(for odd rows)
Tuesday, 17 September 2013
Monday, 16 September 2013
Date Difference in SQL Server
SQL SERVER:
use task
create table date
(Startdate date,
Enddate date)
insert into date values('2013-01-01',GETDATE())
select * from date
select *,DATEDIFF(MONTH,Startdate,Enddate) as diffdate from date
use task
create table date
(Startdate date,
Enddate date)
insert into date values('2013-01-01',GETDATE())
select * from date
select *,DATEDIFF(MONTH,Startdate,Enddate) as diffdate from date
Wednesday, 11 September 2013
MS Reporting
ASPX Page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<div>
<asp:Button ID="btnDisplay" runat="server" Height="30px" Width="90px" Text="Display" OnClick="btnDisplay_Click" />
<br />
<rsweb:ReportViewer ID="ReportViewver1" runat="server" Height="500px" Width="1000px">
<LocalReport ReportPath="Report.rdlc">
<DataSources>
<rsweb:ReportDataSource />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<br />
<asp:Label ID="lblerrmsg" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<div>
<asp:Button ID="btnDisplay" runat="server" Height="30px" Width="90px" Text="Display" OnClick="btnDisplay_Click" />
<br />
<rsweb:ReportViewer ID="ReportViewver1" runat="server" Height="500px" Width="1000px">
<LocalReport ReportPath="Report.rdlc">
<DataSources>
<rsweb:ReportDataSource />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<br />
<asp:Label ID="lblerrmsg" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
MS Reportting
ASPX Page:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HealthtipsConnectionString"].ToString());
SqlCommand cmd;
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnDisplay_Click(object sender, EventArgs e)
{
try
{
con.Open();
string str = "Select * From Tbl_Users";
cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.Text;
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables[0].Columns.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
ReportDataSource rds = new ReportDataSource("DataSet1", ds.Tables[0]);
ReportViewver1.LocalReport.DataSources.Clear();
ReportViewver1.LocalReport.DataSources.Add(rds);
ReportViewver1.LocalReport.Refresh();
}
}
}
catch (Exception ex)
{
lblerrmsg.Text = ex.Message;
}
}
}
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HealthtipsConnectionString"].ToString());
SqlCommand cmd;
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnDisplay_Click(object sender, EventArgs e)
{
try
{
con.Open();
string str = "Select * From Tbl_Users";
cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.Text;
da.SelectCommand = cmd;
da.Fill(ds);
if (ds.Tables[0].Columns.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
ReportDataSource rds = new ReportDataSource("DataSet1", ds.Tables[0]);
ReportViewver1.LocalReport.DataSources.Clear();
ReportViewver1.LocalReport.DataSources.Add(rds);
ReportViewver1.LocalReport.Refresh();
}
}
}
catch (Exception ex)
{
lblerrmsg.Text = ex.Message;
}
}
}
Microsoft Reporting
Web Config file:
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="HealthtipsConnectionString" connectionString="Data Source=.;Initial Catalog=Healthtips;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<httpHandlers>
<add verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
</httpHandlers>
<compilation debug="true"/></system.web>
</configuration>
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="HealthtipsConnectionString" connectionString="Data Source=.;Initial Catalog=Healthtips;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<httpHandlers>
<add verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
</httpHandlers>
<compilation debug="true"/></system.web>
</configuration>
Tuesday, 3 September 2013
Nested Gridview
aspx. cs page
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class nestedgridview : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=.;Integrated Security=true;Initial Catalog=master");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
// This method is used to bind gridview from database
protected void BindGridview()
{
con.Open();
SqlCommand cmd = new SqlCommand("select TOP 4 countryid,countryname from country", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
gvParentGrid.DataSource = ds;
gvParentGrid.DataBind();
}
protected void gvUserInfo_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
con.Open();
GridView gv = (GridView)e.Row.FindControl("gvChildGrid");
int countryid = Convert.ToInt32(e.Row.Cells[1].Text);
SqlCommand cmd = new SqlCommand("select * from state where countryid="+ countryid, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
gv.DataSource = ds;
gv.DataBind();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class nestedgridview : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=.;Integrated Security=true;Initial Catalog=master");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
// This method is used to bind gridview from database
protected void BindGridview()
{
con.Open();
SqlCommand cmd = new SqlCommand("select TOP 4 countryid,countryname from country", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
gvParentGrid.DataSource = ds;
gvParentGrid.DataBind();
}
protected void gvUserInfo_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
con.Open();
GridView gv = (GridView)e.Row.FindControl("gvChildGrid");
int countryid = Convert.ToInt32(e.Row.Cells[1].Text);
SqlCommand cmd = new SqlCommand("select * from state where countryid="+ countryid, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
gv.DataSource = ds;
gv.DataBind();
}
}
}
Nested Gridview
aspx page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="nestedgridview.aspx.cs" Inherits="nestedgridview" %>
<!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 id="Head1" runat="server">
<title>Gridview within Gridivew - Nested gridview example in asp.net </title>
<script language="javascript" type="text/javascript">
function divexpandcollapse(divname) {
var div = document.getElementById(divname);
var img = document.getElementById('img' + divname);
if (div.style.display == "none") {
div.style.display = "inline";
img.src = "minus.gif";
} else {
div.style.display = "none";
img.src = "plus.gif";
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvParentGrid" runat="server" DataKeyNames="countryid" Width="300"
AutoGenerateColumns="false" OnRowDataBound="gvUserInfo_RowDataBound" GridLines="None"
BorderStyle="Solid" BorderWidth="1px" BorderColor="#df5015">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<RowStyle BackColor="#E1E1E1" />
<AlternatingRowStyle BackColor="White" />
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<Columns>
<asp:TemplateField ItemStyle-Width="20px">
<ItemTemplate>
<a href="JavaScript:divexpandcollapse('div<%# Eval("countryid") %>');">
<img id="imgdiv<%# Eval("countryid") %>" width="9px" border="0" src="plus.gif" />
</a>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="countryid" HeaderText="CountryId" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="countryname" HeaderText="CountryName" HeaderStyle-HorizontalAlign="Left" />
<asp:TemplateField>
<ItemTemplate>
<tr>
<td colspan="100%">
<div id="div<%# Eval("countryid") %>" style="display: none; position: relative; left: 15px;
overflow: auto">
<asp:GridView ID="gvChildGrid" runat="server" AutoGenerateColumns="false" BorderStyle="Double"
BorderColor="#df5015" GridLines="None" Width="250px">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<RowStyle BackColor="#E1E1E1" />
<AlternatingRowStyle BackColor="White" />
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<Columns>
<asp:BoundField DataField="stateid" HeaderText="StateID" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="statename" HeaderText="StateName" HeaderStyle-HorizontalAlign="Left" />
</Columns>
</asp:GridView>
</div>
</td>
</tr>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="nestedgridview.aspx.cs" Inherits="nestedgridview" %>
<!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 id="Head1" runat="server">
<title>Gridview within Gridivew - Nested gridview example in asp.net </title>
<script language="javascript" type="text/javascript">
function divexpandcollapse(divname) {
var div = document.getElementById(divname);
var img = document.getElementById('img' + divname);
if (div.style.display == "none") {
div.style.display = "inline";
img.src = "minus.gif";
} else {
div.style.display = "none";
img.src = "plus.gif";
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvParentGrid" runat="server" DataKeyNames="countryid" Width="300"
AutoGenerateColumns="false" OnRowDataBound="gvUserInfo_RowDataBound" GridLines="None"
BorderStyle="Solid" BorderWidth="1px" BorderColor="#df5015">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<RowStyle BackColor="#E1E1E1" />
<AlternatingRowStyle BackColor="White" />
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<Columns>
<asp:TemplateField ItemStyle-Width="20px">
<ItemTemplate>
<a href="JavaScript:divexpandcollapse('div<%# Eval("countryid") %>');">
<img id="imgdiv<%# Eval("countryid") %>" width="9px" border="0" src="plus.gif" />
</a>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="countryid" HeaderText="CountryId" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="countryname" HeaderText="CountryName" HeaderStyle-HorizontalAlign="Left" />
<asp:TemplateField>
<ItemTemplate>
<tr>
<td colspan="100%">
<div id="div<%# Eval("countryid") %>" style="display: none; position: relative; left: 15px;
overflow: auto">
<asp:GridView ID="gvChildGrid" runat="server" AutoGenerateColumns="false" BorderStyle="Double"
BorderColor="#df5015" GridLines="None" Width="250px">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<RowStyle BackColor="#E1E1E1" />
<AlternatingRowStyle BackColor="White" />
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
<Columns>
<asp:BoundField DataField="stateid" HeaderText="StateID" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="statename" HeaderText="StateName" HeaderStyle-HorizontalAlign="Left" />
</Columns>
</asp:GridView>
</div>
</td>
</tr>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Subscribe to:
Posts (Atom)