<%@ Register TagPrefix="Title" TagName="TitleShow" Src="/aspnetdata/Title.ascx" %>
<%@Page Debug=True %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web" %>
<html>
<head>
	<script language="vb" runat="server">
	Dim Source as SqlConnection
	dim d as string="SELECT Staff.*, Category.Content FROM Category INNER JOIN Staff ON Category.ID = Staff.Depart Order by SID"
	sub Page_load(sender as object,e as EventArgs)
		Source = Ctype(Session("NowEmpSQL"),SqlConnection)
		errMsg.Text=""
		if IsPostBack=false then
			bindNew(true)
		end if
	end sub
	sub bindNew(tag as Boolean) '结合至DataGrid
		if tag=true then
			Dim DS as DataSet = new DataSet()
			Dim NComm as SqlDataAdapter = new SqlDataAdapter(d, Source)
			NComm.Fill(DS, "staff")
			Session("NowDS")=DS '声明为Session
			staffShow.DataSource=DS.Tables("staff").DefaultView
			staffShow.DataBind()
		else
			Dim DS as DataSet = Ctype(Session("NowDS"),DataSet)
			staffShow.DataSource=DS.Tables("staff").DefaultView
			staffShow.DataBind()
		end if
	end sub
	sub AddClick(sender as object ,e as EventArgs)
		Dim NowDS as DataSet = Ctype(Session("NowDS"),DataSet)
		Dim NComm1 as SqlDataAdapter = new SqlDataAdapter("select * from Category where Type Like '部门' order by id", Source)
		NComm1.Fill(NowDS, "Category")
		dim ndt as DropDownList =depart '取得Panel中的部门  
		ndt.DataSource=NowDS.Tables("Category").DefaultView
		ndt.DataTextField="Content"
		ndt.DataValueField="ID"
		ndt.DataBind() '结合至部门
		NewStaff.Visible=true
		add.Visible=false
	end sub
	sub NewCancel(sender as object ,e as EventArgs ) '添加时取消
		NewStaff.Visible=false
		add.Visible=true
	end sub
	sub NewSave(sender as object ,e as EventArgs)
		if sid.Text="" then
			errMsg.Text="未输入id，无法保存"
			return
		end if
		dim Istring as string '更新的T-SQL
		Istring="Insert Into Staff (SID,Depart,cname,pid,TEL) Values ("
		Istring+= "'" & sid.Text + "'," & depart.SelectedItem.Value
		Istring+=", '" & cname.Text & "', '" & pid.Text + "'"
		Istring+=", '" & tel.Text & "')"
		try
			dim UpStaff as SqlCommand=new SqlCommand(Istring,Source)
			UpStaff.Connection.Open()
			UpStaff.ExecuteNonQuery()'执行保存
			UpStaff.Connection.Close()
			staffShow.EditItemIndex = -1
			bindNew(true)
			NewStaff.Visible=false
			add.Visible=true
		catch
			errMsg.Text="以" & Istring & "添加失败"
		end try
	end sub
	sub GetCalDrop(NowDepart as string,NowBday as string)
		dim c as Calendar
		c=Ctype(staffShow.Items(staffShow.EditItemIndex).Cells(4).FindControl("cal_Bday"),Calendar)
		if NowBday<>"" then 
			c.SelectedDate=Convert.ToDateTime(NowBday).Date '设定日期
			c.VisibleDate = Convert.ToDateTime(NowBday).Date '切换至目前日期
		else
			c.SelectedDate=DateTime.Today '月历等于今天
		end if
		Dim NowDs as DataSet = Ctype(Session("NowDS"),DataSet)
		Dim NComm1 as SqlDataAdapter = new SqlDataAdapter("select * from Category where Type Like '部门' order by id", Source)
		NComm1.Fill(NowDS, "Category")
		Dim dt as DropDownList '寻找部门控制项  
		dt=Ctype(staffShow.Items(staffShow.EditItemIndex).Cells(5).FindControl("edit_Depart"),DropDownList)
		dt.DataSource=NowDS.Tables("Category").DefaultView
		dt.DataTextField="Content"
		dt.DataValueField="ID"
		dt.DataBind() '结合至部门
		dim f as integer=0  '逐一比对
		Dim Row as DataRow
		for each Row in NowDS.Tables("Category").Rows
			if Row("Content").ToString()=NowDepart then
				dt.SelectedIndex=f '找到及设定选取索引值
				return
			end if
			f+=1
		next
	end sub
	sub staffShow_Edit(sender as object,e as DataGridCommandEventArgs)
		dim p as Label  =Ctype(staffShow.Items(e.Item.ItemIndex).Cells(5).Controls(1),Label)
		dim NowDepart as string = p.Text
		dim y as Label  =Ctype(staffShow.Items(e.Item.ItemIndex).Cells(4).Controls(1),Label)
		dim NowBday as string=y.Text
		staffShow.EditItemIndex = e.Item.ItemIndex '编辑
		bindNew(false)
		GetCalDrop(p.Text,y.Text)
	end sub
	sub Date_Selected(s as object , e as EventArgs )
		dim t as TextBox
		t=Ctype(staffShow.Items(staffShow.EditItemIndex).Cells(4).FindControl("edit_Bday"),TextBox)
		dim c as Calendar
		c=Ctype(staffShow.Items(staffShow.EditItemIndex).Cells(4).FindControl("cal_Bday"),Calendar)
		t.Text=c.SelectedDate.ToShortDateString()
        end sub
	sub staffShow_Cancel(sender as object,e as DataGridCommandEventArgs)
		staffShow.EditItemIndex = -1 '取消
		bindNew(false)
	end sub
	sub staffShow_Update(sender as object ,e as DataGridCommandEventArgs )
		Dim Nowsid as string =staffShow.DataKeys(staffShow.EditItemIndex).ToString()
		Dim Ustring as string '更新的T-SQL
		Ustring="Update Staff Set"
		Ustring+=" SID='" & Ctype(e.Item.Cells(1).Controls(0),TextBox).Text & "'"
		Ustring+=", cname='" & Ctype(e.Item.Cells(2).Controls(0),TextBox).Text & "'"
		Ustring+=", pid ='" & Ctype(e.Item.Cells(3).Controls(0),TextBox).Text & "'"
		Dim Inputdate as string =Ctype(e.Item.Cells(4).FindControl("edit_Bday"),TextBox).Text
		try
			if Inputdate<>"" then
				dim b as DateTime =Convert.ToDateTime(Ctype(e.Item.Cells(4).FindControl("edit_Bday"),TextBox).Text)
				Ustring+=", Bday ='" & b.ToString("yyyy/MM/dd") & "'"
			end if
			Ustring+=", Depart =" & Convert.ToInt32(Ctype(e.Item.Cells(5).FindControl("edit_Depart"),DropDownList).SelectedItem.Value)
			Ustring+=" where SID ='" & Nowsid & "'"
			dim UpStaff as SqlCommand=new SqlCommand(Ustring,Source)
			UpStaff.Connection.Open()
			UpStaff.ExecuteNonQuery() '执行保存
			UpStaff.Connection.Close()
			staffShow.EditItemIndex = -1
			bindNew(true)
		catch ex as Exception
			errMsg.Text="发生错误，无法保存"+ex.ToString()
		end try
	end sub
	</script>
