CODEWITHSUNDEEP
vb.netvisual-studio-2010vb.net-2010
JackSparrow
JackSparrow

Reputation: 389

Database record not getting updated

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()

Upvotes: 1

Views: 92

Answers (3)

Chase Ernst
Chase Ernst

Reputation: 1155

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.

Upvotes: 0

OneFineDay
OneFineDay

Reputation: 9024

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.

Upvotes: 2

David Sdot
David Sdot

Reputation: 2333

Change this:

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

to that:

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

Upvotes: 0

Related Questions