Imports System.Data
Imports System.Data.OleDb
Public Class adduser
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database/database.mdb;")
Dim cmd As OleDbCommand
Dim myDA As OleDbDataAdapter
Dim Cnn As New OleDbConnection(Cnnstring)
Dim Cnnstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database/database.mdb;"
Dim SqlSelect As String
Dim OleReader As OleDbDataReader
Dim UserIDA As String
Dim PassIDA As String
Dim IsFind As Boolean
Dim myDataAdapter As New OleDbDataAdapter
Dim myDataset As New DataSet()
Dim myDataRow As DataRow
Dim SqlComm, myTable As String
Private Sub ขอมลผใชในระบบToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub ออกจากระบบToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Public Sub ShowData()
cmd = New OleDbCommand("Select * FROM tb_user", con)
If con.State = ConnectionState.Closed Then con.Open()
myDA = New OleDbDataAdapter(cmd)
myDataset = New DataSet()
myDA.Fill(myDataset, "MyTable")
DataGridView1.DataSource = myDataset.Tables("MyTable").DefaultView
con.Close()
End Sub
Private Sub DatagridID()
Dim DatagridAllSale As DataGridTableStyle = New DataGridTableStyle
Dim C1 As New DataGridTextBoxColumn
Dim C2 As New DataGridTextBoxColumn
With DatagridAllSale
.AlternatingBackColor = Color.PaleTurquoise
.BackColor = Color.White
.MappingName = "tb_card"
End With
With C1
.HeaderText = "รหัสบัตร"
.MappingName = "id_card"
.Alignment = HorizontalAlignment.Center
.ReadOnly = True
.Width = 150
End With
DatagridAllSale.GridColumnStyles.Add(C1)
With C2
.HeaderText = "สถานะบัตร"
.MappingName = "status"
.Alignment = HorizontalAlignment.Center
.ReadOnly = True
.Width = 150
End With
DatagridAllSale.GridColumnStyles.Add(C2)
End Sub
Private Sub adduser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DatabaseDataSet.tb_user' table. You can move, or remove it, as needed.
Me.Tb_userTableAdapter.Fill(Me.DatabaseDataSet.tb_user)
ShowData()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If TextBox5.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf TextBox6.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf TextBox2.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf TextBox3.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Cnnstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database/database.mdb;"
With Cnn
If .State = ConnectionState.Open Then Close()
.ConnectionString = Cnnstring
.Open()
End With
SqlSelect = "select * from tb_user Where username = " & "'" & TextBox5.Text & "'"
Dim OleCommand As New OleDbCommand(SqlSelect, Cnn)
Dim intNumRows As Integer
intNumRows = OleCommand.ExecuteScalar
If intNumRows > 0 Then
MessageBox.Show("มี username นี้ในระบบแล้ว", "ขัดข้อง", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Cnn.Close()
Else
cmd = New OleDbCommand("Insert Into tb_user(username,password,firstname,surname) Values('" & TextBox5.Text & "', '" & TextBox6.Text & "', '" & TextBox2.Text & "', '" & TextBox3.Text & "')", con)
If con.State = ConnectionState.Closed Then con.Open()
cmd.ExecuteNonQuery()
ShowData() 'Rebinding to DataGridView and view result
MessageBox.Show("เพิ่ม username นี้ในระบบแล้ว", "เสร็จ", MessageBoxButtons.OK, MessageBoxIcon.Information)
con.Close()
End If
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If TextBox5.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf TextBox6.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf TextBox2.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf TextBox3.Text = "" Then
MessageBox.Show("ข้อมูลไม่ครบ", "ใส่ข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Cnnstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database/database.mdb;"
With Cnn
If .State = ConnectionState.Open Then Close()
.ConnectionString = Cnnstring
.Open()
End With
SqlSelect = "select * from tb_user Where username = " & "'" & TextBox5.Text & "'"
Dim OleCommand As New OleDbCommand(SqlSelect, Cnn)
Dim intNumRows As Integer
intNumRows = OleCommand.ExecuteScalar
Cnn.Close()
If intNumRows > 0 Then
cmd = New OleDbCommand("Update tb_user Set password='" & TextBox6.Text & ",'firstname='" & TextBox2.Text & ",surname='" & TextBox3.Text & " Where username='" & TextBox5.Text & "'", con)
If con.State = ConnectionState.Closed Then con.Open()
cmd.ExecuteNonQuery()
ShowData() 'Rebinding to DataGridView and view result
MessageBox.Show("แก้ไข username นี้ในระบบแล้ว", "เสร็จ", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("ไม่พบ username นี้ในระบบ", "ขัดข้อง", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
con.Close()
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
cmd = New OleDbCommand("Delete * From tb_user Where username='" & TextBox5.Text & "'", con)
If con.State = ConnectionState.Closed Then con.Open()
cmd.ExecuteNonQuery()
ShowData() 'Rebinding to DataGridView and view result
MessageBox.Show("ลบ username นี้ในระบบแล้ว", "เสร็จ", MessageBoxButtons.OK, MessageBoxIcon.Information)
con.Close()
End Sub
Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
End Sub
Private Sub DataGridView1_CellMouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseUp
If e.RowIndex = -1 Then Exit Sub
With DataGridView1
TextBox5.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString()
TextBox6.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString()
TextBox2.Text = .Rows.Item(e.RowIndex).Cells(2).Value.ToString()
TextBox3.Text = .Rows.Item(e.RowIndex).Cells(3).Value.ToString()
TextBox5.Focus()
TextBox5.SelectAll()
End With
End Sub
End Class
ตัวอย่างค่าsqlที่สมบูรณ์ตอนรันครับ
"Insert Into tb_user(username,password,firstname,surname) Values('werasak199783', '4214251', 'วี2', 'สัก3')"
"Update tb_user Set password='4214251,'firstname='วี12,surname='สัก2 Where username='werasak199782'"
"Insert Into tb_user(username,password,firstname,surname) Values('werasak199783', '4214251', 'วี2', 'สัก3')"
"Update tb_user Set password='4214251,'firstname='วี12,surname='สัก2 Where username='werasak199782'"
รองแก้ให้เป็นแบบนี้ครับ
"Insert Into tb_user (username,password,firstname,surname) Values ('werasak199783', '4214251', 'วี2', 'สัก3')"
"Update tb_user Set password='4214251','firstname='วี12',surname='สัก2' Where username='werasak199782'"
ถ้ายัง error อีกเอา error ที่ได้มา post ด้วยครับ
Date :
2011-11-21 11:35:21
By :
Nameless
No. 2
Guest
ลองแก้แล้วครับ
"Insert Into tb_user (username,password,firstname,surname) Values ('werasak199783', '4214251', 'วี1', 'สัก2')"
Syntax error in INSERT INTO statement.
"Update tb_user Set password='4214251',firstname='วี1',surname='สัก2' Where username='werasak199782'"
Syntax error in UPDATE statement.