Data not updated correctly

Question

i got 2 forms... Dress_Price is for displaying the data form database ( MS Access 2007 ) another form,Edit_Dress is to edit and update the database..

the code successfully updated the data based on the changes from the form Edit_Dress.. but there is 2 problems -

1. the dgvCustomerDressPrice did not refreshed after updating..

2. there is "space" being added to the record after updated..

   before update price value

   Dress_Name = "Tuxedo" Dress_Price = "150"

   after update price value

   Dress_Name = " Tuxedo" Dress_Price = " 250"

the "space" keeps being added up everytime i update the record... so the search function cant work properly because of the space..

code on Dress_Price :-

Private Sub Dress_Price_Load(sender As Object, e As EventArgs) Handles MyBase.Load
                con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\annonymous\Documents\Visual Studio 2012\Projects\TMS Final\TMS Final\db\db_TMS.accdb"
                con.Open()        

    dgvCustomerDressPrice()        
End Sub 

Private Sub dgvCustomerDressPrice() 

        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDb.OleDbDataAdapter

        da = New OleDb.OleDbDataAdapter("SELECT * FROM tbl_dress", con)

        da.Fill(dt)

        dgvDressPrice.DataSource = dt.DefaultView

        dgvDressPrice.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        con.Close()
    End Sub

Private Sub btnEditDress_Click(sender As Object, e As EventArgs) Handles btnEditDress.Click

        If dgvDressPrice.Rows.Count > 0 Then ' when user click a row, any query for database will based on Order_ID

            If dgvDressPrice.SelectedRows.Count > 0 Then

                Dim intDressID As Integer = dgvDressPrice.SelectedRows(0).Cells("Dress_ID").Value 


                Try
                    If Not con.State = ConnectionState.Open Then
                        con.Open()
                    End If

                    Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM tbl_dress WHERE Dress_ID =" & intDressID, con)
                    ' the record that will be edited is based on the Customer_ID of particular row clicked

                    Dim dt As New DataTable

                    da.Fill(dt)

                    Edit_Dress.txtDressID.Text = intDressID
                    Edit_Dress.txtDressName.Text = dt.Rows(0).Item("Dress_Name")
                    Edit_Dress.txtDressPrice.Text = dt.Rows(0).Item("Dress_Price")
                    ' pass the data from tbl_user into each represented field

                Catch ex As Exception
                    MessageBox.Show("Failed to edit data ! System eror : " & ex.ToString, "Eror !", MessageBoxButtons.OK)
                End Try
            End If
        End If
        Edit_Dress.Show()
    End Sub

Private Sub txtSearch_TextChanged(sender As Object, e As EventArgs) Handles txtSearch.TextChanged
        If Not con.State = ConnectionState.Open Then
            con.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM tbl_dress WHERE Dress_Name like '" & txtSearch.Text & "%' ", con)

        Dim dt As New DataTable
        da.Fill(dt)

        dgvCustomerDressPrice().DataSource = dt

        con.Close()
    End Sub

code on Edit_Dress :-

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        con = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\annonymous\Documents\Visual Studio 2012\Projects\TMS Final\TMS Final\db\db_TMS.accdb")

        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If

            Dim intDressID As Integer
            intDressID = Convert.ToInt32(txtDressID.Text)
            intDressID = Integer.Parse(txtDressID.Text)

            Dim intDressPrice As Integer
            intDressPrice = Convert.ToInt32(txtDressPrice.Text)
            intDressPrice = Integer.Parse(txtDressPrice.Text)

            Dim query As String = "UPDATE tbl_dress SET Dress_Name = ' " & txtDressName.Text & " ' ,  Dress_Price = ' " & intDressPrice & " '  WHERE Dress_ID =  " & intDressID & "  "

            Dim cmd As New OleDb.OleDbCommand(query, con)

            cmd.ExecuteNonQuery()

            MessageBox.Show("Data updated !", "", MessageBoxButtons.OK, MessageBoxIcon.Information)

            Dress_Price.RefreshPriceList()

            con.Close()

            Me.Close()
        Catch ex As Exception
            MessageBox.Show("Failed to save into database ! System eror : " & ex.Message, " ", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

Answer 1

Looking at your UPDATE method it is clear why you have a space added every time. You put it in the update string.

Dim query As String = "UPDATE tbl_dress SET Dress_Name = ' " & _
                                                          ^ here
                      txtDressName.Text & " ' ,  Dress_Price = ' " & _
                                           ^here                ^here
                      intDressPrice & " '  WHERE Dress_ID =  " & intDressID & "  "
                                       ^here

A part from the simple error that could be fixed removing the space, this is not the correct way to create an update command. You should use a parameterized query

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
    con = New OleDb.OleDbConnection(.....)
    Try
      If con.State = ConnectionState.Closed Then
         con.Open()
      End If

      Dim intDressID As Integer
      intDressID = Convert.ToInt32(txtDressID.Text)

      Dim intDressPrice As Integer
      intDressPrice = Convert.ToInt32(txtDressPrice.Text)

      Dim query As String = "UPDATE tbl_dress SET Dress_Name = ?, Dress_Price = ? " & _
                            "WHERE Dress_ID =  ?"
      Dim cmd As New OleDb.OleDbCommand(query, con)
      cmd.Parameters.AddWithValue("@p1", txtDressName.Text)
      cmd.Parameters.AddWithValue("@p2", intDressPrice)
      cmd.Parameters.AddWithValue("@p3", intDressID)
      cmd.ExecuteNonQuery()
      .....