none
My Update data no work can help me take a look RRS feed

  • Question

  • I fail to update my data to my access database. can help view is i am missing something on my code. current my code no showing any error message.  I have try solve many time but i still no success to solve it.

      Private Sub Edit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Edit.Click

            LoginForm.boolresetPassword = True
            LoginForm.ShowDialog()

            If Me.DataGridView1.Rows.Count > 0 Then
                If Me.DataGridView1.SelectedRows.Count > 0 Then
                    Dim intcid1 As Integer = Me.DataGridView1.SelectedRows(0).Cells("ID").Value
                    'open connection
                    If Not cn3.State = ConnectionState.Open Then
                        cn3.Open()
                    End If
                    'get data into datatable
                    Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM stock " & _
                                                         " WHERE cid=" & intcid1, cn3)
                    Dim dt As New DataTable
                    da.Fill(dt)
                    Me.txtbarcode.Text = intcid1
                    Me.txtdetail1.Text = dt.Rows(0).Item("CheckerDetail")

                    Me.txtbarcode.Tag = intcid1
                    'change button add to update
                    Me.save.Text = "Update"
                    'disable button edit
                    Me.Edit.Enabled = True
                End If
            End If


            cn3.Close()
            'refresh data in list
            RefreshData1()
        End Sub

        Private Sub save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click

            Dim cmd As New OleDb.OleDbCommand

            cmd.CommandText = "UPDATE stock " & _
                  " SET" & _
                   ", [CheckerDetail]='" & Me.txtdetail1.Text & "'" & _
             " WHERE [ID]=" & DataGridView1("ID", DataGridView1.CurrentCell.RowIndex).Value
            ''" WHERE [cid]=" & Me.txtbarcode.Tag
            MsgBox("Update Data Successful", MsgBoxStyle.OkOnly, "Message")

            ''End Try
            RefreshData1()
            cn3.Close()
        End Sub

        Private Sub RefreshData1()

            If Not cn3.State = ConnectionState.Open Then
                'open connection
                cn3.Open()
            End If
            Dim da As New OleDb.OleDbDataAdapter("SELECT cid as [ID], " & _
                                                 "CheckerName as [CheckerName] , ShipQuantity as [Quantity], Date as [Date], CompanyName as [CompanyName], CheckerDetail as [CheckerDetail]" & _
                                                 " FROM stock ORDER BY cid", cn3)
            Dim dt As New DataTable
            'fill data to datatable
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
            'close connection
            cn3.Close()
        End Sub

     
    Thursday, July 11, 2019 7:56 AM

Answers

  • Hi,

    one more ‘,’

     cmd.CommandText = "UPDATE stock " & _
                  " SET" & _
                   " [CheckerDetail]='" & Me.txtdetail1.Text & "'" & _
             " WHERE [ID]=" & DataGridView1("ID", DataGridView1.CurrentCell.RowIndex).Value
            ''" WHERE [cid]=" & Me.txtbarcode.Tag

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by christing Friday, July 12, 2019 2:56 AM
    Thursday, July 11, 2019 9:18 AM
    Moderator

All replies

  • Hi,

    Is the following code missing an execution command? cmd.ExecuteNonQuery()

     Dim cmd As New OleDb.OleDbCommand
    
            cmd.CommandText = "UPDATE stock " & _
                  " SET" & _
                   ", [CheckerDetail]='" & Me.txtdetail1.Text & "'" & _
             " WHERE [ID]=" & DataGridView1("ID", DataGridView1.CurrentCell.RowIndex).Value
            ''" WHERE [cid]=" & Me.txtbarcode.Tag
            MsgBox("Update Data Successful", MsgBoxStyle.OkOnly, "Message")

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 11, 2019 8:09 AM
    Moderator
  • @Alex

    I missing to put out this command cmd.ExecuteNonQuery() 

    Dim cmd As New OleDb.OleDbCommand
            cmd.CommandText = "UPDATE stock " & _
                  " SET" & _
                   ", [CheckerDetail]='" & Me.txtdetail1.Text & "'" & _
                    "WHERE([cid] = " & Me.txtbarcode.Tag
            ''WHERE [ID]=" & DataGridView1("ID", DataGridView1.CurrentCell.RowIndex).Value
            MsgBox("Update Data Successful", MsgBoxStyle.OkOnly, "Message")

            'refresh data in list
            cmd.ExecuteNonQuery()
            RefreshData1()
            cn3.Close()

    now i receive this error ExecuteNonQuery: Connection property has not been initialized. is i need insert value above on ExecuteNonQuery

    my project have 2 datagridview and 2 datatable,. 1 datagridview is let the user add new stock second datagridview is let the user add store quantity remarks the store information the purpose i do edit on for second datagridview is want when user key in wrong information user can be edit on remarks area. 

    Thursday, July 11, 2019 8:35 AM
  • Hi,

    miss code,try my code:

     conn = New OleDbConnection(constr)
            conn.Open()
            Dim cmd As New OleDbCommand
            cmd.Connection = conn
            cmd.CommandText = "xxxxxxx "
            cmd.ExecuteNonQuery()
            conn.Close()

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, July 11, 2019 8:51 AM
    Moderator
  • @Alex is the code i write is correct. but still face error on ( Syntax error in UPDATE statement.)

    Dim conn = New OleDbConnection
            conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=mydataX.mdb;"
            conn.Open()
            Dim cmd As New OleDbCommand
            cmd.Connection = conn
            cmd.CommandText = "UPDATE stock " & _
                  " SET" & _
                   ", [CheckerDetail]='" & Me.txtdetail1.Text & "'" & _
             " WHERE [ID]=" & DataGridView1("ID", DataGridView1.CurrentCell.RowIndex).Value
            ''" WHERE [cid]=" & Me.txtbarcode.Tag
            MsgBox("Update Data Successful", MsgBoxStyle.OkOnly, "Message")
            cmd.ExecuteNonQuery()
            conn.Close()



    Thursday, July 11, 2019 9:10 AM
  • Hi,

    one more ‘,’

     cmd.CommandText = "UPDATE stock " & _
                  " SET" & _
                   " [CheckerDetail]='" & Me.txtdetail1.Text & "'" & _
             " WHERE [ID]=" & DataGridView1("ID", DataGridView1.CurrentCell.RowIndex).Value
            ''" WHERE [cid]=" & Me.txtbarcode.Tag

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by christing Friday, July 12, 2019 2:56 AM
    Thursday, July 11, 2019 9:18 AM
    Moderator
  • @alex i receive No this error message value given for one or more required parameters. did i need add value for this stock.

    i need add this command 

      cmd.CommandText = "INSERT INTO stock([checkercid],[CheckerName],[ShipQuantity],[Date],[CompanyName],[CheckerDetail]) " &
        "VALUES('" & getLastNumber().ToString & "', '" & Me.txtCN.Text & "', '" & Me.txtQty.Text & "', '" & Date.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', '" & Me.txtCompanyN.Text & "', '" & Me.txtDetail.Text & "')"
                cmd.ExecuteNonQuery()


    Thursday, July 11, 2019 9:37 AM
  • @ Alex

    i solve the problem already thanks for your helping


    • Edited by christing Friday, July 12, 2019 6:55 AM
    Friday, July 12, 2019 2:56 AM