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. — user20416
– user20416, Commented Dec 6, 2013 at 3:14
How do you know nothing has happened? Have you placed a breakpoint? — user20416
– user20416, Commented Dec 6, 2013 at 7:44

HansUp · Accepted Answer · 2013-12-06 04:17:52Z

2

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.

edited Dec 6, 2013 at 4:17

answered Dec 6, 2013 at 3:55

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

1 Comment

Aouie Over a year ago

still nothing happens after clicking submit button.

Collectives™ on Stack Overflow

Updating Data in Database MS Access vb.net

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related