<%@import namespace="system.data.SqlClient"%>
<%@import namespace="system.data"%>
<html>
<head>
<title>第二十九例  Sql Server数据库的访问操作</title>

<SCRIPT LANGUAGE="VB" debug=true runat="server">

Private Sqlserver AS String

'定义当页面重新装载时的事件
Sub Page_Load(myList AS Object,E as EventArgs)
	Sqlserver = "server=cm;database=model;uid=sa;pwd="
		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("ex29-1VB.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 = "<font size=+1 color=red>每个选项都不能为空！</font><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 ID"
	dbComm = New SqlDataAdapter(Sql,conn)
	dbComm.Fill(ds,"MemberData")
	MemberData.DataSource = ds.Tables("MemberData").DefaultView
	MemberData.DataBind()
End Sub
</SCRIPT>
</head>
<Body>
<form runat=server>

<asp:panel id="MemberFunctionUpdate" runat="server">

<H3><font face="Verdana">第二十九例  Sql Server数据库的访问操作</font></H3>
<ASP:DataGrid id="MemberData" runat="server"
DataKeyField="ID" 
BorderColor="black" 
BorderWidth="1" 
GridLines="Both" 
CellPadding="3" 
CellSpacing="0" 
Font-Name="Verdana" Font-Size="12pt" 
HeaderStyle-BackColor="#aaaadd" 
AlternatingItemStyle-BackColor="#eeeeee" 
AutoGenerateColumns=false 
OnEditCommand="MemberData_Edit"
OnUpdateCommand="MemberData_Update"
OnCancelCommand="MemberData_Cancel"
OnDeleteCommand = "MemberData_Delete"
>

<Columns>

<asp:ButtonColumn Text="删除" CommandName="Delete"/>

<asp:EditCommandColumn 
EditText="编辑"
CancelText="取消"
UpdateText="更新"/>

<asp:TemplateColumn  HeaderText="姓名">
<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="昵称">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("LastName")%>'
runat="server"/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="LastName_up"
Text='<%# Container.DataItem("LastName")%>'
runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

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

<asp:TemplateColumn  HeaderText="地址二">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("Address2")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="Address2_up"
Text='<%# Container.DataItem("Address2")%>'
runat="server" />
</EditItemTEMPLATE>
</asp:TemplateColumn>

<asp:TemplateColumn  HeaderText="城市">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("City")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="City_up"
Text='<%# Container.DataItem("City")%>'
runat="server" />
</EditItemTEMPLATE>
</asp:TemplateColumn>

<asp:TemplateColumn  HeaderText="地区">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("State")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="State_up"
Text='<%# Container.DataItem("State")%>'
runat="server" />
</EditItemTEMPLATE>
</asp:TemplateColumn>

<asp:TemplateColumn  HeaderText="邮编">
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("Zip")%>'
runat="server"/>
</ItemTEMPLATE>
<EditItemTemplate>
<asp:TextBox id="Zip_up"
Text='<%# Container.DataItem("Zip")%>'
runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

</Columns>
</asp:DataGrid>

</BR>

<asp:button  TEXT="增加用户"  runat="server"  onClick="newUser" />
</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>
</form>

</body>
</html>