'คือ ผมต้องการไห้ โชว์ค่า Name ใน combobox เเต่ เวลา Insert ไห้ Insert ID เเทนครับ
'EX code ของ ผม คือ โชว์ค่า ID เเละ insert ID ครับ
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Public Class car
Private nameFileAcc As String = "C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\WORK\WORK\WORK.accdb"
Private _dataset As DataSet
Private _conn As OleDbConnection
Private _cmd As OleDbCommand
Private _rowCount As Integer = 0
Private _position As Integer = 0
Private Sub FormProducts_Load() Handles Me.Load
_conn = New OleDbConnection(_conStr)
_conn.Open()
'====================อ่านข้อมูลจากตารางเพื่อเติมลงในช่อง combo==========================
Dim sql As String = "SELECT * FROM statcar"
_cmd = New OleDbCommand(sql, _conn)
Dim reader As OleDbDataReader = _cmd.ExecuteReader()
'เติมชื่อลูกค้าที่มีลงใน ComboBox
While reader.Read()
Cmbstat.Items.Add(reader("statcar_namestat"))
End While
Dim sql1 As String = "SELECT * FROM Car_type order by Cartype_name"
_cmd = New OleDbCommand(sql1, _conn)
Dim reader1 As OleDbDataReader = _cmd.ExecuteReader()
'เติมชื่อลูกค้าที่มีลงใน ComboBox
While reader1.Read()
cmbtype.Items.Add(reader1("cartype_ID")) <<-----
End While
'=========================================================================================
FillListBox()
ReadData()
UpdateStatus()
End Sub
Private Sub ReadData()
Dim dTable As New DataTable
Dim sqlStr As String
Dim conDBACC As New ConnectDBACC(nameFileAcc)
sqlStr = "SELECT * FROM car,statcar,car_type "
sqlStr += "WHERE car.car_typecarID=car_type.cartype_ID AND car.car_stat=statcar.statcar_ID"
sqlStr += " AND car.car_NOCar='" + ListBox1.SelectedItem.ToString + "' "
dTable = conDBACC.Query(sqlStr)
If dTable.Rows.Count > 0 Then
textno.Text = dTable.Rows(0)("Car_NOCar")
TextID.Text = dTable.Rows(0)("Car_ID")
Textdriver.Text = dTable.Rows(0)("Car_Driver")
cmbtype.Text = dTable.Rows(0)("Car_TypeCarID") <------
Textag.Text = dTable.Rows(0)("Car_AgenciesName")
Textbrand.Text = dTable.Rows(0)("Car_Brand")
Textmodel.Text = dTable.Rows(0)("Car_Model")
textstart.Value = dTable.Rows(0)("Car_CarStart")
Cmbstat.Text = dTable.Rows(0)("statCar_namestat")
End If
End Sub
Private Sub FillListBox()
'เพิ่มรายชื่อหมวดสินค้าลงใน ListBox
Dim prodname As String = ""
ListBox1.Items.Clear()
Dim dTable As New DataTable
Dim sqlStr As String
Dim conDBACC As New ConnectDBACC(nameFileAcc)
sqlStr = "SELECT * FROM car,statcar,car_type WHERE car.car_typecarID=car_type.cartype_ID AND car.car_stat=statcar.statcar_ID order by car_nocar"
dTable = conDBACC.Query(sqlStr)
For i = 0 To dTable.Rows.Count - 1
prodname = dTable.Rows(i)("Car_nocar").ToString
ListBox1.Items.Add(prodname)
Next
'ให้รายการลำดับแรกใน ListBox ถูกเลือก
If (ListBox1.Items.Count > 0) Then
ListBox1.SelectedIndex = 0
End If
End Sub
''อ่านรูปภาพมาแสดงที่ PictureBox ซึ่งเราต้องทราบลำดับแถว
''เพื่อที่จะใช้ในการอ่านข้อมูลจาก DataSet
'Private Sub ShowPicture(ByVal index As Integer)
' Dim imgByte() As Byte = _
' _dataset.Tables("car").Rows(index)("Car_Picture")
' Dim imgStream As New MemoryStream(imgByte)
' If (imgStream.Length > 0) Then
' PictureBox1.Image = Image.FromStream(imgStream)
' Else
' PictureBox1.Image = Nothing
' End If
'End Sub
Private Sub BtnSave_Click() Handles BtnSave.Click
'Or Texttype.Text = ""
If (textno.Text = "" Or Textag.Text = "" Or Textbrand.Text = "" Or Textmodel.Text = "" Or textstart.Text = "") Then
MsgBox("กรุณาใส่ข้อมูลไห้ครบ")
Return
End If
Insert
Dim sql As String = ""
If (TextID.Text = "") Then
sql = "INSERT INTO car(Car_nocar,Car_driver,Car_typecarid,Car_Agenciesname "
sql &= ",Car_brand,Car_model,Car_carstart,Car_stat) VALUES(@no,@dr,@type,@ag,@br,@mo,@start,"
'==========================================================================================
If (Cmbstat.Text = "ว่าง") Then
sql &= "'1')"
End If
If (Cmbstat.Text = "ไม่ว่าง") Then
sql &= "'2')"
End If
If (Cmbstat.Text = "เสีย") Then
sql &= "'3')"
End If
If (Cmbstat.Text = "ซ่อม") Then
sql &= "'4')"
End If
If (Cmbstat.Text = "พัง") Then
sql &= "'5')"
End If
'==========================================================================================
Update
Else
sql = "UPDATE car SET "
sql &= "car_nocar = @no, car_driver = @dr,Car_typecarid = @type,Car_Agenciesname = @ag ,"
sql &= "Car_brand = @br,Car_model = @mo , Car_carstart = @start , car_stat="
If (Cmbstat.Text = "ว่าง") Then
sql &= "'1'"
End If
If (Cmbstat.Text = "ไม่ว่าง") Then
sql &= "'2'"
End If
If (Cmbstat.Text = "เสีย") Then
sql &= "'3'"
End If
If (Cmbstat.Text = "ซ่อม") Then
sql &= "'4'"
End If
If (Cmbstat.Text = "พัง") Then
sql &= "'5'"
End If
''ถ้าได้เลือกรูปภาพเอาไว้จึงจะสั่งอัพเดทรูปภาพด้วย
'If (OpenFileDialog1.FileName <> "") Then
' sql &= ", car_Picture = @pic "
'End If
sql &= " WHERE car_ID = " & TextID.Text
End If
_cmd = New OleDbCommand(sql)
_cmd.Parameters.AddWithValue("no", textno.Text)
_cmd.Parameters.AddWithValue("dr", Textdriver.Text)
_cmd.Parameters.AddWithValue("type", cmbtype.Text) <------
_cmd.Parameters.AddWithValue("ag", Textag.Text)
_cmd.Parameters.AddWithValue("br", Textbrand.Text)
_cmd.Parameters.AddWithValue("mo", Textmodel.Text)
_cmd.Parameters.AddWithValue("start", textstart.Text)
Dim conDBACC As New ConnectDBACC(nameFileAcc)
conDBACC.ExecuteCommand(_cmd)
Dim name As String = textno.Text
FillListBox()
ReadData()
'Dim pic() As Byte = {}
''ถ้าได้เลือกรูปภาพเอาไว้จึงจะเพิ่มข้อมูลรูปภาพลงไป
'If (OpenFileDialog1.FileName <> "") Then
' Dim fiStream As New FileStream(OpenFileDialog1.FileName, _
' FileMode.Open, _
' FileAccess.Read)
' Dim binReader As New BinaryReader(fiStream)
' pic = binReader.ReadBytes(fiStream.Length)
' _cmd.Parameters.AddWithValue("pic", pic)
'Else
' _cmd.Parameters.AddWithValue("pic", pic)
'End If
' ''Dim affectedRow As Integer = _cmd.ExecuteNonQuery
' ''If (affectedRow < 1) Then
' '' ToolStripStatusLabel1.Text = "เกิดข้อผิดพลาดในการบันทึกข้อมูล"
' '' Return
' ''Else
' '' ToolStripStatusLabel1.Text = "ข้อมูลถูกจัดเก็บแล้ว"
'FillListBox()
'ReadData()
'ClearBindings()
' '' If (TabControl1.SelectedIndex = 0) Then
' '' End If
'ให้รายการที่เพิ่มหรือแก้ไขเป็นรายการที่ถูกเลือกใน ListBox
Dim idx As Integer = ListBox1.FindStringExact(name)
If (idx > -1) Then
ListBox1.SetSelected(idx, True)
End If
' '' UpdateStatus()
' ''End If
End Sub
'Private Sub LnkPic_LinkClicked() Handles LnkPic.LinkClicked
' With OpenFileDialog1
' .Filter = "Image Files(*.gif;*.jpg)|*.gif;*.jpg"
' .FileName = ""
' If (.ShowDialog() = DialogResult.OK) Then
' PictureBox1.Image = Image.FromFile(.FileName)
' End If
' End With
'End Sub
Private Sub ListBox1_SelectedIndexChanged() _
Handles ListBox1.SelectedIndexChanged
Dim index As Integer = ListBox1.SelectedIndex
_position = index
ReadData()
UpdateStatus()
End Sub
Private Sub UpdateStatus()
Dim dTable As New DataTable
Dim sqlStr As String
Dim conDBACC As New ConnectDBACC(nameFileAcc)
sqlStr = "SELECT * FROM car INNER JOIN statcar ON car.car_Stat=statcar.statcar_ID "
dTable = conDBACC.Query(sqlStr)
'If dTable.Rows.Count > 0 Then
ToolStripStatusLabel1.Text = _
(_position + 1) & " of " & dTable.Rows.Count
End Sub
Private Sub TabControl1_SelectedIndexChanged() _
Handles TabControl1.SelectedIndexChanged
If (TabControl1.SelectedIndex = 0) Then
FillListBox()
ReadData()
'If (_rowCount = 0) Then
' Return
'End If
BtnDelete.Enabled = True
ListBox1.Enabled = True
ListBox1.SetSelected(0, True)
ElseIf (TabControl1.SelectedIndex = 1) Then
ClearBindings()
textstart.Value = Date.Today
Cmbstat.Text = "ว่าง"
BtnDelete.Enabled = False
ListBox1.Enabled = False
End If
End Sub
Private Sub ClearBindings()
For Each c As Object In GroupBox1.Controls
If (TypeOf c Is TextBox) Then
c.Text = ""
c.DataBindings.Clear()
End If
Next
'PictureBox1.Image = Nothing
End Sub
Private Sub BtnDelete_Click() Handles BtnDelete.Click
If (textno.Text = "") Then
Return
End If
If (MsgBox("ลบข้อมูลแถวนี้ ?", MsgBoxStyle.OkCancel) = _
MsgBoxResult.Cancel) Then
Return
End If
Dim sql As String = _
"DELETE FROM car WHERE car_ID = " & TextID.Text
_cmd = New OleDbCommand(sql)
Dim conDBACC As New ConnectDBACC(nameFileAcc)
conDBACC.ExecuteCommand(_cmd)
ReadData()
'Dim r As Integer = _cmd.ExecuteNonQuery()
'If (r > 0) Then
' ToolStripStatusLabel1.Text = "ข้อมูลถูกลบแล้ว"
'End If
Dim idx0 As Integer = ListBox1.SelectedIndex
ClearBindings()
FillListBox()
ReadData()
If (idx0 > 0) Then
ListBox1.SetSelected(idx0 - 1, True)
End If
End Sub
Private Sub FormProducts_FormClosed() Handles Me.FormClosed
End Sub
Private Sub Cbstat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cmbstat.SelectedIndexChanged
End Sub
''new
'Private Sub Showstat()
' Dim sql As String
' sql = "SELECT * FROM Cart_type "
' _cmd = New OleDbCommand(sql, _conn)
' _cmd.Parameters.AddWithValue("type", Cmbstat.SelectedItem)
' _adapter = New OleDbDataAdapter(_cmd)
' _dataset = New DataSet()
' _adapter.Fill(_dataset, "ord")
' ' DataGridView1.DataSource = _dataset.Tables("ord")
'End Sub
'Private Sub CmbCust_SelectedIndexChanged() _
'Handles Cmbstat.SelectedIndexChanged
' Showstat()
'End Sub
End Class
Private Sub FillListBox()
Dim prodname As String = ""
ListBox1.Items.Clear()
Dim dTable As New DataTable
Dim sqlStr As String
Dim conDBACC As New ConnectDBACC(nameFileAcc)
sqlStr = "SELECT * FROM car,statcar,car_type WHERE car.car_typecarID=car_type.cartype_ID AND car.car_stat=statcar.statcar_ID order by car_nocar"
dTable = conDBACC.Query(sqlStr)
if dTable.rows.count>0 then
ListBox1.ValueMember = "car_id"
ListBox1.DisplayMember = "car_name"
ListBox1.DataSource = dTable
end if
End Sub
Load balance : Server 01
