CODEWITHSUNDEEP
sqlvb.netvisual-studioms-access
Ahmed
Ahmed

Reputation: 41

Cant update MS Access Database from vb.net

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

Upvotes: 1

Views: 668

Answers (1)

HansUp
HansUp

Reputation: 97131

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.

Upvotes: 2

Related Questions