Imports System.Data
Imports System.Data.OleDb
Public Class Main
Inherits System.Windows.Forms.Form
Dim mycon As OleDbConnection
Dim myAdapter As OleDbDataAdapter
Dim dataset As New DataSet
Dim mycmd As OleDbCommand
Dim mycmd1 As OleDbCommand
Dim mycmd2 As OleDbCommand
Dim mycmd3 As OleDbCommand
Dim myreader As OleDbDataReader
Private Sub Main_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'BookRentsDataSet.Book' table. You can move, or remove it, as needed.
Me.BookTableAdapter.Fill(Me.BookRentsDataSet.Book)
'TODO: This line of code loads data into the 'BookRentsDataSet.Member' table. You can move, or remove it, as needed.
Me.MemberTableAdapter.Fill(Me.BookRentsDataSet.Member)
mycon = New OleDbConnection("PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source=C:\Users\Miki\Documents\Visual Studio 2010\Projects\BookRents\BookRents\BookRents.mdb")
End Sub
Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd_1.Click
If txtMem_Name_1.Text = "" And txtMem_Last_1.Text = "" And txtMem_Tel_1.Text = "" Then
MessageBox.Show("กรุณาใส่ข้อมูลให้ครบ", "ผิดพลาด")
Exit Sub
End If
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Member;", mycon)
If mycmd1.ExecuteScalar = 0 Then
mycon.Close()
mycmd = New OleDbCommand("Insert INTO Member(Mem_ID,Mem_Name,Mem_Last_Name,Mem_Tel,Mem_Date,Mem_Date_Ex,Mem_Status) values(11001,txtMem_Name_1.Text,txtMem_Last_1.Text,txtMem_Tel_1.Text,DateTimePicker1.Text,DateTimePicker3.Text,'ยังไม่ได้เช่า')", mycon)
mycmd.Parameters.AddWithValue("@Mem_Name", txtMem_Name_1.Text)
mycmd.Parameters.AddWithValue("@Mem_Last_Name", txtMem_Last_1.Text)
mycmd.Parameters.AddWithValue("@Mem_Tel", txtMem_Tel_1.Text)
mycmd.Parameters.AddWithValue("@Mem_Date", DateTimePicker1.Text)
mycmd.Parameters.AddWithValue("@Mem_Date_Ex", DateTimePicker3.Text)
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
ElseIf mycmd1.ExecuteScalar > 0 Then
mycon.Close()
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Member;", mycon)
mycmd2 = New OleDbCommand("SELECT MAX(Mem_ID) FROM Member;", mycon)
mycmd = New OleDbCommand("Insert INTO Member(Mem_ID,Mem_Name,Mem_Last_Name,Mem_Tel,Mem_Date,Mem_Date_Ex,Mem_Status) values(" & mycmd2.ExecuteScalar + 1 & ",txtMem_Name_1.Text,txtMem_Last_1.Text,txtMem_Tel_1.Text,DateTimePicker1.Text,DateTimePicker3.Text,'ยังไม่ได้เช่า')", mycon)
mycmd.Parameters.AddWithValue("@Mem_Name", txtMem_Name_1.Text)
mycmd.Parameters.AddWithValue("@Mem_Last_Name", txtMem_Last_1.Text)
mycmd.Parameters.AddWithValue("@Mem_Tel", txtMem_Tel_1.Text)
mycmd.Parameters.AddWithValue("@Mem_Date", DateTimePicker1.Text)
mycmd.Parameters.AddWithValue("@Mem_Date_Ex", DateTimePicker3.Text)
mycon.Close()
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
End If
txtMem_Name_1.Text = ""
txtMem_Last_1.Text = ""
txtMem_Tel_1.Text = ""
End Sub
Private Sub Btn_Serch_1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Serch_1.Click
mycon.Open()
mycmd = New OleDbCommand("SELECT * FROM Member WHERE Mem_ID = " & txtSearch_1.Text & ";", mycon)
myreader = mycmd.ExecuteReader()
While myreader.Read()
txtMem_ID_2.Text = CStr(myreader.Item("Mem_ID"))
txtMem_Name_2.Text = CStr(myreader.Item("Mem_Name"))
txtMem_Last_2.Text = CStr(myreader.Item("Mem_Last_Name"))
txtMem_Tel_2.Text = CStr(myreader.Item("Mem_Tel"))
txtMem_Date_2.Text = CStr(myreader.Item("Mem_Date_Ex"))
End While
mycon.Close()
End Sub
Private Sub BtnEdit_1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit_1.Click
mycmd = New OleDbCommand("UPDATE Member set Mem_Name=@Mem_Name,Mem_Last_Name=@Mem_Last_Name,Mem_Tel=@Mem_Tel,Mem_Date_Ex=@Mem_Date_Ex where Mem_ID = " & txtMem_ID_2.Text & ";", mycon)
mycmd.Parameters.AddWithValue("@Mem_Name", txtMem_Name_2.Text)
mycmd.Parameters.AddWithValue("@Mem_Last_Name", txtMem_Last_2.Text)
mycmd.Parameters.AddWithValue("@Mem_Tel", txtMem_Tel_2.Text)
mycmd.Parameters.AddWithValue("@Mem_Date_Ex", txtMem_Date_2.Text)
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
txtMem_ID_2.Text = ""
txtMem_Name_2.Text = ""
txtMem_Last_2.Text = ""
txtMem_Tel_2.Text = ""
txtMem_Date_2.Text = ""
End Sub
Private Sub BtnDel_1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDel_1.Click
If MsgBox("คุณแน่ใจหรือว่าต้องการลบข้อมูลนี้?", MsgBoxStyle.Question + MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
mycmd = New OleDbCommand("delete from Member where Mem_ID = " & txtSearch_1.Text & " ", mycon)
mycmd.Parameters.AddWithValue("@Mem_ID", txtSearch_1.Text)
mycmd = New OleDbCommand("delete from Member where Mem_ID = " & txtSearch_1.Text & " ", mycon)
mycmd.Parameters.AddWithValue("@Mem_ID", txtSearch_1.Text)
End If
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
txtMem_ID_2.Text = ""
txtMem_Name_2.Text = ""
txtMem_Last_2.Text = ""
txtMem_Tel_2.Text = ""
txtMem_Date_2.Text = ""
End Sub
Private Sub BtnClear_2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear_2.Click
txtMem_ID_2.Text = ""
txtMem_Name_2.Text = ""
txtMem_Last_2.Text = ""
txtMem_Tel_2.Text = ""
txtMem_Date_2.Text = ""
End Sub
Private Sub BtnClear_1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear_1.Click
txtMem_Name_1.Text = ""
txtMem_Last_1.Text = ""
txtMem_Tel_1.Text = ""
End Sub
Private Sub BtnAdd_3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd_3.Click
If txtBook_N.Text = "" And txtBook_T.Text = "" And txtBook_R.Text = "" And txtBook_P.Text = "" Then
MessageBox.Show("กรุณาใส่ข้อมูลให้ครบ", "ผิดพลาด")
Exit Sub
End If
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Book;", mycon)
If mycmd1.ExecuteScalar = 0 Then
mycon.Close()
mycmd = New OleDbCommand("Insert INTO Book(Book_ID,Book_Name,Book_Price,Book_Balance,List_Price,Book_Type) values(15001,txtBook_N.Text,txtBook_T.Text,txtBook_R.Text,txtBook_P.Text,Gb_1)", mycon)
mycmd.Parameters.AddWithValue("@Book_Name", txtBook_N.Text)
mycmd.Parameters.AddWithValue("@Book_Balance", txtBook_T.Text)
mycmd.Parameters.AddWithValue("@List_Price", txtBook_R.Text)
mycmd.Parameters.AddWithValue("@Book_Price", txtBook_P.Text)
If Rdb_1.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_1.Text)
ElseIf Rdb_2.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_2.Text)
ElseIf Rdb_3.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_3.Text)
ElseIf Rdb_4.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_4.Text)
End If
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
ElseIf mycmd1.ExecuteScalar > 0 Then
mycon.Close()
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Book;", mycon)
mycmd2 = New OleDbCommand("SELECT MAX(Book_ID) FROM Book;", mycon)
mycmd = New OleDbCommand("Insert INTO Book(Book_ID,Book_Name,Book_Price,Book_Balance,List_Price,Book_Type) values(" & mycmd2.ExecuteScalar + 1 & ",txtBook_N.Text,txtBook_T.Text,txtBook_R.Text,txtBook_P.Text,Gb_1)", mycon)
mycmd.Parameters.AddWithValue("@Book_Name", txtBook_N.Text)
mycmd.Parameters.AddWithValue("@Book_Balance", txtBook_T.Text)
mycmd.Parameters.AddWithValue("@List_Price", txtBook_R.Text)
mycmd.Parameters.AddWithValue("@Book_Price", txtBook_P.Text)
If Rdb_1.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_1.Text)
ElseIf Rdb_2.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_2.Text)
ElseIf Rdb_3.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_3.Text)
ElseIf Rdb_4.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", Rdb_4.Text)
End If
mycon.Close()
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
End If
txtBook_N.Text = ""
txtBook_T.Text = ""
txtBook_R.Text = ""
txtBook_P.Text = ""
End Sub
Private Sub BtnClear_3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear_3.Click
txtBook_N.Text = ""
txtBook_T.Text = ""
txtBook_R.Text = ""
txtBook_P.Text = ""
End Sub
Private Sub BtnClear_4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear_4.Click
txtBook_N.Text = ""
txtBook_T.Text = ""
txtBook_R.Text = ""
txtBook_P.Text = ""
End Sub
Private Sub Btn_Serch_2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Serch_2.Click
mycon.Open()
mycmd = New OleDbCommand("SELECT * FROM Book WHERE Book_ID = " & txtSearch_2.Text & ";", mycon)
myreader = mycmd.ExecuteReader()
Dim a As Stringhttps://www.thaicreate.com/topic/new.html#top
While myreader.Read()
txtBook_C_2.Text = CStr(myreader.Item("Book_ID"))
txtBook_N_2.Text = CStr(myreader.Item("Book_Name"))
txtBook_T_2.Text = CStr(myreader.Item("Book_Price"))
txtBook_R_2.Text = CStr(myreader.Item("Book_Balance"))
txtBook_P_2.Text = CStr(myreader.Item("List_Price"))
a = CStr(myreader.Item("Book_Type"))
If a = "นิตยสาร" Then
RadioButton4.Checked = True
ElseIf a = "วารสาร" Then
RadioButton3.Checked = True
ElseIf a = "นวนิยาย" Then
RadioButton2.Checked = True
ElseIf a = "การ์ตูน" Then
RadioButton1.Checked = True
End If
End While
mycon.Close()
End Sub
Private Sub BtnDel_2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDel_2.Click
If MsgBox("คุณแน่ใจหรือว่าต้องการลบข้อมูลนี้?", MsgBoxStyle.Question + MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
mycmd = New OleDbCommand("delete from Book where Book_ID = " & txtSearch_2.Text & " ", mycon)
mycmd.Parameters.AddWithValue("@Book_ID", txtSearch_2.Text)
mycmd = New OleDbCommand("delete from Book where Book_ID = " & txtSearch_2.Text & " ", mycon)
mycmd.Parameters.AddWithValue("@Book_ID", txtSearch_2.Text)
End If
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
txtBook_C_2.Text = ""
txtBook_N_2.Text = ""
txtBook_T_2.Text = ""
txtBook_R_2.Text = ""
txtBook_P_2.Text = ""
Gb_1.Text = ""
End Sub
Private Sub BtnEdit_2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit_2.Click
mycmd = New OleDbCommand("UPDATE Book set Book_Name=@Book_Name,Book_Balance=@Book_Balance,List_Price=@List_Price,Book_Price=@Book_Price,Book_Type=@Book_Type where Book_ID = " & txtBook_C_2.Text & ";", mycon)
mycmd.Parameters.AddWithValue("@Book_Name", txtBook_N_2.Text)
mycmd.Parameters.AddWithValue("@Book_Balance", txtBook_T_2.Text)
mycmd.Parameters.AddWithValue("@List_Price", txtBook_R_2.Text)
mycmd.Parameters.AddWithValue("@Book_Price", txtBook_P_2.Text)
If RadioButton4.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", RadioButton4.Text)
ElseIf RadioButton3.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", RadioButton3.Text)
ElseIf RadioButton2.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", RadioButton2.Text)
ElseIf RadioButton1.Checked = True Then
mycmd.Parameters.AddWithValue("@Book_Type", RadioButton1.Text)
End If
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
txtBook_N_2.Text = ""
txtBook_T_2.Text = ""
txtBook_R_2.Text = ""
txtBook_P_2.Text = ""
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' If txtBook_N.Text = "" And txtBook_T.Text = "" And txtBook_R.Text = "" And txtBook_P.Text = "" Then
'MessageBox.Show("กรุณาใส่ข้อมูลให้ครบ", "ผิดพลาด")
' Exit Sub
' End If
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Rent_Book;", mycon)
If mycmd1.ExecuteScalar = 0 Then
mycon.Close()
mycmd = New OleDbCommand("Insert INTO Rent_Book(Rent_Book_Id,Book_ID,Mem_ID,Rent_Date,Deadline) values(1,TextBox2.Text,TextBox3.Text,DateTimePicker2.Value,DateTimePicker4.Value)", mycon)
mycmd.Parameters.AddWithValue("@Book_ID", TextBox2.Text)
mycmd.Parameters.AddWithValue("@Mem_ID", TextBox3.Text)
mycmd.Parameters.AddWithValue("@Rent_Date", DateTimePicker2.Text)
mycmd.Parameters.AddWithValue("@Deadline", DateTimePicker4.Text)
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
ElseIf mycmd1.ExecuteScalar > 0 Then
mycon.Close()
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Rent_Book;", mycon)
mycmd2 = New OleDbCommand("SELECT MAX(Rent_Book_Id) FROM Rent_Book;", mycon)
mycmd = New OleDbCommand("Insert INTO Rent_Book(Rent_Book_Id,Book_ID,Mem_ID,Rent_Date,Deadline) values(" & mycmd2.ExecuteScalar + 1 & ",TextBox2.Text,TextBox3.Text,DateTimePicker2.Text,DateTimePicker4.Text)", mycon)
mycmd.Parameters.AddWithValue("@Book_ID", TextBox2.Text)
mycmd.Parameters.AddWithValue("@Mem_ID", TextBox3.Text)
mycmd.Parameters.AddWithValue("@Rent_Date", DateTimePicker2.Text)
mycmd.Parameters.AddWithValue("@Deadline", DateTimePicker4.Text)
mycon.Close()
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
End If
TextBox2.Text = ""
TextBox3.Text = ""
DateTimePicker2.Text = ""
DateTimePicker4.Text = ""
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Return_Book;", mycon)
If mycmd1.ExecuteScalar = 0 Then
mycon.Close()
mycmd = New OleDbCommand("Insert INTO Return_Book(Re_Book_Id,Mem_ID,Date_Return) values(1,TextBox5.Text,DateTimePicker6.Text)", mycon)
mycmd.Parameters.AddWithValue("@Mem_ID", TextBox5.Text)
mycmd.Parameters.AddWithValue("@Date_Return", DateTimePicker6.Text)
'mycmd.Parameters.AddWithValue("@Penalty", Label28.Text)
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
ElseIf mycmd1.ExecuteScalar > 0 Then
mycon.Close()
mycon.Open()
mycmd1 = New OleDbCommand("SELECT COUNT(*) FROM Return_Book;", mycon)
mycmd2 = New OleDbCommand("SELECT MAX(Rent_Book_Id) FROM Return_Book;", mycon)
mycmd = New OleDbCommand("Insert INTO Return_Book(Mem_ID,Date_Return) values(TextBox5.Text,DateTimePicker6.Text)", mycon)
mycmd.Parameters.AddWithValue("@Mem_ID", TextBox5.Text)
mycmd.Parameters.AddWithValue("@Date_Return", DateTimePicker6.Text)
' mycmd.Parameters.AddWithValue("@Penalty", Label28.Text)
mycon.Close()
mycon.Open()
mycmd.ExecuteNonQuery()
mycon.Close()
End If
TextBox5.Text = ""
DateTimePicker6.Text = ""
Label28.Text = ""
End Sub
End Class
dim strcon as string = "Select * From ? Order by ..."
dim ds as new dataset
dim da as oledbdataadapter = new oledbdataadapter(strcon,connectionstring)
da.fill(ds,"TableName")