<%@import namespace="system.data.SQLClient"%>
<%@import namespace="system.data"%>

<SCRIPT LANGUAGE="vb" debug=true RUNAT="server">

Private SQLserver AS String

'定義當頁面重新裝載時的事件
Sub Page_Load(myList AS Object,E as EventArgs)
	SQLserver = "server=localhost;database=model;Trusted_Connection=yes;"
		If NOT Page.IsPostBack
			MemberFunctionUpdate.Visible = true
			MemberFunctionAdd.Visible = false
			BindMemberData()
		End If
End Sub

'定義函數Panel的可見控件
Sub PanelReset()
	MemberFunctionUpdate.Visible = true
	MemberFunctionAdd.Visible = false
End Sub

'定義函數：新增用戶時的可見控件
Sub newUser(sender As Object , e As EventArgs)
	MemberFunctionUpdate.Visible = false
	MemberFunctionAdd.Visible = true
End Sub

'定義函數：取消成員數據編輯時候的事件
Sub MemberData_Cancel(Sender As Object, E As DataGridCommandEventArgs)
        MemberData.EditItemIndex = -1
        BindMemberData()
End Sub

'定義函數：編輯成員數據編輯時候的事件
Sub MemberData_Edit(Sender As Object, E As DataGridCommandEventArgs)
        MemberData.EditItemIndex = E.Item.ItemIndex
        BindMemberData()
End Sub

'定義函數：取消新增用戶時進入的頁面
Sub OnCancel(sender As Object , e As System.EventArgs)
	Response.Redirect("upgrate.aspx")
End Sub

'定義函數：新增用戶
Sub MemberData_Add(sender As Object , e As System.EventArgs)
	Dim addUserCommand  	As SQLCommand
	Dim delMember 		as String
        Dim conn            	AS SQLConnection     
        Dim NewUserIn		AS String
        
        conn = New SQLConnection(SQLServer)
        conn.open()	

		If FirstName.Value = "" OR LastName.Value = "" OR Address1.Value = "" OR City.Value = "" OR UserState.Value = "" OR Zip.Value = "" then
			validationFail.Text = "All Fields in Bold Are Required<BR><BR>"
		Else
        
        NewUserIn = "INSERT INTO MemberData Values (@FirstName,@LastName,@Address1,@Address2,@City,@State,@Zip)"
		addUserCommand = New SQLCommand(NewUserIn, conn)
		
        addUserCommand.Parameters.Add(New SQLParameter("@FirstName", SQLdbType.nVarChar, 20))
        addUserCommand.Parameters("@FirstName").Value = FirstName.value
        
        addUserCommand.Parameters.Add(New SQLParameter("@LastName", SQLdbType.nVarChar, 20))
        addUserCommand.Parameters("@LastName").Value = LastName.value
        
        addUserCommand.Parameters.Add(New SQLParameter("@Address1", SQLdbType.nVarChar, 50))
        addUserCommand.Parameters("@Address1").Value = Address1.value
        
        addUserCommand.Parameters.Add(New SQLParameter("@Address2", SQLdbType.nVarChar, 70))
        addUserCommand.Parameters("@Address2").Value = Address2.value
        
        addUserCommand.Parameters.Add(New SQLParameter("@City", SQLdbType.nVarChar, 70))
        addUserCommand.Parameters("@City").Value = City.value
        
        addUserCommand.Parameters.Add(New SQLParameter("@State", SQLdbType.nVarChar, 20))
        addUserCommand.Parameters("@State").Value = UserState.value
        
        addUserCommand.Parameters.Add(New SQLParameter("@Zip", SQLdbType.nVarChar, 20))
        addUserCommand.Parameters("@Zip").Value = Zip.value
		adduserCommand.executeReader()
		PanelReset()
		BindMemberData()
		End If
End Sub

