Database Update Error

Jmekubo

New member
Joined
Jun 20, 2013
Messages
2
Programming Experience
3-5
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.
imgs (1).pngimgs (2).pngsnapshot3.png

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
 
". 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
"

Make sure you clear the old item from the box when you update. I had this issue when updating my lists before. I realized when I queried the db for an update I wasn't clearing my local list before adding the items again, resulting in duplicates on the local end. Hope this helps.
 
Back
Top