Updating Data in Database MS Access vb.net

Question

Private Sub Exe1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    txtScore.Enabled = False
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\SJMI.Alfie\Documents\Visual Studio 2010\Projects\WindowsApplication2\WindowsApplication1\Accounts.accdb"
    con.Open()
End Sub
Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click

    myqry = "UPDATE Accounts SET StudNo=?, Exer1=? WHERE Number=?"
    Cmd = New OleDbCommand(myqry, con)
    Cmd.Parameters.AddWithValue("?", txtScore.Text)
    Cmd.Parameters.AddWithValue("?", myID.Text)
    Cmd.ExecuteNonQuery()

    txtScore.Text = score.ToString
    con.Close()
    MsgBox("Thank You!!")
    Login.Show()
    Me.Hide()

End Sub

Nothing happens after I click the submit button.

No errors? Have you tried setting a breakpoint at the line beginning myqry? Also, your statement expects three parameters and you are only supplying two. In addition, you should generally use ADO.NET connections and commands within a Using block. — Zev Spitz
How do you know nothing has happened? Have you placed a breakpoint? — Zev Spitz

HansUp HansUp · Accepted Answer · 2013-12-06T03:55:13

When you're using OleDb with Access, you must supply the parameter values in the order they appear in your SQL statement.

Also your UPDATE includes 3 parameters but your code supplies values for only 2 of them. You need to add the third parameter value, but I don't know where that comes from.

I think you need something close to this, and substitute whatever is appropriate for my [value for Number parameter] placeholder.

myqry = "UPDATE Accounts SET StudNo=?, Exer1=? WHERE [Number]=?"
Cmd = New OleDbCommand(myqry, con)
Cmd.Parameters.AddWithValue("?", myID.Text)
Cmd.Parameters.AddWithValue("?", txtScore.Text)
Cmd.Parameters.AddWithValue("?", [value for Number parameter])
Cmd.ExecuteNonQuery()

Note I enclosed the field name Number in square brackets because it is a reserved word.

Updating Data in Database MS Access vb.net

1 Answers