'定義函數：更新用戶數據
Sub MemberData_Update(Sender As Object, E As DataGridCommandEventArgs)
        Dim updateMember    As String
        Dim upCommand       As SQLCommand
        Dim myParam         AS SQLParameter
        Dim memberFormData  As TextBox
        Dim connState       As Integer
        Dim paramNumber     As Integer
        Dim paramName       As String
        Dim conn            AS SQLConnection

        conn = New SQLConnection(SQLServer)
        conn.Open()
                
        updateMember = "Update MemberData Set FirstName = @FirstName,"
        updateMember = updateMember & "LastName=@LastName,Address1=@Address1,"
        updateMember = updateMember & "Address2=@Address2,City=@City,State=@state,"
        updateMember = updateMember & "Zip=@zip Where ID = @ID"
        
        upCommand = New SQLCommand(updateMember, conn)
        upCommand.Parameters.Add(new SQLParameter("@ID", SQLdbType.nVarChar, 50))
        upCommand.Parameters.Add(new SQLParameter("@FirstName", SQLdbType.nVarChar, 50))
        upCommand.Parameters.Add(new SQLParameter("@LastName", SQLdbType.nVarChar, 50))
        upCommand.Parameters.Add(new SQLParameter("@Address1", SQLdbType.nVarChar, 50))
        upCommand.Parameters.Add(new SQLParameter("@Address2", SQLdbType.nVarChar, 50))
        upCommand.Parameters.Add(new SQLParameter("@City", SQLdbType.nVarChar, 50))
        upCommand.Parameters.Add(new SQLParameter("@State", SQLdbType.nVarChar, 50))
        upCommand.Parameters.Add(new SQLParameter("@Zip", SQLdbType.nVarChar, 50))
        
        upCommand.Parameters.Item("@ID").Value = MemberData.DataKeys.Item(E.Item.ItemIndex)
        
        For paramNumber = 1 To upCommand.Parameters.Count - 1
            myParam = upCommand.Parameters(paramNumber)
            paramName = myParam.ParameterName
            memberFormData = E.Item.FindControl(paramName.SubString(1)&"_up")
            upCommand.Parameters.Item(paramName).Value = memberFormData.Text
        Next
        upCommand.ExecuteReader()
        MemberData.EditItemIndex = -1 
        BindMemberData()                              
End Sub

'定義函數：刪除用戶數據
Sub MemberData_Delete(Sender As Object, E As DataGridCommandEventArgs)
		Dim delCommand  	As SQLCommand
		Dim delMember 		as String
        Dim conn            AS SQLConnection
        
        conn = New SQLConnection(SQLServer)
        conn.open()
        
        delMember = "Delete from MemberData Where ID = @ID"   
        delCommand = New SQLCommand(delMember, conn)           
        delCommand.Parameters.Add(new SQLParameter("@ID", SQLdbType.nVarChar, 50))       
	delCommand.Parameters.Item("@ID").Value = MemberData.DataKeys.Item(E.Item.ItemIndex)
	delCommand.ExecuteReader()
        MemberData.EditItemIndex = -1 
        BindMemberData()   		
End Sub

'定義函數：顯示用戶數據
Sub BindMemberData()
	Dim conn AS SQLConnection
	Dim dbComm AS SQLDataAdapter
	Dim SQL AS String
	Dim ds AS New DataSet
	conn = New SQLConnection(SQLserver)
	SQL = "Select * from MemberData ORDER BY LastName"
	dbComm = New SQLDataAdapter(SQL,conn)
	dbComm.Fill(ds,"MemberData")
	MemberData.DataSource = ds.Tables("MemberData").DefaultView
	MemberData.DataBind()
End Sub
</SCRIPT>


<asp:panel id="MemberFunctionUpdate" runat="server">
<title>第三十四例 程序構架與代碼分離（四）</title>
<BR><p></P>
      <h3><font face="Verdana">第三十四例 程序構架與代碼分離（四）</font></h3>
<BR><BR>
<H2>注 冊 成 員 列 表 一 覽</H2>

<asp:DataGrid id="MemberData"
DataKeyField="ID"
AutoGenerateColumns="false"
OnEditCommand="MemberData_Edit"
OnUpdateCommand="MemberData_Update"
OnCancelCommand="MemberData_Cancel"
OnDeleteCommand = "MemberData_Delete"
bordercolor="navy"
cellpadding="5"
Runat="server">

<Columns>

<asp:ButtonColumn Text="刪除" CommandName="Delete"
HeaderStyle-backcolor="lightyellow"/>


<asp:EditCommandColumn 
EditText="編輯"
CancelText="取消"
UpdateText="更新"
HeaderStyle-backcolor="lightyellow"/>


