VB.NET and MySql UPDATE query

Question

I have my codes here without errors (at least not when I Debug it, I use VS 2010), but what I wish to happen is when I click on the add button, the number/s in the textbox(txtQty) would add to the numbers currently saved in the column "Quantity". (ex: txtQty "100" and current on the column is "200", I want to add "100" from the textbox to the column which has "200" and save it as a new number "300". Simple math I know, but the problem is I have yet to know how to use math equations on VB.NET Datagridview or MySql Database This Code executes but when I click Add the numbers in the column return to 0. Any Hint or Tips would be much appreciated, thanks.

        cmd.Connection = conn

        Dim Result = "Quantity" + txtQty.Text

        cmd.CommandText = " UPDATE incomingdeliveries SET Quantity ='" & Result & "';"
        cmd.Parameters.Add(New MySqlParameter("@Quantity", txtQty.Text))

        cmd.ExecuteNonQuery()

        MsgBox("Data Added to the Database")

            Me.IncomingdeliveriesTableAdapter.Dispose()
        Me.IncomingdeliveriesTableAdapter.Fill(Me.IncomingDeliveriesDataSet.incomingdeliveries)

            lblCode.Text = ("Tables Refreshed!")

John Woo · Accepted Answer · 2012-10-30 05:43:57Z

4

your command text must be parameterized also

cmd.CommandText = " UPDATE incomingdeliveries SET Quantity = @iQuantity + Quantity"
cmd.Parameters.Add(New MySqlParameter("@iQuantity", txtQty.Text))

UPDATE 1

Using _conn As New MySqlConnection("connectionStr here")
    Using _comm As New MySqlCommand()
        With _comm
            .Connection = _conn
            .CommandText = "UPDATE incomingdeliveries SET Quantity = @iQuantity + Quantity"
            .CommandType = CommandType.Text
            .Parameters.AddWithValue("@iQuantity", txtQty.Text)
        End With
        Try
            _conn.Open()
            _comm.ExecuteNonQuery()
        Catch ex As MySqlException
            Msgbox(ex.Message.ToString())
        End Try
    End Using
End Using

edited Oct 30, 2012 at 5:43

answered Oct 30, 2012 at 5:21

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

12 Comments

Alex Luthor Over a year ago

ok I changed my query to yours thanks for that, now it's able to add, but the problem now is that the whole column is being changed. (ex: I add 100, all columns now get added with 100), also when I click add again i get this error( Parameter '@iQuantity' has already been defined.)

John Woo Over a year ago

is cmd declared globally? bad. but you can add cmd.Parameters.Clear() before the command text.

nawfal Over a year ago

@AlexLuthor one, you should specify the row to be updated using a where clause in your update query (may be with an id?). Two, you should use var cmd = new () rather than use the same SqlCommand field always. In other words instantiate a new instance everytime, preferably with using clause. I hope John will amend his answer to reflect that.

Alex Luthor Over a year ago

declared globally? hmm if your asking me if I used it in everything yes... I also added the line you gave... and it helped remove the exception. but the other problem remains, would you mind taking a look at it for me if it's ok... I'm really new with VB.NET and MySql and I'm still learning..

Alex Luthor Over a year ago

@nawfal var cmd = new() so in .net it's dim cmd = new mysqlcommand right? hmm and using clause? okay thanks I will read about that... Thanks for your advice..

|

Collectives™ on Stack Overflow

VB.NET and MySql UPDATE query

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related