Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim cmd As New OleDb.OleDbCommand
'status automatic display when add all item
Dim Status As String = "avalaible"
txtStatus.Text = Status
If Not cnn.State = ConnectionState.Open Then
'open connection if it its not open yet
cnn.Open()
End If
cmd.Connection = cnn
'check whethter add an new or update
If Me.txtEquipmentID.Tag & "" = "" Then
'add new data to table
cmd.CommandText = "INSERT INTO Equipment(EquipmentID, EquipmentItem, Model, Description, Quantity, SerialNo, Status) " & _
"VALUES(" & Me.txtEquipmentID.Text & ",'" & Me.cboEquipmentItem.Text & "','" & Me.cboModel.Text & "', '" & Me.txtDescription1.Text & "', '" & _
Me.txtQtty.Text & "','" & Me.txtSerialNo.Text & "','" & Me.txtStatus.Text & "')"
cmd.ExecuteNonQuery()
'display msge box
MsgBox("Successfull added")
Else
'update data in datatable
cmd.CommandText = "UPDATE Equipment " & _
"SET EquipmentID= " & Me.txtEquipmentID.Text & _
", EquipmentItem='" & Me.cboEquipmentItem.Text & "'" & _
", Model='" & Me.cboModel.Text & "'" & _
", Description='" & Me.txtDescription1.Text & "'" & _
", Quantity='" & Me.txtQtty.Text & "'" & _
", SerialNo='" & Me.txtSerialNo.Text & "'" & _
", Status='" & Me.txtStatus.Text & "'" & _
" WHERE EquipmentID=" & Me.txtEquipmentID.Tag
cmd.ExecuteNonQuery()
End If
Me.btnClear.PerformClick()
'close conneciton
cnn.Close()
End Sub
This my Edit code
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
'check for the selected item in list
If Me.dgvEquipment.Rows.Count Then
If Me.dgvEquipment.SelectedRows.Count > 0 Then
Dim intEquipmentID As Integer = Me.dgvEquipment.SelectedRows(0).Cells("id").Value
'get data form database followed by equipid
'open connection
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
'find command how to change STATUS CHANGE AUTOMATIC from AVAILABLE to UNVALAIBLE when edit the description
'your code here
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Equipment " & _
"WHERE EquipmentID=" & intEquipmentID, cnn)
Dim dtEquipment As New DataTable
da.Fill(dtEquipment)
Me.txtEquipmentID.Text = intEquipmentID
Me.cboEquipmentItem.Text = dtEquipment.Rows(0).Item("EquipmentItem")
Me.cboModel.Text = dtEquipment.Rows(0).Item("Model")
Me.txtDescription1.Text = dtEquipment.Rows(0).Item("Description")
Me.txtQtty.Text = dtEquipment.Rows(0).Item("Quantity")
Me.txtSerialNo.Text = dtEquipment.Rows(0).Item("SerialNo")
Me.txtStatus.Text = dtEquipment.Rows(0).Item("Status")
'hide the id to be edited in TAG if txtequipid in case change
Me.txtEquipmentID.Tag = intEquipmentID
'change button add to update
Me.btnAdd.Text = "Update"
'disable button edit
Me.btnEdit.Enabled = False
'close connection
cnn.Close()
End If
End If
End Sub