</head>
<body background="/aspnetdata/images/winback.gif">
<Title:TitleShow id="BookT" Runat="server" />
<form runat="server">
<asp:Panel id="NewStaff" runat="server"
	BackColor="gainsboro"
	borderwidth="1"
	bordercolor="red"
	Height="150px"
	Width="300px"
	visible="false"
>
<blockquote><br>
请输入编号(主键)：<asp:textbox id="sid" runat="server" /><br>
请输入姓名：<asp:textbox id="cname" runat="server" /><br>
请输入身份证编号：<asp:textbox id="pid" runat="server" /><br>
请输入电话：<asp:textbox id="tel" runat="server" /><br>
请指定部门：<asp:dropdownlist id="depart" runat="server" /><br>
<asp:linkButton Text="Save" runat="server" OnClick="NewSave" />
<asp:linkButton Text="Cancel" runat="server" OnClick="NewCancel" /><br>
</blockquote>
</asp:Panel>
<asp:label id="errMsg" Runat="server" />
<asp:linkbutton Text="Add" id="add" runat="server" OnClick="AddClick" />
      <ASP:DataGrid id="staffShow" runat="server"
        BorderColor="black"
        BorderWidth="1"
        GridLines="Both"
        CellPadding="3"
        CellSpacing="0"
        Font-Name="Verdana"
        Font-Size="9pt"
        HeaderStyle-BackColor="#aaaadd"
	DataKeyField="SID"
	aUTOgENERATEcOLUMNS="FALSE"
	OnEditCommand="staffShow_Edit"
	OnCancelCommand="staffShow_Cancel"
	OnUpdateCommand="staffShow_Update">
      <Columns>
        <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Save" ItemStyle-Wrap="false"/>
	<asp:BoundColumn HeaderText="SID" DataField="SID" ItemStyle-Wrap="false"/>
	<asp:BoundColumn HeaderText="Name" DataField="cname"/>
	<asp:BoundColumn HeaderText="PID" DataField="pid"/>
	<asp:TemplateColumn HeaderText="Birthday" >
		<ItemTemplate>
			<asp:Label runat="server" Text=<%# DataBinder.Eval(Container.DataItem, "Bday","{0:yyyy/MM/dd}") %> id="Bday"/>
		</ItemTemplate>
		<EditItemTemplate>
			<asp:Textbox id="edit_Bday" Text=<%# DataBinder.Eval(Container.DataItem, "Bday","{0:yyyy/MM/dd}") %> runat="server" />
			<asp:Calendar id="cal_Bday" Font-Size="11px" onselectionchanged="Date_Selected" runat="server" />
		</EditItemTemplate>
	</asp:TemplateColumn>
	<asp:TemplateColumn HeaderText="Department" >
		<ItemTemplate>
			<asp:Label runat="server" Text=<%# DataBinder.Eval(Container.DataItem, "Content") %> id="Content"/>
		</ItemTemplate>
		<EditItemTemplate>
			<asp:DropDownList runat="server" id="edit_Depart" />
		</EditItemTemplate>
	</asp:TemplateColumn>
	</Columns>
</ASP:DataGrid>
</form>