Hi guys ,
I have a problem when updating my SQL database. When am trying to update an item( White Bread 40g), the update works fine but I get the following problems:
1. The ItemName combobox item list displays duplicate values for the same item. i.e old itemname(White Bread 40g) and itemname after update(White Bread 80g). I only need to see the new Itemname after update.How can resolve this? See snapshot 2
2. Also on SQL database table "Items". A row exists with correct updated item "White Bread 80g" but again a different row Itemname column is ovewritten with the old Itemname description i.e " White Bread 40g". Category,Units and Price for that row remain same. see snapshot 3.
Find below codes for updating, deleting and saving.
Will appreciate your input.
I have a problem when updating my SQL database. When am trying to update an item( White Bread 40g), the update works fine but I get the following problems:
1. The ItemName combobox item list displays duplicate values for the same item. i.e old itemname(White Bread 40g) and itemname after update(White Bread 80g). I only need to see the new Itemname after update.How can resolve this? See snapshot 2
2. Also on SQL database table "Items". A row exists with correct updated item "White Bread 80g" but again a different row Itemname column is ovewritten with the old Itemname description i.e " White Bread 40g". Category,Units and Price for that row remain same. see snapshot 3.
Find below codes for updating, deleting and saving.
Will appreciate your input.
VB.NET:
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
Dim sConnectionString As String
' connection string
sConnectionString = "Server=JOHN-MEKUBO\SQLEXPRESS;Initial Catalog=Store Management System;Integrated Security=SSPI "
Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()
Dim DaSitems As New DataSet("Items")
Dim dtItem As New DataTable
Dim IDD As String = Val(txtItemID.Text)
Dim SelectedEditRow As DaSitems.ItemsRow
Dim Response As String
DTaitems.FillSchema(DaSitems1, SchemaType.Source, "Items")
DTaitems.Fill(DaSitems1, "Items")
Dim ITName As String = cboItemName.SelectedValue
Dim UPrice As String = txtUnitPrice.Text
If ITName = "" Or UPrice = "" Then
MsgBox("Please fill up all Item details.", MsgBoxStyle.Critical)
Exit Sub
End If
MsgBox("Make Sure The Item to Edit is selected first!!!")
Response = MsgBox("Are you sure you want to edit selected Item?", 35)
cboItemName.Focus()
Select Case Response
Case 6
cboItemName.Select()
'get Row to edit
SelectedEditRow = DaSitems1.Items.FindByItemID(IDD)
SelectedEditRow("Item Name") = ITName
SelectedEditRow("CategoryID") = txtCategoryID.Text
SelectedEditRow("Category") = txtCategory.Text
SelectedEditRow("UnitID") = txtUnitID.Text
SelectedEditRow("Units") = txtUnits.Text
SelectedEditRow("Unit Price") = UPrice
Case 7
Exit Sub
Case 2
Exit Sub
End Select
Try
' Update the table adapter
DTaitems.Update(Me.DaSitems1, "Items")
DaSitems1.AcceptChanges()
cboItemName.Refresh()
Catch ex As Exception
DaSitems1.RejectChanges()
MsgBox(ex.ToString)
End Try
Me.Refresh()
btnDelete.Enabled = False
btnCancel.Enabled = False
btnClose.Enabled = False
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim sConnectionString As String
' connection string
sConnectionString = "Server=JOHN-MEKUBO\SQLEXPRESS;Initial Catalog=Store Management System;Integrated Security=SSPI "
'Try
Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()
Dim DaSitems As New DataSet("Items")
Dim dtItem As New DataTable
Dim IDD As String = Val(txtItemID.Text)
Dim SelectedDeleteRow As DaSitems.ItemsRow
Dim Response As String
Dim ITName As String = cboItemName.SelectedValue
Dim UPrice As String = txtUnitPrice.Text
DTaitems.FillSchema(DaSitems1, SchemaType.Source, "Items")
DTaitems.Fill(DaSitems1, "Items")
dtItem = DaSitems1.Tables("Items")
If ITName = "" Or UPrice = "" Then
MsgBox("Please fill up all Item details.", MsgBoxStyle.Critical)
Exit Sub
End If
MsgBox("Make Sure The Item to Remove is selected first!!!")
Response = MsgBox("Are you sure you want to delete selected Item?", 35)
Select Response
Case 6
Try
cboItemName.Select()
'get Row to Delete
SelectedDeleteRow = DaSitems1.Items.FindByItemID(IDD)
SelectedDeleteRow("Item Name") = ITName
SelectedDeleteRow("CategoryID") = txtCategoryID.Text
SelectedDeleteRow("Category") = txtCategory.Text
SelectedDeleteRow("UnitID") = txtUnitID.Text
SelectedDeleteRow("Units") = txtUnits.Text
SelectedDeleteRow("Unit Price") = UPrice
' Delete the record
SelectedDeleteRow.Delete()
DTaitems.Update(DaSitems1, "Items")
Me.DaSitems1.AcceptChanges()
MsgBox("Item Deleted")
Catch ex As Exception
DaSitems1.RejectChanges()
MsgBox(ex.ToString)
MsgBox("Action aborted")
End Try
Case 7
Exit Sub
Case 2
Exit Sub
End Select
Me.Refresh()
cboItemName.Refresh()
btnDelete.Enabled = False
btnEdit.Enabled = True
btnCancel.Enabled = False
btnClose.Enabled = False
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
Me.Validate()
Me.ItemsBindingSource.EndEdit()
Me.DTaitems.Update(Me.DaSitems1.Items)
Me.ItemsTableAdapter.Update(Me.Store_Management_SystemDataSet.Items)
Me.Store_Management_SystemDataSet.AcceptChanges()
Me.DaSitems1.AcceptChanges()
MsgBox("Record Changes Saved")
Catch ex As SqlException
MessageBox.Show("SQL Server error # " & ex.Number _
& ": " & ex.Message, ex.GetType.ToString)
MsgBox("Update failed")
Exit Sub
End Try
btnSave.Enabled = False
btnEdit.Enabled = True
btnDelete.Enabled = True
btnCancel.Enabled = True
cboItemName.Update()
txtCategory.Update()
Me.DaSitems1.Clear()
DTaitems.Fill(DaSitems1, "Items")
txtUnits.Refresh()
Me.Refresh()
End Sub
