Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Public Class database
Private con As OleDbConnection
Private cmd As OleDbCommand
Private strconstring As String
Public Sub New()
strconstring = System.Configuration.ConfigurationManager.ConnectionStrings("Chase.My.MySettings.chaseConnectionString").ConnectionString
End Sub
Public Function querydataset(ByVal strsql As String) As DataSet
Dim ds As DataSet
Dim da As New OleDbDataAdapter
con = New OleDbConnection
With con
.ConnectionString = strconstring
.Open()
End With
cmd = New OleDbCommand
With cmd
.Connection = con
.CommandType = strsql
.CommandType = CommandType.Text
End With
da.SelectCommand = cmd
da.Fill(ds)
Return ds
End Function
Public Function queryexecute(ByVal strsql As String) As Boolean
con = New OleDbConnection
With con
.ConnectionString = strconstring
.Open()
End With
Try
cmd = New OleDbCommand
With cmd
.Connection = con
.CommandType = CommandType.Text
.CommandText = strsql
End With
cmd.ExecuteNonQuery()
Return True
Catch ex As Exception
Return False
End Try
End Function
End Class
หน้า Form
Imports System.Data
Imports System.Data.OleDb
Public Class Form2
Dim clsdb As New database
'Dim fcon As New Main
'Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\Chase\Chase\Chase\bin\Debug\chase.mdb")
'Dim cmd As New OleDbCommand
Sub showdata()
On Error Resume Next
Dim sql As String = "select sid as รหัสพนักงาน,sname as ชื่อพนักงาน,stel as หมายเลขติดต่อ from staff "
Dim ds As DataSet
ds = clsdb.querydataset(sql)
DataGrid1.DataSource = ds.Tables(0)
'Dim ds As New DataSet
'Dim da As New OleDbDataAdapter
'da = New OleDbDataAdapter(sql, con)
'da.Fill(ds, "s")
'DataGrid1.DataSource = ds.Tables("s")
End Sub
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'On Error Resume Next
showdata()
clear()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' fcon.connect()
Dim sql As String = "insert into staff values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"
'con.Open()
'With cmd
' .CommandText = sql
' .CommandType = CommandType.Text
' .Connection = con
' .ExecuteNonQuery()
'End With
'con.Close()
'MsgBox("OK")
If clsdb.queryexecute(sql) = True Then
MsgBox("OK")
End If
showdata()
clear()
clsdb.close()
End Sub
Sub clear()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sql As String = "update staff set sname='" & TextBox2.Text & "',stel='" & TextBox3.Text & "' where sid='" & TextBox1.Text & "'"
'con.Open()
'With cmd
' .CommandText = sql
' .CommandType = CommandType.Text
' .Connection = con
' .ExecuteNonQuery()
'End With
'con.Close()
'MsgBox("OK")
If clsdb.queryexecute(sql) = True Then
MsgBox("OK")
TextBox1.ReadOnly = False
showdata()
clear()
clsdb.close()
Else
MsgBox("ไม่สารถแก้ไขได้")
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim sql As String = "delete from staff where sid='" & TextBox1.Text & "'"
'con.Open()
'With cmd
' .CommandText = sql
' .CommandType = CommandType.Text
' .Connection = con
' .ExecuteNonQuery()
'End With
'con.Close()
'MsgBox("OK")
If clsdb.queryexecute(sql) = True Then
MsgBox("OK")
End If
showdata()
clear()
clsdb.close()
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim sql As String = "select * from staff where sname='" & TextBox2.Text & "' "
Dim ds As DataSet
ds = clsdb.querydataset(sql)
TextBox1.Text = ds.Tables(0).Rows(0)("sid")
TextBox2.Text = ds.Tables(0).Rows(0)("sname")
TextBox3.Text = ds.Tables(0).Rows(0)("stel")
TextBox1.ReadOnly = True
clsdb.close()
End Sub
End Class