Database record not getting updated

Question

I'm hoping for someone to be able to offer me some help please? I have spent the last 4 hours trying to fix this problem but not got anywhere. I dont actually have an error code which is making it even more difficult, it just doesnt do anything.

What I am trying to do is Read the value in Textbox2 and minus it from the GamesPlayed field in Table1 where the JobID matches TextBox1.

I dont see what I have done wrong? Many thanks.

 Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\Database1.mdb")
        Dim cmd As New OleDbCommand
        With cmd
            .CommandType = CommandType.Text
            .Connection = conn
            .CommandText = "UPDATE [Table1] SET GamesPlayed = GamesPlayed - " & Val(TextBox2.Text) & " WHERE JobID = TextBox1.text"
            .Parameters.Add("@p1", Me.ComboBox1.SelectedValue)
        End With
        conn.Open()
        cmd.ExecuteNonQuery()

score 2 · Accepted Answer · answered Aug 01 '13 at 17:25

You where off to a good start, but you need to use the parameters you add.

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\Database1.mdb")
    Dim cmd As New OleDbCommand
    With cmd
        .CommandType = CommandType.Text
        .Connection = conn
        .CommandText = "UPDATE [Table1] SET GamesPlayed = @p1 WHERE JobID = @p2"
        .Parameters.Add("@p1", Me.ComboBox1.SelectedValue)
        .Parameters.Add("@p2", Me.Textbox1.Text)
    End With
    conn.Open()
    cmd.ExecuteNonQuery()

I would also recommend naming your controls something useful and not the default designer names.

score 0 · Answer 2 · answered Aug 01 '13 at 17:16

0

Change this:

.CommandText = "UPDATE ...  JobID = TextBox1.text"

to that:

.CommandText = "UPDATE ... JobID = " & TextBox1.text

answered Aug 01 '13 at 17:16

David Sdot

2,343
1
20
26

Can you say SQL injection? – OneFineDay Aug 01 '13 at 17:21
He's using it on a Access database so I don't really worry and yes I can: SQL injection ;) – David Sdot Aug 01 '13 at 17:23
Hi, I tried what you suggested but still not working. Yes sorry I am using Access, also the JobID field is a number. – JackSparrow Aug 01 '13 at 17:26
DonA please see orginal post. – JackSparrow Aug 01 '13 at 17:59
Don, please ignore I have figures it out just had to add the GamesPlayed value in again, many thanks – JackSparrow Aug 01 '13 at 18:05

score 0 · Answer 3 · edited May 23 '17 at 12:20

cmd.ExecuteNonQuery() will not directly return a value. If you would like to get a value, try cmd.ExecuteReader(). Example:

        Dim cmd As New OleDb ("UPDATE [Table1] SETCommandGamesPlayed = GamesPlayed - " & Val(TextBox2.Text) & " WHERE JobID = TextBox1.text")
        Dim yourvalvue As OleDbDataReader = cmd.ExecuteReader()

       Do While (yourvalue.read)
         'what you want to do
       Loop

This question is a good example of what the different execute functions do.

Database record not getting updated

3 Answers3