USE [ThaiCreateDB]
GO
/****** Object: Table [dbo].[Menu] Script Date: 01/22/2013 10:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Menu](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](150) NULL,
[ParentID] [int] NULL,
[FormName] [varchar](150) NULL,
[RowForm] [int] NULL,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED
(
[MenuID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Menu] ADD CONSTRAINT [DF_Menu_MenuName] DEFAULT ('') FOR [MenuName]
GO
ALTER TABLE [dbo].[Menu] ADD CONSTRAINT [DF_Menu_ParentMenuID] DEFAULT ((0)) FOR [ParentID]
GO
ALTER TABLE [dbo].[Menu] ADD CONSTRAINT [DF_Menu_CallForMenuName] DEFAULT ('') FOR [FormName]
GO
ALTER TABLE [dbo].[Menu] ADD CONSTRAINT [DF_Menu_SEQ] DEFAULT ((0)) FOR [RowForm]
GO
- table : User Code (SQL)
USE [ThaiCreateDB]
GO
/****** Object: Table [dbo].[User] Script Date: 01/22/2013 11:00:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[User](
[UserId] [varchar](10) NOT NULL,
[UserName] [varchar](100) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
- table : UserMenu Code (SQL)
USE [ThaiCreateDB]
GO
/****** Object: Table [dbo].[UserMenu] Script Date: 01/22/2013 11:01:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserMenu](
[UserId] [varchar](10) NOT NULL,
[MenuId] [int] NOT NULL,
CONSTRAINT [PK_UserMenu] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[MenuId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
- views : vw_usermenu Code (SQL)
SELECT dbo.Menu.MenuID, dbo.Menu.MenuName, dbo.UserMenu.UserId, dbo.Menu.RowForm, dbo.Menu.ParentID, dbo.Menu.FormName
FROM dbo.Menu INNER JOIN
dbo.UserMenu ON dbo.Menu.MenuID = dbo.UserMenu.MenuId
Imports System.Data
Imports System.Text
Public Class FrmSearch
Public dtSearch As New DataTable
Public dvSearch As New DataView
Private Sub FrmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Me.dtSearch Is Nothing Then
Me.dgvData.DataSource = Me.dtSearch
End If
End Sub
End Class
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Public Class ConnectionDBClass
Public con As New SqlConnection
Public com As New SqlCommand
Public da As New SqlDataAdapter
Public tr As SqlTransaction
Public Function connectDB() As Boolean
If con.State = ConnectionState.Open Then con.Close()
con.ConnectionString = "Data Source=127.0.0.1\sql2008;Initial Catalog=ThaiCreateDB;User ID=sa;Password=itserver;"
con.Open()
Return True
End Function
End Class
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Public Class FrmSetupUser
Dim _frmSearch As FrmSearch ' ประกาศ ตัวแปร เพื่อเรียกใช้งาน ฟอร์ม Search
Dim _condb As New ConnectionDBClass ' ประกาศ ตัวแปร เพื่อเรียกใช้งาน คลาส ConnectionDBClass
Dim sb As New StringBuilder
Private Sub FrmSetupUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.txtUserId.Text = ""
Me.txtUserName.Text = ""
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If Me.txtUserId.Text = "" Then
MsgBox("กรุณากรอก ข้อมูล!!", MsgBoxStyle.Critical, "Error")
Me.txtUserId.Focus()
Exit Sub
End If
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Insert into [User] (UserId,UserName) ")
sb.Append(" values ('" & Me.txtUserId.Text & "', ")
sb.Append(" '" & Me.txtUserName.Text & "') ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtUserId.Text = ""
Me.txtUserName.Text = ""
MsgBox("บันทึกข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
End Sub
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
If Me.txtUserId.Text = "" Or Me.txtUserName.Text = "" Then
MsgBox("กรุณากรอก ข้อมูล!!", MsgBoxStyle.Critical, "Error")
Me.txtUserName.Focus()
Exit Sub
End If
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Update [User] Set ")
sb.Append(" UserName = '" & Me.txtUserName.Text & "' ")
sb.Append(" Where UserId = '" & Me.txtUserId.Text & "'")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtUserId.Text = ""
Me.txtUserName.Text = ""
MsgBox("แก้ไขข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If Me.txtUserId.Text = "" Then
MsgBox("กรุณาตรวจสอบ ข้อมูล ก่อน ทำการลบ!!", MsgBoxStyle.Critical, "Error")
Me.txtUserId.Focus()
Exit Sub
End If
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Delete From [User] ")
sb.Append(" Where UserId = '" & Me.txtUserId.Text & "' ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
sb.Remove(0, sb.Length)
sb.Append(" Delete From UserMenu ")
sb.Append(" Where UserId = '" & Me.txtUserId.Text & "' ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtUserId.Text = ""
Me.txtUserName.Text = ""
MsgBox("ลบข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Me._frmSearch = New FrmSearch
If Me._condb.connectDB Then
sb.Remove(0, sb.Length)
sb.Append(" Select * from [User] ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsUser As New DataSet
Me._condb.da.Fill(dsUser, "dtUser")
If Not dsUser Is Nothing Then
Me._frmSearch.dtSearch = dsUser.Tables(0)
AddHandler Me._frmSearch.txtSearch.TextChanged, AddressOf txtSearch_TextChanged 'สร้าง event TextChanged
AddHandler Me._frmSearch.dgvData.CellMouseDoubleClick, AddressOf dgvData_CellMouseDoubleClick 'สร้าง event CellMouseDoubleClick
Me._frmSearch.ShowDialog()
End If
End If
End Sub
Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
If Me._frmSearch.txtSearch.Text <> "" Then
Me._frmSearch.dvSearch = Me._frmSearch.dtSearch.DefaultView
Me._frmSearch.dvSearch.RowFilter = " UserId like '%" & Me._frmSearch.txtSearch.Text & "%' "
Else
Me._frmSearch.dvSearch.RowFilter = ""
Me._frmSearch.dgvData.DataSource = Me._frmSearch.dtSearch
End If
End Sub
Sub dgvData_CellMouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs)
If (e.RowIndex <> -1) Then
Me.txtUserId.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("UserId").Value.ToString()
Me.txtUserName.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("UserName").Value.ToString()
Me._frmSearch.Close()
End If
End Sub
End Class
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Public Class FrmSetupMenu
Dim _frmSearch As FrmSearch
Dim _condb As New ConnectionDBClass
Dim sb As New StringBuilder
Private Sub FrmSetupMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.txtFormName.Text = ""
Me.txtMenuName.Text = ""
Me.txtRowForm.Text = ""
Me.txtMenuId.Text = ""
Me.GetParentMenu()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If Me.txtMenuName.Text = "" Then
MsgBox("กรุณากรอก ข้อมูล!!", MsgBoxStyle.Critical, "Error")
Me.txtMenuName.Focus()
Exit Sub
End If
If Me.txtFormName.Text = "" And Me.chbNoForm.Checked = False Then
MsgBox("กรุณากรอก ข้อมูล!!", MsgBoxStyle.Critical, "Error")
Me.txtFormName.Focus()
Exit Sub
End If
Me.GetMaxMenuId()
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Insert into Menu (MenuName,ParentID,Formname,RowForm) ")
sb.Append(" values ('" & Me.txtMenuName.Text & "', ")
If Me.chbParent.Checked Then
sb.Append(" '0', ")
Else
sb.Append(" " & CInt(Me.cboParentMenu.SelectedValue) & ", ")
End If
sb.Append(" '" & Me.txtFormName.Text & "', ")
sb.Append(" " & CInt(Me.txtRowForm.Text) & ") ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtMenuName.Text = ""
Me.txtFormName.Text = ""
Me.txtRowForm.Text = ""
Me.txtRowForm.Text = ""
Me.txtMenuId.Text = ""
MsgBox("บันทึกข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
Me.GetParentMenu()
End Sub
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
If Me.txtMenuName.Text = "" Or Me.txtFormName.Text = "" Then
MsgBox("กรุณากรอก ข้อมูล!!", MsgBoxStyle.Critical, "Error")
Me.txtFormName.Focus()
Exit Sub
End If
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Update Menu Set ")
sb.Append(" MenuName = '" & Me.txtMenuName.Text & "' ")
sb.Append(" ParentID = " & CInt(Me.cboParentMenu.SelectedValue) & " ")
sb.Append(" Formname = '" & Me.txtFormName.Text & "' ")
sb.Append(" RowForm = " & CInt(Me.txtRowForm.Text) & " ")
sb.Append(" Where MenuId = " & CInt(Me.txtMenuId.Text) & " ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtMenuName.Text = ""
Me.txtFormName.Text = ""
Me.txtRowForm.Text = ""
Me.txtRowForm.Text = ""
Me.txtMenuId.Text = ""
MsgBox("แก้ไขข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
Me.GetParentMenu()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If Me.txtMenuId.Text = "" Then
MsgBox("กรุณาตรวจสอบ ข้อมูล ก่อน ทำการลบ!!", MsgBoxStyle.Critical, "Error")
Me.txtMenuName.Focus()
Exit Sub
End If
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Delete From Menu ")
sb.Append(" Where MenuId = '" & Me.txtMenuId.Text & "' ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
sb.Remove(0, sb.Length)
sb.Append(" Delete From UserMenu ")
sb.Append(" Where MenuId = '" & Me.txtMenuId.Text & "' ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtMenuName.Text = ""
Me.txtFormName.Text = ""
Me.txtRowForm.Text = ""
Me.txtRowForm.Text = ""
Me.txtMenuId.Text = ""
MsgBox("ลบข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
Me.GetParentMenu()
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Me._frmSearch = New FrmSearch
If Me._condb.connectDB Then
sb.Remove(0, sb.Length)
sb.Append(" Select * from Menu ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsUser As New DataSet
Me._condb.da.Fill(dsUser, "dtMenu")
If Not dsUser Is Nothing Then
Me._frmSearch.dtSearch = dsUser.Tables(0)
AddHandler Me._frmSearch.txtSearch.TextChanged, AddressOf txtSearch_TextChanged
AddHandler Me._frmSearch.dgvData.CellMouseDoubleClick, AddressOf dgvData_CellMouseDoubleClick
Me._frmSearch.ShowDialog()
End If
End If
End Sub
Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
If Me._frmSearch.txtSearch.Text <> "" Then
Me._frmSearch.dvSearch = Me._frmSearch.dtSearch.DefaultView
Me._frmSearch.dvSearch.RowFilter = " MenuName like '%" & Me._frmSearch.txtSearch.Text & "%' or Formname like '%" & Me._frmSearch.txtSearch.Text & "%' "
Else
Me._frmSearch.dvSearch.RowFilter = ""
Me._frmSearch.dgvData.DataSource = Me._frmSearch.dtSearch
End If
End Sub
Sub dgvData_CellMouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs)
If (e.RowIndex <> -1) Then
Me.txtMenuId.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("MenuId").Value.ToString()
Me.cboParentMenu.SelectedValue = CInt(Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("ParentID").Value.ToString())
Me.txtMenuName.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("MenuName").Value.ToString()
Me.txtFormName.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("Formname").Value.ToString()
Me.txtRowForm.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("RowForm").Value.ToString()
Me._frmSearch.Close()
End If
End Sub
' checkbox ไม่มีฟอร์ม
Private Sub chbNoForm_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chbNoForm.CheckedChanged
If Me.chbNoForm.Checked Then
Me.txtFormName.Enabled = False
Else
Me.txtFormName.Enabled = True
End If
End Sub
Private Sub GetParentMenu()
If Me._condb.connectDB() Then
sb.Remove(0, sb.Length)
sb.Append(" select * from Menu ")
sb.Append(" where ParentID = 0 ")
sb.Append(" order by ParentID,RowForm ")
Dim dsMenu As New DataSet
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Me._condb.da.Fill(dsMenu, "dtMenu")
If Not dsMenu Is Nothing Then
Me.cboParentMenu.DisplayMember = "MenuName"
Me.cboParentMenu.DataSource = dsMenu.Tables(0)
Me.cboParentMenu.ValueMember = "MenuId"
Me.cboParentMenu.SelectedIndex = -1
Me.cboParentMenu.Update()
End If
End If
End Sub
Private Sub GetMaxMenuId()
If Me._condb.connectDB() Then
sb.Remove(0, sb.Length)
sb.Append(" select RowForm from Menu ")
If Me.cboParentMenu.Text = "" Then
sb.Append(" where ParentID = 0 ")
Else
sb.Append(" where ParentID = " & CInt(Me.cboParentMenu.SelectedValue) & " ")
End If
sb.Append(" order by RowForm desc ")
Dim dsMenu As New DataSet
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Me._condb.da.Fill(dsMenu, "dtMenu")
If Not dsMenu Is Nothing Then
Me.txtRowForm.Text = CStr(CInt(dsMenu.Tables(0).Rows(0).Item("RowForm")) + 1)
Else
Me.txtRowForm.Text = "0"
End If
End If
End Sub
End Class
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Public Class FrmMenuSecure
Dim _frmSearch As FrmSearch
Dim _condb As New ConnectionDBClass
Dim sb As New StringBuilder
Private Sub FrmMenuSecure_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.txtUserId.Text = ""
Me.txtUserName.Text = ""
Me.txtMenuId.Text = ""
Me.txtMenuName.Text = ""
Me.txtFormName.Text = ""
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If Me.txtUserId.Text = "" Then
MsgBox("กรุณากรอก ข้อมูล!!", MsgBoxStyle.Critical, "Error")
Me.txtUserId.Focus()
Exit Sub
End If
If Me.txtMenuId.Text = "" Then
MsgBox("กรุณากรอก ข้อมูล!!", MsgBoxStyle.Critical, "Error")
Me.txtMenuId.Focus()
Exit Sub
End If
'ตรวจสอบ ข้อมูล ก่อน บันทึก
If Me.GetUserMenuByUserId(Me.txtUserId.Text, Me.txtMenuId.Text) = False Then
MsgBox("ข้อมูลซ้ำ !!", MsgBoxStyle.Critical, "Error")
Exit Sub
End If
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Insert into UserMenu (UserId,MenuId) ")
sb.Append(" values ('" & Me.txtUserId.Text & "', ")
sb.Append(" '" & Me.txtMenuId.Text & "') ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtMenuId.Text = ""
Me.txtMenuName.Text = ""
Me.txtFormName.Text = ""
MsgBox("บันทึกข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If Me.txtMenuId.Text = "" Then
MsgBox("กรุณาตรวจสอบ ข้อมูล ก่อน ทำการลบ!!", MsgBoxStyle.Critical, "Error")
Me.txtMenuName.Focus()
Exit Sub
End If
If Me._condb.connectDB() Then
Me._condb.tr = Me._condb.con.BeginTransaction()
Try
sb.Remove(0, sb.Length)
sb.Append(" Delete From UserMenu ")
sb.Append(" Where UserId = '" & Me.txtUserId.Text & "' ")
Me._condb.com = New SqlCommand
Me._condb.com.CommandType = CommandType.Text
Me._condb.com.CommandText = sb.ToString()
Me._condb.com.Connection = Me._condb.con
Me._condb.com.Transaction = Me._condb.tr
Me._condb.com.ExecuteNonQuery()
Me._condb.tr.Commit()
Me.txtMenuName.Text = ""
Me.txtFormName.Text = ""
MsgBox("ลบข้อมูลสำเร็จ", MsgBoxStyle.Information, "ผลการทำงาน")
Catch ex As Exception
Me._condb.tr.Rollback()
MsgBox("ไม่สามารถ บันทึก ข้อมูลได้ เนื่อง จาก " & ex.Message.ToString(), MsgBoxStyle.Critical, "Error")
End Try
Else
MsgBox("ไม่สามารถ ติดต่อ ฐาน ข้อมูลได้", MsgBoxStyle.Critical, "Error")
End If
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Me._frmSearch = New FrmSearch
If Me._condb.connectDB Then
sb.Remove(0, sb.Length)
sb.Append(" Select * from UserMenu ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsUserMenu As New DataSet
Me._condb.da.Fill(dsUserMenu, "dtUserMenu")
If Not dsUserMenu Is Nothing Then
Me._frmSearch.dtSearch = dsUserMenu.Tables(0)
AddHandler Me._frmSearch.txtSearch.TextChanged, AddressOf txtSearch_TextChanged
AddHandler Me._frmSearch.dgvData.CellMouseDoubleClick, AddressOf dgvData_CellMouseDoubleClick
Me._frmSearch.ShowDialog()
End If
End If
End Sub
Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
If Me._frmSearch.txtSearch.Text <> "" Then
Me._frmSearch.dvSearch = Me._frmSearch.dtSearch.DefaultView
Me._frmSearch.dvSearch.RowFilter = " UserId like '%" & Me._frmSearch.txtSearch.Text & "%' or Convert(MenuId,System.String) like '%" & Me._frmSearch.txtSearch.Text & "%' "
Else
Me._frmSearch.dvSearch.RowFilter = ""
Me._frmSearch.dgvData.DataSource = Me._frmSearch.dtSearch
End If
End Sub
Sub dgvData_CellMouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs)
If (e.RowIndex <> -1) Then
If Me._condb.connectDB Then
sb.Remove(0, sb.Length)
sb.Append(" Select * from [User] ")
sb.Append(" where UserId = '" & Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("UserId").Value.ToString() & "' ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsUserMenu As New DataSet
Me._condb.da.Fill(dsUserMenu, "dtUserMenu")
If Not dsUserMenu Is Nothing Then
Me.txtUserId.Text = dsUserMenu.Tables(0).Rows(0).Item("UserId").ToString()
Me.txtUserName.Text = dsUserMenu.Tables(0).Rows(0).Item("UserName").ToString()
Else
Me.txtUserId.Text = ""
Me.txtUserName.Text = ""
End If
End If
If Me._condb.connectDB Then
sb.Remove(0, sb.Length)
sb.Append(" Select * from Menu ")
sb.Append(" where MenuId = " & Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("MenuId").Value.ToString() & " ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsMenu As New DataSet
Me._condb.da.Fill(dsMenu, "dtMenu")
If Not dsMenu Is Nothing Then
Me.txtMenuId.Text = dsMenu.Tables(0).Rows(0).Item("MenuId").ToString()
Me.txtMenuName.Text = dsMenu.Tables(0).Rows(0).Item("MenuName").ToString()
Me.txtFormName.Text = dsMenu.Tables(0).Rows(0).Item("Formname").ToString()
Else
Me.txtMenuId.Text = ""
Me.txtMenuName.Text = ""
Me.txtFormName.Text = ""
End If
End If
Me._frmSearch.Close()
End If
End Sub
Private Sub btnSearchUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchUser.Click
Me._frmSearch = New FrmSearch
If Me._condb.connectDB Then
sb.Remove(0, sb.Length)
sb.Append(" Select * from [User] ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsUser As New DataSet
Me._condb.da.Fill(dsUser, "dtUser")
If Not dsUser Is Nothing Then
Me._frmSearch.dtSearch = dsUser.Tables(0)
AddHandler Me._frmSearch.txtSearch.TextChanged, AddressOf txtSearchUser_TextChanged
AddHandler Me._frmSearch.dgvData.CellMouseDoubleClick, AddressOf dgvDataUser_CellMouseDoubleClick
Me._frmSearch.ShowDialog()
End If
End If
End Sub
Sub txtSearchUser_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
If Me._frmSearch.txtSearch.Text <> "" Then
Me._frmSearch.dvSearch = Me._frmSearch.dtSearch.DefaultView
Me._frmSearch.dvSearch.RowFilter = " UserId like '%" & Me._frmSearch.txtSearch.Text & "%' or Username like '%" & Me._frmSearch.txtSearch.Text & "%' "
Else
Me._frmSearch.dvSearch.RowFilter = ""
Me._frmSearch.dgvData.DataSource = Me._frmSearch.dtSearch
End If
End Sub
Sub dgvDataUser_CellMouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs)
If (e.RowIndex <> -1) Then
Me.txtUserId.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("UserId").Value.ToString()
Me.txtUserName.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("UserName").Value.ToString()
Me._frmSearch.Close()
End If
End Sub
Private Sub btnSearchMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchMenu.Click
Me._frmSearch = New FrmSearch
If Me._condb.connectDB Then
sb.Remove(0, sb.Length)
sb.Append(" Select * from Menu ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsMenu As New DataSet
Me._condb.da.Fill(dsMenu, "dtMenu")
If Not dsMenu Is Nothing Then
Me._frmSearch.dtSearch = dsMenu.Tables(0)
AddHandler Me._frmSearch.txtSearch.TextChanged, AddressOf txtSearchMenu_TextChanged
AddHandler Me._frmSearch.dgvData.CellMouseDoubleClick, AddressOf dgvDataMenu_CellMouseDoubleClick
Me._frmSearch.ShowDialog()
End If
End If
End Sub
Sub txtSearchMenu_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
If Me._frmSearch.txtSearch.Text <> "" Then
Me._frmSearch.dvSearch = Me._frmSearch.dtSearch.DefaultView
Me._frmSearch.dvSearch.RowFilter = " MenuName like '%" & Me._frmSearch.txtSearch.Text & "%' or Formname like '%" & Me._frmSearch.txtSearch.Text & "%' "
Else
Me._frmSearch.dvSearch.RowFilter = ""
Me._frmSearch.dgvData.DataSource = Me._frmSearch.dtSearch
End If
End Sub
Sub dgvDataMenu_CellMouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs)
If (e.RowIndex <> -1) Then
Me.txtMenuId.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("MenuId").Value.ToString()
Me.txtMenuName.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("MenuName").Value.ToString()
Me.txtFormName.Text = Me._frmSearch.dgvData.Rows(e.RowIndex).Cells("Formname").Value.ToString()
Me._frmSearch.Close()
End If
End Sub
Private Function GetUserMenuByUserId(ByVal userid As String, ByVal menuid As String) As Boolean
If userid <> "" And menuid <> "" Then
If Me._condb.connectDB() Then
sb.Remove(0, sb.Length)
sb.Append(" select * from UserMenu ")
sb.Append(" where userid = '" & userid & "' ")
sb.Append(" and menuid = " & CInt(menuid) & " ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsUserMenu As New DataSet
Me._condb.da.Fill(dsUserMenu, "dtUserMenu")
If Not dsUserMenu Is Nothing Then
If dsUserMenu.Tables(0).Rows.Count <> 0 Then
Return False
Else
Return True
End If
Else
Return True
End If
End If
End If
Return True
End Function
End Class
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Public Class GenMenuClass
Dim _condb As New ConnectionDBClass
Dim sb As New StringBuilder
Public Function getMenuIdByUserId(ByVal userid As String) As DataTable
Dim ds As New DataSet
If Me._condb.connectDB() Then
sb.Remove(0, sb.Length)
sb.Append(" select * from vw_usermenu ")
sb.Append(" where userid = '" & userid & "' ")
sb.Append(" and parentid =0 ")
sb.Append(" order by menuid,rowform ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
If Not ds.Tables("dtUserMenu") Is Nothing Then
ds.Tables("dtUserMenu").Clear()
End If
Me._condb.da.Fill(ds, "dtUserMenu")
If ds.Tables("dtUserMenu") Is Nothing Then
Return Nothing
End If
End If
Return ds.Tables("dtUserMenu")
End Function
Public Function GetMenuIDByUserIdAndParentId(ByVal userid As String, ByVal parentMenu As Int32) As DataTable
Dim ds As New DataSet
If Me._condb.connectDB() Then
sb.Remove(0, sb.Length)
sb.Append(" select * from vw_usermenu ")
sb.Append(" where userid = '" & userid & "' ")
sb.Append(" and parentid = " & parentMenu & " ")
sb.Append(" order by menuid,rowform ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
If Not ds.Tables("dtMenu") Is Nothing Then
ds.Tables("dtMenu").Clear()
End If
Me._condb.da.Fill(ds, "dtMenu")
If ds.Tables("dtMenu") Is Nothing Then
Return Nothing
End If
End If
Return ds.Tables("dtMenu")
End Function
Public Function GetFromNameByUserIdAndMenename(ByVal userid As String, ByVal menuname As String) As DataTable
Dim ds As New DataSet
If Me._condb.connectDB() Then
sb.Remove(0, sb.Length)
sb.Append(" select * from Vw_UserMenu ")
sb.Append(" where UserId = '" & userid & "' ")
sb.Append(" and MenuName = '" & menuname & "' ")
sb.Append(" order by RowForm ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
If Not ds.Tables("dtMenu") Is Nothing Then
ds.Tables("dtMenu").Clear()
End If
Me._condb.da.Fill(ds, "dtMenu")
If ds.Tables("dtMenu") Is Nothing Then
Return Nothing
End If
End If
Return ds.Tables("dtMenu")
End Function
End Class
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Public Class FrmLogin
Dim _mainFrm As New MainForm
Dim _condb As New ConnectionDBClass
Dim sb As New StringBuilder
Private Sub FrmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.txtUserId.Text = ""
Me.txtUserId.Focus()
End Sub
Private Sub btnLogOn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogOn.Click
If Me.txtUserId.Text = "" Then
MsgBox("ตรวจสอบ UserId!! ", MsgBoxStyle.Critical, "Error")
Me.txtUserId.Focus()
Exit Sub
End If
If Me._condb.connectDB() Then
sb.Remove(0, sb.Length)
sb.Append(" select * from [User] ")
sb.Append(" where userid = '" & Me.txtUserId.Text.Trim() & "' ")
Me._condb.da = New SqlDataAdapter(sb.ToString(), Me._condb.con)
Dim dsUser As New DataSet
Me._condb.da.Fill(dsUser, "dtUser")
If Not dsUser Is Nothing Then
Me._mainFrm.UserId = dsUser.Tables(0).Rows(0).Item("UserId").ToString()
Me._mainFrm.Show()
Me._mainFrm.Activate()
Me.Hide()
Else
Me._mainFrm.UserId = ""
End If
End If
End Sub
End Class
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Public Class MainForm
Dim formCurrentRunning As New FormCollection()
Dim _condb As New ConnectionDBClass
Dim sb As New StringBuilder
Dim menuroot As ToolStripMenuItem
Dim menusub As ToolStripMenuItem
Private _UserId As String
Public Property UserId() As String
Get
Return _UserId
End Get
Set(ByVal value As String)
_UserId = value
End Set
End Property
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim _GenMenu As New GenMenuClass
Dim dtRootMenu As New DataTable
dtRootMenu = _GenMenu.getMenuIdByUserId(Me.UserId)
For index As Integer = 0 To dtRootMenu.Rows.Count - 1
menuroot = New ToolStripMenuItem(dtRootMenu.Rows(index).Item("MenuName").ToString())
Dim dtSubMenu As DataTable = _GenMenu.GetMenuIDByUserIdAndParentId(Me.UserId, CInt(dtRootMenu.Rows(index).Item("MenuId").ToString()))
For index1 As Integer = 0 To dtSubMenu.Rows.Count - 1
menusub = New ToolStripMenuItem(dtSubMenu.Rows(index1).Item("MenuName").ToString(), Nothing)
menuroot.DropDownItems.Add(menusub)
Next
Me.MenuStrip.Items.Add(menuroot)
Next
End Sub
End Class
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim _GenMenu As New GenMenuClass
Dim dtRootMenu As New DataTable
dtRootMenu = _GenMenu.getMenuIdByUserId(Me.UserId)
For index As Integer = 0 To dtRootMenu.Rows.Count - 1
menuroot = New ToolStripMenuItem(dtRootMenu.Rows(index).Item("MenuName").ToString())
Dim dtSubMenu As DataTable = _GenMenu.GetMenuIDByUserIdAndParentId(Me.UserId, CInt(dtRootMenu.Rows(index).Item("MenuId").ToString()))
For index1 As Integer = 0 To dtSubMenu.Rows.Count - 1
menusub = New ToolStripMenuItem(dtSubMenu.Rows(index1).Item("MenuName").ToString(), Nothing)
'********* เพิ่มโค้ด ตรงนี้ครับ **********************
AddHandler menusub.Click, AddressOf MenuSub_Click 'สร้าง event handles ให้ sub menu
'******************************************************
menuroot.DropDownItems.Add(menusub)
Next
Me.MenuStrip.Items.Add(menuroot)
Next
End Sub
เมื่อ เกิดเหตุการณ์ คลิกที่ Sub Menu
Code (VB.NET)
Sub MenuSub_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim strMenuName As String = sender.ToString.Trim()
Dim _getForm As New GenMenuClass
Dim dtFormName As New DataTable
'เรียกใช้งาน ฟังก์ชัน GetFromNameByUserIdAndMenename จากคลาส GenMenuClass
'ส่งค่าตัวแปร _UserId กับ strMenuName เพื่อค้นหา ชื่อ ฟอร์ม
dtFormName = _getForm.GetFromNameByUserIdAndMenename(_UserId, strMenuName)
If dtFormName.Rows.Count <> 0 Then
Dim openForm As New Form
Dim strFormName As String = dtFormName.Rows(0)("FormName").ToString().Trim()
'class [Assembly] ให้ imports system.Reflection ด้วย
strFormName = [Assembly].GetEntryAssembly.GetName.Name & "." & strFormName
openForm = DirectCast([Assembly].GetEntryAssembly.CreateInstance(strFormName), Form)
openForm.MdiParent = Me
openForm.WindowState = FormWindowState.Maximized
openForm.Show()
End If
End Sub
Load balance : Server 05
