Cant update MS Access Database from vb.net

Question

I can add, delete, and search data on my MS access through vb.net application but cant update. It runs and finishes the update query but isn't updating the database, don't get any errors either. I'm new to VB.net and sql and have been following a guide to get what I have so far.

Coding I used for adding new record (Works fine for me):

Private Sub Savebtn_Click(sender As Object, e As EventArgs) Handles Addbtn.Click
        Dim Insertquery As String = "Insert into Risk_Register(ID, Risk_Name, Risk_Description, Owner, Control, Probability, Impact, Risk_Level) values (@ID, @Risk_Name, @Risk_Description, @Owner, @Control, @Probability, @Impact, @Risk_Level)"
        Runquery(Insertquery)
        MsgBox("The record has been added successfully to the database.", 0, "Information")
    End If
End Sub

For Update (Not updating)

Private Sub Updatebtn_Click(sender As Object, e As EventArgs) Handles Updatebtn.Click
    Dim Updatequery As String = "Update Risk_Register Set Risk_Name=@Risk_Name, Risk_Description=@Risk_Description, Owner=@Owner, Control=@Control, Probability=@Probability, Impact=@Impact, Risk_Level=@Risk_Level Where ID=@ID"
    Runquery(Updatequery)
    MsgBox("The record has been updated successfully in the database.", 0, "Information")
End Sub

RunQuery Coding

Public Sub Runquery(ByVal query As String)

    con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\ahmed\OneDrive\Desktop\ProjectDatabase2003.mdb")
    Dim cmd As New OleDbCommand(query, con)
    cmd.Parameters.AddWithValue("@ID", txtRiskid.Text)
    cmd.Parameters.AddWithValue("@Risk_Name", txtRiskname.Text)
    cmd.Parameters.AddWithValue("@Risk_Description", txtRiskdescription.Text)
    cmd.Parameters.AddWithValue("@Owner", txtOwner.Text)
    cmd.Parameters.AddWithValue("@Control", txtControl.Text)
    cmd.Parameters.AddWithValue("@Probability", txtProbability.Text)
    cmd.Parameters.AddWithValue("@Impact", txtImpact.Text)
    cmd.Parameters.AddWithValue("@Risk_Level", txtRisklevel.Text)
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()

End Sub

What debugging have you done? Maybe the contents of txtRiskid.Text doesn't match any rows in the database? — ADyson
– ADyson, Commented Mar 29, 2021 at 14:24
When searching or deleting records I use txtRiskid.Text to complete those functions and it works when I do that but just not when I try to update. — Ahmed
– Ahmed, Commented Mar 29, 2021 at 14:42

HansUp · Accepted Answer · 2021-03-29 14:46:36Z

2

When used with the OleDb provider, Access does not recognize parameters by name. You must supply the parameter values in the order Access expects them.

In your UPDATE, Access expects the @ID value last. But your RunQuery procedure supplies it as the first parameter value.

You can modify the procedure to supply @ID first for an INSERT and last for an UPDATE. Or you can use RunQuery for INSERT and create a separate version for UPDATE.

answered Mar 29, 2021 at 14:46

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmed Over a year ago

Thank you so much, I didn't think the solution was that simple, I was trying to fix it for hours.

Collectives™ on Stack Overflow

Cant update MS Access Database from vb.net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related