SubsDotNet: January 2014

Friday 31 January 2014

CheckBoxList Operations in Asp.Net

Ans:-

Default.aspx:-

<%@ 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>Check Box List with TextBox Operation</title>
</head>
<body>
<form id="form1" runat="server">
<center>
<div style="border:solid:1px:gray">
<br />
Emp Names:<asp:CheckBoxList ID="CheckBoxList1" runat="server" AutoPostBack="true"
onselectedindexchanged="CheckBoxList1_SelectedIndexChanged">
</asp:CheckBoxList>
<br />
Selected Name: <asp:TextBox ID="txtId" runat="server"></asp:TextBox><br />
</div>

</center>
</form>
</body>
</html>

Default.aspx.cs:-

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

static SqlConnection cn;

static SqlCommand cmd;

static SqlDataAdapter da;

static string strConn = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindChkData();

}

void BindChkData()

{

cn = new SqlConnection(strConn);

cn.Open();

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spGetEmpName";

da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds, "Emp");

CheckBoxList1.DataSource = ds;

CheckBoxList1.DataTextField = "name";

CheckBoxList1.DataValueField = "id";

CheckBoxList1.DataBind();

}

protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)

{

string items1=string.Empty;

//int[] arr=new int[11];

//int i = 0;

ArrayList arr = new ArrayList();

foreach (ListItem li in CheckBoxList1.Items)

{

if (li.Selected == true)

{

items1 += li.Text + ",";

txtId.Text = li.Value;

arr.Add(txtId.Text);

//arr[i] =Convert.ToInt32(txtId.Text);

//i++;

}

string[] item1 = items1.Split(',');

foreach (string s in item1)

{

Response.Write(s + " ");

}

//foreach (int j in arr)

//{

// Response.Write(j + " ");

//}

Response.Write("<br/>");

foreach (var s in arr)

{

Response.Write(s + " ");

}

web.config:-

</connectionStrings>

Stored Procedure:-

create proc spGetEmpName

begin

select id,name from Emp;

end

Thursday 30 January 2014

Drop Down List with Text Box Operation in Asp.Net

Get data in to Drop Down List from DB and then while select particular id on Drop Down List then all related data will show in TextBoxes:-

Ans:-

Default.aspx:-

<%@ 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>DropDownListwithTextBoxOperations</title>
</head>
<body>
<form id="form1" runat="server">
<center>
<div>
Select EmpId:<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"
onselectedindexchanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
<br />
Name:<asp:TextBox ID="txtName" runat="server" ></asp:TextBox>
<br />
Sal:<asp:TextBox ID="txtSal" runat="server" ></asp:TextBox>
<br />
DeptId:<asp:TextBox ID="txtDeptId" runat="server" ></asp:TextBox>
<br />
Doj:<asp:TextBox ID="txtDoj" runat="server" ></asp:TextBox>
<br />
Mgrno:<asp:TextBox ID="txtMgrNo" runat="server" ></asp:TextBox>
<br />
ImgId:<asp:TextBox ID="txtImgId" runat="server" ></asp:TextBox>

</div>
</center>
</form>
</body>
</html>

Default.aspx.cs:-

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

static SqlConnection cn;

static SqlDataAdapter da;

static SqlCommand cmd;

static string strConn = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindDDLData();

}

void BindDDLData()

{

cn = new SqlConnection(strConn);

cn.Open();

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spGetEmpName";

da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

da.Fill(dt);

DropDownList1.DataValueField = "id";

DropDownList1.DataTextField = "id";

DropDownList1.DataSource = dt;

DropDownList1.DataBind();

DropDownList1.Items.Insert(0, "Select");

cn.Close();

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

cn = new SqlConnection(strConn);

cn.Open();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spGetEmpNameById";

cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = DropDownList1.SelectedValue.ToString()?? "";

da = new SqlDataAdapter(cmd);

//DataSet ds = new DataSet();

DataTable dt = new DataTable();

da.Fill(dt);

if (dt.Rows.Count > 0)

{

//txtName.Text=ds.Tables["Emp"].Rows[0]["name"].ToString();

txtName.Text = dt.Rows[0]["name"].ToString();

txtSal.Text = dt.Rows[0]["sal"].ToString();

txtDeptId.Text = dt.Rows[0]["deptid"].ToString();

txtDoj.Text = dt.Rows[0]["doj"].ToString();

txtMgrNo.Text = dt.Rows[0]["mgrno"].ToString();

txtImgId.Text = dt.Rows[0]["imgid"].ToString();

}

else

{

Response.Write("No Data in the Table..Pls Check");

}

web.config:-

</connectionStrings>

Stored Procedure:-

1.create proc spGetEmpName

begin

select id,name from Emp;

end

exec spGetEmpName

2.create proc spGetEmpNameById(@id int)

begin

select name,sal,deptid,doj,mgrno,imgid from Emp where id=@id;

end

Issue:-Too many parameter supplied.

Wednesday 29 January 2014

Generate Crystal Report using Web Service in ASP.NET

Ans:-

Steps:-

1.Click File->New->Website
->Choose Loation..
->Choose Language..
->& then Select Asp.Net WebService
->ok

2.After Creation Of Project->Right Click on the Name of the Project
->Select Add
->Add New/Existing Item
->& then select Existing Crystal Report
if you have Or else Select New Crystal
Report from Template through providing
DSN Paths..

3.After Selecting Crystal Report->Right Click on CrystalReport.rpt
->Select Publish as Web Service
->& then one new Service will Create
named as CrystalReportService.asmx
->Select this Service as Set As Start Page
->& then Build the Solution
->& then Copy this Url.

To Consume the Report Web Service from a Client Project:-

Steps:-

1.Right Click on top of Project->Add->Add New Project
->& Now Select C#/VB.Net Language with windows Application
->Set as Start Page to New Project

2.then Select References->Add Service References
->& in Here just Paste that Previously Created Url Or By Clicking CrystalReport.asmx & Selecting Copy...& then Paste here
->After done this one New Web Reference should be Create in Solution Explorer

3.& then go to the ToolBox -> Drag Crystal Report Viewer to the Windows Form
->& then Double Click on the Wondow form & View Code as
InitializeComponent();
->& Space down two Spaces
->& then Enter this Code- CrystalReportViewer1.ReportSource="http://localhost:49542/CrystalReportUsingWS/CrystalReportService.asmx"
->& then Save and Build the Solution

Handle Exception in Stored Procedure using RAISERROR & @@ERROR in Sql Server

Ans:-

Create proc spCheckException(@id int,@name varchar(30),@sal money,@deptid int,@doj date,@mgrno int)
as
begin
if @deptid>=120
begin
RAISERROR ('You entered %d, the deptid can not be greater than 120.', 10, 1, @deptid)
end
else
begin
begin transaction
insert into Emp(id,name,sal,deptid,doj,mgrno) values(@id,@name,@sal,@deptid,@doj,@mgrno)
if @@ERROR<>0
rollback transaction
else
commit transaction
end
end

exec spCheckException 12,'hhhh',5575,124,'01-21-2014',1

This msg will arise-You entered 124, the deptid can not be greater than 120.

Add Column,Constraint(Primary Key,Foreign Key) to the Existing Table

Ans:-

add column:-
alter table tbl_Emp
add col_imgid int

add primary key to the column:-
alter table tbl_Img
add primary key(col_imgid)

add foreign key to the column:-
alter table tbl_Emp
add constraint fk_key foreign key (col_imgid) references tbl_Img(col_imgid)

Tuesday 28 January 2014

W.A.Q with Example of Query,Joins,Functions and Stored Procedures in Sql Server

Create DB:-
create database SubsDB

use SubsDB

Create Tables:-
create table Emp(id int constraint pr_key primary key,name varchar(15),sal money,deptid int foreign key references Dept(deptid))

create table Dept(deptid int constraint pr_key1 primary key,dname varchar(15))

Insert Data into Tables:-
insert into Emp values(01,'sub',15000,103)
insert into Dept(deptid,dname) values(101,'Software')

Show Tables:-
select * from Emp
select * from Dept

For adding new column to the table:-
alter table Emp
add doj date

For finding top most highest sal:-
select MAX(sal) from emp

For finding only 3rd highest sal:-
select top 1 sal from emp where sal in(select top 3 sal from Emp order by sal desc) order by sal

For finding sal According to top 3 sal :-
select name,sal from Emp where sal in(select sal from Emp where sal in(select top 3 sal from Emp order by sal desc))

For finding only 5th row nos sal:-
select sal from Emp where id=5
or
select sal from Emp where id in(select top 1 id from Emp where id in(select top 5 id from Emp order by id)order by id desc)

For Show Date-Month-Year:-
select DATEPART(DD,'1/24/2014')as DDate,DATEPART(MM,'1/24/2014')as MMonth,DATEPART(YY,'1/24/2014') as YYear

For Show Month Name:-
select DATENAME(MONTH,'1/24/2014')

Find Addition Beween two Dates:-
select doj,DATEADD(DAY,5,doj) from Emp
OR
select DATEADD(DAY,5,'1/24/2014')

Find Difference Between two Dates(yy-mm-dd format):-
select DATEDIFF(DAY,'2014-01-01','2014-01-05')

Find Length of Employee:-
select name,LEN(name) as NameLength from Emp

select name,SUBSTRING(name,2,4) as SubStringName from Emp

Find emp name who are working last 5 yr
select doj,CAST(GETDATE() as Date) as CurrentDate,DATEDIFF(DAY,doj,CAST(GETDATE() as Date)) as DateDifferents from Emp where DATEDIFF(DAY,doj,CAST(GETDATE() as Date))>5*365

For Casting DateTime to Date Format:-
select CAST(GETDATE() as Date)

For Casting DateTime to Time Format:-
select CAST(GETDATE() as Time)

Find the details of emp who r working from 1990
select * from Emp where doj>'1990-01-01'

Find emp name who r working with software dept
select name from Emp where deptid=(select deptid from Dept where dname='software')

Find names from 2nd letter to 5th letter:-
select substring(name,2,5) from Emp

Find date and time:-
select GETDATE()

Joins:-

Inner Join:-

select e.id,e.name,e.sal,e.doj,d.dname from Emp e inner join Dept d

on e.deptid=d.deptid

Self Join:-

select e.id,e.name,e.sal,e.doj,d.mgrno from Emp e,Emp d where e.id=d.mgrno

Equi Join:-

select e.id,e.name,e.sal,e.doj,d.dname from Emp e,Dept d where e.deptid=d.deptid

Left Outer Join:-

select e.id,e.name,e.sal,e.doj,d.dname,e.deptid,d.deptid from Emp e left outer join

Dept d on e.deptid=d.deptid

Right Outer Join:-

select e.id,e.name,e.sal,e.doj,d.dname,e.deptid,d.deptid from Emp e right outer join

Dept d on e.deptid=d.deptid

Full Outer Join:-

select e.id,e.name,e.sal,e.doj,d.dname,e.deptid,d.deptid from Emp e full outer join

Dept d on e.deptid=d.deptid

Add Column to Table:-

alter table Emp

add mgrno int

create proc spViewEmpData
as
begin
select * from Emp
end

exec spViewEmpData

create proc spViewDeptData
as
Begin
select * from Dept
end

exec spViewDeptData

create proc spInsertDeptData(@deptid int,@dname varchar(15))
as
begin
insert into Dept(deptid,dname) values(@deptid,@dname)
end

exec spInsertDeptData 111,'sss'

create proc spUpdateDeptData(@deptid int,@dname varchar(15))
as
begin
update Dept set dname=@dname where deptid=@deptid
end

exec spUpdateDeptData 111,'jkl'

create proc spDeleteDeptData(@deptid int)
as
begin
delete Dept where deptid=@deptid
end

exec spDeleteDeptData 111

By Inner Join:-
create proc spFindEmpWrkingWthDept(@deptid int)
as
begin
select e.id,e.name,d.dname from Emp e inner join Dept d on e.deptid=d.deptid where
d.deptid=@deptid
end

exec spFindEmpWrkingWthDept 101

OR by Sub Query:-
create proc spFindEmpWrkingWthDept2(@deptid int)
as
begin
select id,name from Emp where deptid=(select deptid from Dept where deptid=@deptid)
end

exec spFindEmpWrkingWthDept2 101

Function:-

Create Function GetEmpNameById(@id int)
Returns @tbl Table
(
@name varchar(30)
)
As
Begin
Insert @tbl
select name from Emp where id=@id;
Return
End

Select name from dbo.GetEmpNameById(1)

Gridview CRUD Operations using VB.NET

Ans:-

Default.aspx:-

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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 id="Head1" runat="server">
<title>Gridview CRUD Operations</title>
<style type="text/css">

</style>
</head>
<body>
<form id="form1" runat="server">
<center>
<div>
<div>Welcome..</div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateColumns="False" BackColor="White" BorderColor="#999999"
BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical"
PageSize="5" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" ShowFooter ="true" >
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<Columns>
<asp:TemplateField HeaderText="Sr.No." SortExpression="srno">

<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
<FooterTemplate>

<asp:Button ID="btnAdd" runat="server" Text ="Add" onclick="btnAdd_Click"></asp:Button>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="DeptID" SortExpression="deptid">
<EditItemTemplate>
<asp:TextBox ID="txtDid" runat="server" Text='<%# Bind("deptid") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblDid" runat="server" Text='<%# Bind("deptid") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>

<asp:TextBox ID="txtDeptid" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="DeptName" SortExpression="dname">
<EditItemTemplate>
<asp:TextBox ID="txtDname" runat="server" Text='<%# Bind("dname") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblDname" runat="server" Text='<%# Bind("dname") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>

<asp:TextBox ID="txtDeptname" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#DCDCDC" />
</asp:GridView>
<div></div>
</div>
</center>

</form>
</body>
</html>

Default.aspx.cs:-

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Page.IsPostBack <> True Then

BindData()

End If

End Sub

Sub BindData()

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

Dim strQry As String = "select * from Dept"

Dim da As SqlDataAdapter = New SqlDataAdapter(strQry, cn)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "Dept")

GridView1.DataSource = ds

GridView1.DataBind()

End Sub

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging

GridView1.PageIndex = e.NewPageIndex

BindData()

End Sub

Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

cn.Open()

Dim grdv As GridViewRow = GridView1.Rows(e.RowIndex)

Dim tDid As Label = grdv.FindControl("lblDid")

Dim strQry As String = "Delete from Dept where deptid=" + tDid.Text

Dim cmd As SqlCommand = New SqlCommand(strQry, cn)

Dim i As Integer = cmd.ExecuteNonQuery()

If (i > 0) Then

Response.Write("Record is Successfully Deleted")

Else

Response.Write("Record is not Deleted")

End If

cn.Close()

End Sub

Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing

GridView1.EditIndex = e.NewEditIndex

BindData()

End Sub

Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

cn.Open()

Dim grdv As GridViewRow = GridView1.Rows(e.RowIndex)

Dim tDid As TextBox = grdv.FindControl("txtDid")

Dim tDname As TextBox = grdv.FindControl("txtDname")

Dim strQry As String = "Update Dept set dname='" + tDname.Text + "' where deptid=" + tDid.Text

Dim cmd As SqlCommand = New SqlCommand(strQry, cn)

Dim i As Integer = cmd.ExecuteNonQuery()

If (i > 0) Then

Response.Write("Record is Successfully Updated")

Else

Response.Write("Record is not Updated")

End If

GridView1.EditIndex = -1

BindData()

cn.Close()

End Sub

Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit

GridView1.EditIndex = -1

BindData()

End Sub

Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

cn.Open()

Dim grdv As GridViewRow = GridView1.FooterRow

Dim tDid As TextBox = grdv.FindControl("txtDeptid")

Dim tDname As TextBox = grdv.FindControl("txtDeptname")

Dim strQry As String = "Insert into Dept(deptid,dname) values(" + tDid.Text + ",'" + tDname.Text + "')"

Dim cmd As SqlCommand = New SqlCommand(strQry, cn)

Dim i As Integer = cmd.ExecuteNonQuery()

If (i > 0) Then

Response.Write("Record is Successfully Inserted")

Else

Response.Write("Record is not Inserted")

End If

cn.Close()

End Sub

End Class

web.config:-

</connectionStrings>

Create Table in Sql Server:-

create table Dept(deptid int constraint pr_key1 primary key,dname varchar(15))

Saturday 18 January 2014

Registration Form Validation using Java Script Regular Expression in Asp.Net

Ans:-

In Register.aspx :-

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

function CheckValidation() {

var userName = document.getElementById("txtName").value;

var pwd = document.getElementById("txtPwd").value;

var cPwd = document.getElementById("txtCPwd").value;

var mob = document.getElementById("txtMobile").value;

var email = document.getElementById("txtEmail").value;

var regxUserNm = /^[A-z]+$/;

var regxMob = /^[8-9][0-9]{9}$/;

var regxEmail = /^[A-z]{3,5}[\@][A-z]{3,5}[\.]{1}[A-z]{2,3}$/;

if (regxUserNm.test(userName) != true) {

alert("Invalid UserName");

return false;

}

if (regxEmail.test(email) != true) {

alert("Invalid EmailId");

return false;

}

if (regxMob.test(mob) != true) {

alert("Invalid PhoneNo");

return false;

}

if (pwd == "") {

alert("Enter Password");

return false;

}

if (pwd !=cPwd) {

alert("Enter Confirm password must be same as Pwd");

return false;

}

</script>

</head>

<body>

<div>

Rgistration Form

<table>

<tr>

</tr>

<tr>

<td>Email Id </td><td>:</td><td><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>Mobile No </td><td>:</td><td><asp:TextBox ID="txtMobile" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>Password </td><td>:</td><td><asp:TextBox ID="txtPwd" runat="server" Text=""></asp:TextBox></td>

</tr>

<tr>

<td>Confirm Password </td><td>:</td><td><asp:TextBox ID="txtCPwd" runat="server"></asp:TextBox></td>

</tr>

<tr>

</tr>

</table>

</div>

</center>

</form>

</body>

</html>