Imports System.Data.SqlClient
Imports System.DateTime
Partial Class member
Inherits System.Web.UI.UserControl
Dim sys As New SySDB
'新增
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Me.txt_num.Text = "" Then Response.Write("<script>alert('請輸入卡號!')</script>") : Exit Sub
If Me.txt_name.Text = "" Then Response.Write("<script>alert('請輸入姓名!')</script>") : Exit Sub
If Me.txt_email.Text = "" Then RegularExpressionValidator3.Enabled = False
If Me.txt_passport.Text = "" Then RegularExpressionValidator1.Enabled = False
addmember()
End Sub
Private Sub addmember()
sys.InsertCommand = "insert into member(CardId,name,age,birthday,sex,address,mail,phone,passport,addtime) values (@CardId,@name,@age,@birthday,@sex,@address,@mail,@phone,@passport,@addtime)"
sys.InsertParameters.Add("CardId", Me.txt_num.Text.Trim)
sys.InsertParameters.Add("name", Me.txt_name.Text.Trim)
sys.InsertParameters.Add("age", Me.txt_age.Text.Trim)
sys.InsertParameters.Add("birthday", Me.year.Text.Trim.ToString)
sys.InsertParameters.Add("sex", Me.sex.SelectedValue.Trim)
sys.InsertParameters.Add("address", Me.txt_address.Text.Trim)
sys.InsertParameters.Add("mail", Me.txt_email.Text.Trim)
sys.InsertParameters.Add("phone", Me.txt_phone.Text.Trim)
sys.InsertParameters.Add("passport", Me.txt_passport.Text.Trim)
sys.InsertParameters.Add("addtime", Format(Now, "yyyy/MM/dd")) '加入操作時間
Me.Button1.Attributes.Add("onclick", "return confirm('請先確定資料是否正確?')")
Try
If sys.Insert > 0 Then
Response.Write("<script>alert('恭喜!你已經成功新增一個會員!')</script>")
Else
Response.Write("<script>alert('新增會員失敗')</script>")
End If
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
cleardata()
Me.btn_delete.Enabled = False
Me.btn_edit.Enabled = False
Me.txt_num.ReadOnly = False
Me.txt_num.BackColor = Drawing.Color.Yellow
End Sub
Private Sub cleardata()
Me.txt_num.Text = ""
Me.txt_name.Text = ""
Me.txt_age.Text = ""
Me.year.Text = ""
Me.sex.SelectedValue = "女"
Me.txt_address.Text = ""
Me.txt_email.Text = ""
Me.txt_phone.Text = ""
Me.txt_passport.Text = ""
End Sub
' Private Sub loadmonth()
' Dim i As Integer = 1
' For i = 1 To 12 Step 1
' month.Items.Add(i)
'Next
' End Sub
' Private Sub loadday()
' 'Dim i As Integer
' Dim j As Integer = 1
' For j = 1 To 31 Step 1
' day.Items.Add(j)
' 'j = j + 1
' Next
' End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Me.Page.IsPostBack = False Then
load_total()
End If
End Sub
Sub load_total()
sys.SelectCommand = "SELECT count(*) as a1 FROM [member]"
sys.DataSourceMode = SqlDataSourceMode.DataReader
Dim dr As SqlDataReader = sys.Select()
While dr.Read
Me.total.Text = "現有會員:" & dr("a1") & "位"
End While
End Sub
'查找
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
sys.SelectCommand = "select * from member where CardId like @CardId"
sys.SelectParameters.Add(New ControlParameter("CardId", Me.txt_num.ID))
Me.Controls.Add(sys)
Dim dv As Data.DataView = sys.Select(New DataSourceSelectArguments)
Dim i As Integer
'讀入資料,使用for循环来绑定资料
For i = 0 To dv.Table.Rows.Count - 1
Me.txt_num.Text = dv.Table.Rows(i)("CardId").ToString
Me.txt_name.Text = dv.Table.Rows(i)("name").ToString
Me.txt_age.Text = dv.Table.Rows(i)("age").ToString
Me.year.Text = dv.Table.Rows(i)("birthday").ToString
Me.sex.SelectedValue = dv.Table.Rows(i)("sex").ToString
Me.txt_address.Text = dv.Table.Rows(i)("address").ToString
Me.txt_email.Text = dv.Table.Rows(i)("mail").ToString
Me.txt_passport.Text = dv.Table.Rows(i)("passport").ToString
Me.txt_phone.Text = dv.Table.Rows(i)("phone").ToString
Next
If Me.txt_num.Text <> "" Then
Me.btn_delete.Enabled = True
Me.btn_edit.Enabled = True
Me.txt_num.ReadOnly = True
Me.txt_num.BackColor = Drawing.Color.Red
End If
End Sub
Protected Sub btn_clear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_clear.Click
cleardata()
Me.btn_delete.Enabled = False
Me.btn_edit.Enabled = False
Me.txt_num.ReadOnly = False
Me.txt_num.BackColor = Drawing.Color.Yellow
End Sub
'編輯
Protected Sub btn_edit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_edit.Click
Try
sys.UpdateCommand = "update member set name = @name, age=@age,birthday=@birthday,sex=@sex,address=@address,mail=@mail,phone=@phone,passport=@passport where CardId=@CardId"
sys.UpdateParameters.Add("name", Me.txt_name.Text.Trim.ToString)
sys.UpdateParameters.Add("age", Me.txt_age.Text.Trim)
sys.UpdateParameters.Add("birthday", Me.year.Text.Trim.ToString)
sys.UpdateParameters.Add("sex", Me.sex.SelectedValue.ToString)
sys.UpdateParameters.Add("address", Me.txt_address.Text.Trim.ToString)
sys.UpdateParameters.Add("mail", Me.txt_email.Text.Trim.ToString)
sys.UpdateParameters.Add("phone", Me.txt_phone.Text.Trim.ToString)
sys.UpdateParameters.Add("passport", Me.txt_passport.Text.Trim.ToString)
sys.UpdateParameters.Add("CardId", Me.txt_num.Text.Trim)
sys.Update()
Me.btn_edit.Attributes.Add("onclick", "return confirm('請先確定會員資料是否正確?')") '彈出提示窗口
If sys.Update > 0 Then
Response.Write("<script>alert('會員資料更新成功!')</script>")
Else
Response.Write("<script>alert('會員資料更新失敗!')</script>")
End If
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
cleardata()
Me.btn_delete.Enabled = False
Me.btn_edit.Enabled = False
Me.txt_num.ReadOnly = False
Me.txt_num.BackColor = Drawing.Color.Yellow
End Sub
'刪除操作
Protected Sub btn_delete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_delete.Click
Try
sys.DeleteCommand = "delete from member where CardId =@CardId"
sys.DeleteParameters.Add("CardId", Me.txt_num.Text.Trim)
sys.Delete()
Me.btn_delete.Attributes.Add("onclick", "return confirm('是否要刪除此會員?')")
If sys.Delete > 0 Then
Response.Write("<script>alert('已經成功刪除此會員!')</script>")
End If
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
cleardata()
Me.btn_delete.Enabled = False
Me.btn_edit.Enabled = False
Me.txt_num.ReadOnly = False
Me.txt_num.BackColor = Drawing.Color.Yellow
End Sub
End Class