<asp:TemplateColumn  HeaderText="姓名" 
HeaderStyle-backcolor="lightyellow"
ItemStyle-Wrap="false">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("FirstName")%>'
runat="server"/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="FirstName_up"
Text='<%# Container.DataItem("FirstName")%>'
Size="10"
runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>



<asp:TemplateColumn  HeaderText="昵稱" 
ItemStyle-Wrap="false"
HeaderStyle-backcolor="lightyellow">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("LastName")%>'
Size="10"
runat="server"/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="LastName_up"
Text='<%# Container.DataItem("LastName")%>'
Size="10"
runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>



<asp:TemplateColumn  HeaderText="地址一" 
HeaderStyle-backcolor="lightyellow"
ItemStyle-Wrap="false">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("Address1")%>'
Size="10"
ItemStyle-Wrap="false"
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="Address1_up"
Text='<%# Container.DataItem("Address1")%>'
Size="30"
runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>



<asp:TemplateColumn  HeaderText="地址二" 
ItemStyle-Wrap="false"
HeaderStyle-backcolor="lightyellow">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("Address2")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="Address2_up"
Text='<%# Container.DataItem("Address2")%>'
Size="30"
runat="server" />
</EditItemTEMPLATE>
</asp:TemplateColumn>



<asp:TemplateColumn  HeaderText="城市" 
ItemStyle-Wrap="false"
HeaderStyle-backcolor="lightyellow">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("City")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="City_up"
Text='<%# Container.DataItem("City")%>'
Size="15"
runat="server" />
</EditItemTEMPLATE>
</asp:TemplateColumn>



<asp:TemplateColumn  HeaderText="地區" 
ItemStyle-Wrap="false"
HeaderStyle-backcolor="lightyellow">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("State")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="State_up"
Text='<%# Container.DataItem("State")%>'
Size="12"
runat="server" />
</EditItemTEMPLATE>
</asp:TemplateColumn>



<asp:TemplateColumn  HeaderText="郵編"
ItemStyle-Wrap="false"
HeaderStyle-backcolor="lightyellow">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("Zip")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="Zip_up"
Text='<%# Container.DataItem("Zip")%>'
Size="8"
runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:EditCommandColumn 
EditText="編輯"
CancelText="取消"
UpdateText="更新"
ItemStyle-Wrap="false"
HeaderStyle-backcolor="lightyellow"/>
</Columns>
</asp:DataGrid>
</property>
</BR></BR>

<CENTER>
<asp:button  TEXT="增加用戶"  runat="server"  onClick="newUser" />
</CENTER>
</asp:panel>
<asp:panel  id="MemberFunctionAdd"  runat="server">
<CENTER>
<H3>增加一個用戶</H3>

<asp:label id="validationFail"  runat="server" />

<TABLE>
<TR>
<TD valign="top"><B>姓名:</B></TD>
<TD valign="top"><INPUT TYPE="text"  id="FirstName" runat="server"></TD>
</TR>
<TR>
<TD valign="top"><B>昵稱:</B></TD>
<TD valign="top"><INPUT TYPE="text"  id="LastName" runat="server"></TD>
</TR>
<TR>
<TD valign="top"><B>地址一:</B></TD>
<TD valign="top"><INPUT TYPE="text"  id="Address1" runat="server"></TD>
</TR>
<TR>
<TD valign="top"><B>地址二:</B></TD>
<TD valign="top"><INPUT TYPE="text"  id="Address2" runat="server"></TD>
</TR>
<TR>
<TD valign="top"><B>城市:</B></TD>
<TD valign="top"><INPUT TYPE="text"  id="City" runat="server"></TD>
</TR>
<TR>
<TD valign="top"><B>地區:</B></TD>
<TD valign="top"><INPUT TYPE="text"  id="UserState" runat="server"></TD>
</TR>
<TR>
<TD valign="top"><B>郵編:</B></TD>
<TD valign="top"><INPUT TYPE="text"  id="Zip" runat="server"></TD>
</TR>
<TR>
<TD valign="top" colspan="2"  align="center">
<BR><BR>
<INPUT TYPE="Submit"  VALUE="提交"  runat="server"  onServerClick="MemberData_Add" />
<INPUT TYPE="Submit"  VALUE="取消"  runat="server"  onServerClick="OnCancel" />
</TD>
</TR>
</TABLE>
</asp:panel>


</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>