001.
Option
Explicit
On
002.
Option
Strict
On
003.
Imports
System.Data
004.
Imports
System.Data.OleDb
005.
Imports
System.Text
006.
007.
Public
Class
frmDevice
008.
Dim
Conn
As
OleDbConnection
009.
Dim
com
As
OleDbCommand
010.
Dim
da
As
OleDbDataAdapter
011.
Dim
dr
As
OleDbDataReader
012.
Dim
Active
As
Boolean
013.
Dim
tr
As
OleDbTransaction
014.
015.
Private
Sub
ClearAllData()
016.
Dim
myCtl
As
Control
017.
For
Each
myCtl
In
grbDataDevice.Controls
018.
If
myCtl.
GetType
.Name
Is
GetType
(System.Windows.Forms.TextBox).Name
Then
myCtl.Text =
""
019.
If
myCtl.
GetType
.Name
Is
GetType
(System.Windows.Forms.ComboBox).Name
Then
myCtl.Text =
""
020.
Next
021.
txtDeviceNo.Text =
"####"
022.
End
Sub
023.
Private
Sub
LockTrue()
024.
Dim
myCtl
As
Control
025.
For
Each
myCtl
In
grbDataDevice.Controls
026.
If
myCtl.
GetType
.Name
Is
GetType
(System.Windows.Forms.TextBox).Name
Then
027.
If
myCtl.Name <>
"txtDeviceID"
Then
028.
myCtl.Enabled =
True
029.
End
If
030.
End
If
031.
If
myCtl.
GetType
.Name
Is
GetType
(System.Windows.Forms.ComboBox).Name
Then
myCtl.Enabled =
True
032.
033.
Next
034.
End
Sub
035.
Private
Sub
LockFalse()
036.
Dim
myCtl
As
Control
037.
For
Each
myCtl
In
grbDataDevice.Controls
038.
If
myCtl.
GetType
.Name
Is
GetType
(System.Windows.Forms.TextBox).Name
Then
myCtl.Enabled =
False
039.
If
myCtl.
GetType
.Name
Is
GetType
(System.Windows.Forms.ComboBox).Name
Then
myCtl.Enabled =
False
040.
041.
Next
042.
End
Sub
043.
044.
Private
Sub
frmDevice_Load(
ByVal
sender
As
Object
,
ByVal
e
As
System.EventArgs)
Handles
Me
.Load
045.
046.
Dim
strConn
As
String
047.
strConn = DBConnString.strConn
048.
Conn =
New
OleDbConnection()
049.
With
conn
050.
If
.State = ConnectionState.Open
Then
.Close()
051.
.ConnectionString = strConn
052.
.Open()
053.
End
With
054.
Call
LoadComboLink()
055.
056.
With
cboSearch
057.
.Items.Add(
"ปี"
)
058.
.Items.Add(
"ข้อมูลอุปกรณ์"
)
059.
.SelectedIndex = 0
060.
End
With
061.
Call
ShowData(
False
)
062.
Call
FormatdgvDataList()
063.
064.
cmdAdd.Enabled =
True
065.
cmdEdit.Enabled =
False
066.
cmdSave.Enabled =
True
067.
cmdDelete.Enabled =
False
068.
cmdExits.Enabled =
True
069.
Call
LockTrue()
070.
Active =
False
071.
Call
ClearAllData()
072.
txtDataDevice.Focus()
073.
End
Sub
074.
Private
Sub
LoadComboLink()
075.
da =
New
OleDbDataAdapter
076.
com =
New
OleDbCommand
077.
End
Sub
078.
Private
Sub
ShowData(
ByVal
bShow
As
Boolean
)
079.
080.
Dim
tSQL
As
String
081.
Dim
tFed
As
String
=
""
082.
Dim
tWhe
As
String
=
""
083.
Dim
dr
As
OleDbDataReader
084.
tSQL =
"SELECT * FROM DEVICE "
085.
If
bShow
And
txtSearch.Text.Trim() <>
""
Then
086.
Select
Case
cboSearch.SelectedIndex
087.
Case
0
088.
tSQL = tSQL &
" WHERE (DEVICE_YEAR LIKE @DEVICE_YEAR)"
089.
tWhe = txtSearch.Text.Trim()
090.
tFed =
"DEVIVE_YEAR"
091.
Case
1
092.
tSQL = tSQL &
" WHERE (DEVICE_NAME LIKE @DEVICE_NAME)"
093.
tWhe = txtSearch.Text.Trim()
094.
tFed =
"DEVICE_NAME"
095.
End
Select
096.
End
If
097.
tSQL = tSQL &
" ORDER BY ID_DEVICE"
098.
Dim
dt
As
DataTable
099.
com =
New
OleDbCommand()
100.
With
com
101.
.CommandText = tSQL
102.
.CommandType = CommandType.Text
103.
.Connection = conn
104.
.Parameters.Clear()
105.
If
bShow
Then
106.
If
tWhe <>
""
Then
107.
.Parameters.AddWithValue(tFed, OleDbType.VarChar).Value =
"%"
& tWhe &
"%"
108.
End
If
109.
End
If
110.
dr = .ExecuteReader
111.
If
dr.HasRows
Then
112.
dt =
New
DataTable()
113.
dt.Load(dr)
114.
dgvDataList.DataSource = dt
115.
Else
116.
dgvDataList.DataSource =
Nothing
117.
End
If
118.
End
With
119.
dr.Close()
120.
End
Sub
121.
122.
Private
Sub
AddData()
123.
124.
125.
If
MessageBox.Show(
"คุณต้องการเพิ่ม"
&
Me
.Text &
"ใหม่ ใช่หรือไม่?"
,
"คำยืนยัน"
, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes
Then
126.
tr = Conn.BeginTransaction()
127.
Try
128.
da =
New
OleDbDataAdapter
129.
da.InsertCommand.CommandText = (
"INSERT INTO DEVICE(DEVICE_NO,DEVICE_NAME,PRICE_PER_MONTH,DEVICE_YEAR)"
)
130.
da.InsertCommand.CommandText = (
" VALUES (@DEVICE_NO,@DEVICE_NAME,@PRICE_PER_MONTH,@DEVICE_YEAR)"
)
131.
Dim
sqlAdd
As
String
132.
sqlAdd = da.ToString()
133.
With
com
134.
.CommandText = sqlAdd
135.
.CommandType = CommandType.Text
136.
.Connection = Conn
137.
.Transaction = tr
138.
.Parameters.Clear()
139.
.Parameters.AddWithValue(
"@DEVICE_NO"
, OleDbType.Numeric).Value = txtDeviceNo.Text.Trim()
140.
.Parameters.AddWithValue(
"@DEVICE_NAME"
, OleDbType.VarChar).Value = txtDataDevice.Text.Trim()
141.
.Parameters.AddWithValue(
"@PRICE_PER_MONTH"
, OleDbType.Numeric).Value = txtPrice.Text.Trim()
142.
.Parameters.AddWithValue(
"@DEVICE_YEAR"
, OleDbType.Numeric).Value = cboYear.Text.Trim()
143.
Dim
result
As
Integer
= com.ExecuteNonQuery()
144.
145.
146.
End
With
147.
tr.Commit()
148.
MessageBox.Show(
"เพิ่ม"
&
Me
.Text &
"ใหม่ เรียบร้อยแล้ว !!!"
,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Information)
149.
Call
ShowData(
False
)
150.
Call
FormatdgvDataList()
151.
cmdAdd.Enabled =
False
152.
cmdEdit.Enabled =
False
153.
cmdSave.Enabled =
True
154.
cmdDelete.Enabled =
False
155.
cmdExits.Enabled =
True
156.
cmdSearch.Enabled =
False
157.
Call
LockTrue()
158.
Active =
False
159.
Call
ClearAllData()
160.
Catch
ex
As
Exception
161.
MessageBox.Show(
"เกิดความผิดพลาด !!!"
& ex.Message,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
162.
tr.Rollback()
163.
End
Try
164.
165.
End
If
166.
167.
End
Sub
168.
Private
Sub
EditData()
169.
If
MessageBox.Show(
"คุณต้องการบันทึก"
&
Me
.Text &
" ใช่หรือไม่?"
,
"คำยืนยัน"
, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes
Then
170.
tr = Conn.BeginTransaction()
171.
Try
172.
173.
da =
New
OleDbDataAdapter
174.
175.
da.SelectCommand =
New
OleDbCommand(
"UPDATE DEVICE"
)
176.
da.SelectCommand =
New
OleDbCommand(
" SET DEVICE_NO=@DEVICE_NO"
)
177.
da.SelectCommand =
New
OleDbCommand(
" ,DEVICE_NAME=@DEVICE_NAME"
)
178.
da.SelectCommand =
New
OleDbCommand(
" ,PRICE_PER_MONTH=@PRICE_PER_MONTH"
)
179.
da.SelectCommand =
New
OleDbCommand(
" ,DEVICE_YEAR=@DEVICE_YEAR"
)
180.
da.SelectCommand =
New
OleDbCommand(
" WHERE (DEVICE_ID=@DEVICE_ID)"
)
181.
Dim
sqlEdit
As
String
182.
sqlEdit = da.ToString()
183.
184.
With
com
185.
.CommandText = sqlEdit
186.
.CommandType = CommandType.Text
187.
.Connection = Conn
188.
.Transaction = tr
189.
.Parameters.Clear()
190.
.Parameters.AddWithValue(
"@DEVICE_NO"
, OleDbType.Numeric).Value = txtDeviceNo.Text.Trim()
191.
.Parameters.AddWithValue(
"@DEVICE_NAME"
, OleDbType.VarChar).Value = txtDataDevice.Text.Trim()
192.
.Parameters.AddWithValue(
"@PRICE_PER_MONTH"
, OleDbType.VarChar).Value = txtPrice.Text.Trim()
193.
.Parameters.AddWithValue(
"@DEVICE_YEAR"
, OleDbType.VarChar).Value = cboYear.Text.Trim()
194.
195.
Dim
result
As
Integer
= com.ExecuteNonQuery()
196.
197.
If
result = 0
Then
198.
tr.Rollback()
199.
MessageBox.Show(
"เกิดข้อผิดพลาด !!!"
,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Information)
200.
txtDataDevice.SelectAll()
201.
Else
202.
tr.Commit()
203.
MessageBox.Show(
"แก้ไข"
&
Me
.Text &
" เรียบร้อยแล้ว !!!"
,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Information)
204.
205.
Call
ClearAllData()
206.
Call
ShowData(
False
)
207.
Call
FormatdgvDataList()
208.
Call
LockFalse()
209.
cmdAdd.Enabled =
True
210.
cmdEdit.Enabled =
True
211.
cmdSave.Enabled =
False
212.
cmdDelete.Enabled =
False
213.
cmdExits.Enabled =
True
214.
cmdSearch.Enabled =
True
215.
End
If
216.
End
With
217.
Catch
ex
As
Exception
218.
tr.Rollback()
219.
220.
MessageBox.Show(
"เกิดข้อผิดพลาด เนื่องจาก "
& ex.Message,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Information)
221.
End
Try
222.
End
If
223.
txtDataDevice.Focus()
224.
End
Sub
225.
Private
Sub
FormatdgvDataList()
226.
With
dgvDataList
227.
If
.RowCount > 0
Then
228.
.Columns(0).HeaderText =
"ลำดับที่"
229.
.Columns(1).HeaderText =
"รหัสอุปกรณ์"
230.
.Columns(2).HeaderText =
"ข้อมูลอุปกรณ์"
231.
.Columns(3).HeaderText =
"ราคาค่าเช่า/เดือน"
232.
.Columns(4).HeaderText =
"ปี"
233.
234.
.Columns(0).Width = 80
235.
.Columns(1).Width = 100
236.
.Columns(2).Width = 250
237.
.Columns(3).Width = 150
238.
.Columns(4).Width = 80
239.
240.
End
If
241.
End
With
242.
End
Sub
243.
244.
Private
Sub
dgvDataList_CellMouseUp(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.Windows.Forms.DataGridViewCellMouseEventArgs)
Handles
dgvDataList.CellMouseUp
245.
If
e.RowIndex = -1
Then
Exit
Sub
246.
Dim
tstatus
As
String
=
""
247.
With
dgvDataList
248.
249.
txtPrice.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString()
250.
txtDeviceNo.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString()
251.
txtDataDevice.Text = .Rows.Item(e.RowIndex).Cells(2).Value.ToString()
252.
txtPrice.Text = .Rows.Item(e.RowIndex).Cells(3).Value.ToString()
253.
cboYear.Text = .Rows.Item(e.RowIndex).Cells(4).Value.ToString()
254.
Call
LockFalse()
255.
256.
Active =
True
257.
cmdAdd.Enabled =
True
258.
cmdEdit.Enabled =
True
259.
cmdSave.Enabled =
False
260.
cmdDelete.Enabled =
True
261.
cmdExits.Enabled =
True
262.
cmdSearch.Enabled =
True
263.
txtDataDevice.Focus()
264.
txtDataDevice.SelectAll()
265.
End
With
266.
End
Sub
267.
268.
Private
Sub
cmdSave_Click(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.EventArgs)
Handles
cmdSave.Click
269.
270.
271.
If
txtDataDevice.Text.Trim =
""
Or
txtPrice.Text.Trim =
""
Then
272.
MessageBox.Show(
"กรุณากรอกข้อมูลที่มี * ให้ครบก่อน !!!"
,
"ผลการตรวจสอบ"
, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
273.
Exit
Sub
274.
End
If
275.
276.
If
Active =
False
Then
277.
Call
AddData()
278.
Else
279.
Call
EditData()
280.
End
If
281.
End
Sub
282.
Private
Sub
cmdSearch_Click(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.EventArgs)
Handles
cmdSearch.Click
283.
Call
ShowData(
True
)
284.
cmdAdd.Enabled =
True
285.
cmdEdit.Enabled =
True
286.
cmdSave.Enabled =
False
287.
cmdDelete.Enabled =
True
288.
cmdExits.Enabled =
True
289.
cmdSearch.Enabled =
True
290.
Call
LockFalse()
291.
End
Sub
292.
Private
Sub
cmdEdit_Click(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.EventArgs)
Handles
cmdEdit.Click
293.
cmdAdd.Enabled =
False
294.
cmdEdit.Enabled =
False
295.
cmdSave.Enabled =
True
296.
cmdDelete.Enabled =
False
297.
cmdExits.Enabled =
True
298.
cmdSearch.Enabled =
False
299.
Call
LockTrue()
300.
Active =
True
301.
End
Sub
302.
Private
Sub
cmdAdd_Click(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.EventArgs)
Handles
cmdAdd.Click
303.
cmdAdd.Enabled =
False
304.
cmdEdit.Enabled =
False
305.
cmdSave.Enabled =
True
306.
cmdDelete.Enabled =
False
307.
cmdExits.Enabled =
True
308.
cmdSearch.Enabled =
True
309.
Call
LockTrue()
310.
Active =
False
311.
Call
ClearAllData()
312.
txtDataDevice.Focus()
313.
End
Sub
314.
Private
Sub
cmdDelete_Click(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.EventArgs)
Handles
cmdDelete.Click
315.
If
MessageBox.Show(
"คุณต้องการลบ"
&
Me
.Text &
" ใช่หรือไม่?"
,
"คำยืนยัน"
, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes
Then
316.
317.
tr = Conn.BeginTransaction()
318.
Try
319.
da =
New
OleDbDataAdapter()
320.
da.InsertCommand =
New
OleDbCommand(
"DELETE FROM DEVICE"
)
321.
da.InsertCommand =
New
OleDbCommand(
" WHERE (DEVICE_NO=@DEVICE_NO)"
)
322.
Dim
sqlEdit
As
String
323.
sqlEdit = da.ToString()
324.
With
com
325.
.CommandText = sqlEdit
326.
.CommandType = CommandType.Text
327.
.Connection = Conn
328.
.Transaction = tr
329.
.Parameters.Clear()
330.
.Parameters.AddWithValue(
"@DEVICE_NO"
, OleDbType.Numeric).Value = txtDeviceNo.Text.Trim()
331.
Dim
result
As
Integer
= com.ExecuteNonQuery()
332.
333.
If
result = 0
Then
334.
tr.Rollback()
335.
MessageBox.Show(
"รหัสที่คุณป้อน ไม่ถูกต้อง !!!"
,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Information)
336.
337.
txtDataDevice.SelectAll()
338.
Else
339.
tr.Commit()
340.
MessageBox.Show(
"ลบ"
&
Me
.Text &
" เรียบร้อยแล้ว !!!"
,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Information)
341.
342.
Call
ClearAllData()
343.
Call
ShowData(
False
)
344.
Call
FormatdgvDataList()
345.
cmdAdd.Enabled =
True
346.
cmdEdit.Enabled =
True
347.
cmdSave.Enabled =
False
348.
cmdDelete.Enabled =
False
349.
cmdExits.Enabled =
True
350.
cmdSearch.Enabled =
True
351.
Call
LockFalse()
352.
End
If
353.
End
With
354.
Catch
ex
As
Exception
355.
tr.Rollback()
356.
357.
MessageBox.Show(
"เกิดข้อผิดพลาด เนื่องจาก "
& ex.Message,
"ผลการทำงาน"
, MessageBoxButtons.OK, MessageBoxIcon.Information)
358.
End
Try
359.
End
If
360.
txtDataDevice.Focus()
361.
End
Sub
362.
363.
Private
Sub
cmdExits_Click(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.EventArgs)
Handles
cmdExits.Click
364.
If
MessageBox.Show(
"คุณต้องการจบการทำงาน ใช่หรือไม่?"
,
"คำยืนยัน"
, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes
Then
365.
Me
.Close()
366.
frmMain.Show()
367.
End
If
368.
End
Sub
369.
370.
Private
Sub
txtPrice_keyPress(
ByVal
sender
As
Object
,
ByVal
e
As
System.Windows.Forms.KeyPressEventArgs)
Handles
txtPrice.KeyPress
371.
Select
Case
Asc(e.KeyChar)
372.
Case
47
To
58
373.
e.Handled =
False
374.
Case
8, 13, 46
375.
e.Handled =
False
376.
Case
Else
377.
e.Handled =
True
378.
End
Select
379.
End
Sub
380.
End
Class