Update datagridview and database vb.net

Question

i want to update my datagridview and also my database im new to vb and i dont know what im doing wrong can sombody help me?

Private Sub DataGridView_Booking_Update(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView_Booking.RowLeave
    Try
        con.Open()
        Using cmd As New SqlCommand("UPDATE Tbl_booking SET Omschrijving = @Omschrijving, Jaar = @Jaar, Opmerking = @Opmerking ,Sortnr = @Sortnr)", con)
            cmd.Parameters.Add("@Omschrijving", SqlDbType.VarChar)
            cmd.Parameters.Add("@Jaar", SqlDbType.Int)
            cmd.Parameters.Add("@Opmerking", SqlDbType.VarChar)
            cmd.Parameters.Add("@Sortnr", SqlDbType.Int)
            cmd.ExecuteNonQuery()

        End Using
    Catch ex As Exception
        MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
    Finally
        con.Close()
    End Try

Your forgot to set the actual values for your parameters. E.g. `cmd.Parameters.Add("@Omschrijving", SqlDbType.VarChar).Value = dataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value`. Additionally it would be helpful if you mention any errors you get. — Alex B., Sep 20 '16 at 12:08
I get the error "Error while updating record on table... incorrect syntax near ')'. and if get passed that i get the error "Error while upadting record on table... Failed to convert parameter value from a String to a Int32" — Kevin, Sep 20 '16 at 12:25
Look at the SQL and the error message. https://www.tutorialspoint.com/sql/sql-insert-query.htm — Ňɏssa Pøngjǣrdenlarp, Sep 20 '16 at 14:24

RainerJ · Answer 1 · 2017-09-24T02:08:22.427

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\database1.accdb;Persist Security Info=False;")
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("INSERT INTO table1(name,age,class) VALUES('John Legend','22','B.A Part 1')", con)
        cmd.ExecuteNonQuery()
        MsgBox("Record inserted successfully.")
        con.Close()
    Catch
        MsgBox("Error Occured.")
    End Try
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Try
        Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\database1.accdb;Persist Security Info=False;")
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("update table1 set name='John DOE',age='23',class='12th' where id='1'", con)
        cmd.ExecuteNonQuery()
        MsgBox("Record Updated Successfully.")
        con.Close()
    Catch
        MsgBox("Error Occured.")
    End Try
End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Try
        Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\database1.accdb;Persist Security Info=False;")
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("Delete from table1 where name='John DOE'", con)
        cmd.ExecuteNonQuery()
        MsgBox("Record Deleted Successfully.")
        con.Close()
    Catch
        MsgBox("Error Occured.")
    End Try
End Sub

Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged

    Me.Validate()
    Me.dbDataAdapter.Update(Me.dbdataset.Tables("MyTable"))

    Me.dbDataSet.AcceptChanges()

End Sub

Update datagridview and database vb.net

1 Answers1