update quantity column in database

[vbmanDan]

Trying to update the quantity column of a table in a database with the row being selected from a combobox and the quantity being added selected by a textbox.

Try
            connection.Open()

            Dim command As OleDbCommand = New OleDbCommand("UPDATE Raw_Materials SET Quantity=+('" & Int.parse(TextBox1.Text) & "') 
            WHERE Part number=('" & ComboBox1.SelectedItem & ") ")

            Dim sdr As OleDbDataReader

            sdr = command.ExecuteReader()



            sdr.Close()
            connection.Close()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

getting an error message- Error 1 Overload resolution failed because no accessible 'Int' accepts this number of arguments.

other than that i'm not sure this is the right sql code anyway, new to sql and can't find any similiar tutorials , all the update tutorials are to do with adding a new row to a database.

[gusano79]

Is this a compilation error? It looks more like a VB problem than a SQL problem.

[Consider]

Not sure if this is tied to the error, but you will want to use ExecuteNonQuery() to run your update, not ExecuteReader().

[vbmanDan]

Yeah the current error is a vb error within the SQL command, however even if that errors fixed I'm not sure it's going to work due to the SQL havnt been able to find any examples of updating databases by adding values to a column.
SET quantity+(number). .... Is this the correct way to add values to a row in a database?

[gusano79]

vb error within the SQL command

That doesn't exist.

VB is complaining about VB code. It doesn't parse SQL code--you won't find out if it's correct or not until you get the VB part straightened out so that it compiles and runs. You'll get a runtime exception if the SQL isn't right.

An experiment to try:

Int.parse(TextBox1.Text)

You have the above expression inline as part of the string definition. Try assigning the result of this expression to a variable in its own line, and use the variable instead when you're concatenating strings. See which line the compiler complains about.

But wait:

"UPDATE Raw_Materials SET Quantity=+('" & Int.parse(TextBox1.Text) & "') WHERE Part number=('" & ComboBox1.SelectedItem & ") "

So you're parsing some text into an integer, and then immediately you convert it back to text? Why not use TextBox1.Text directly? If you're using Int.parse to guarantee that it's a number, that's not a best practice. Better: Set up the text box so it only allows numeric input.

SET quantity+(number). .... Is this the correct way to add values to a row in a database?

No.

A standard SQL UPDATE statement would look more like this: UPDATE Raw_Materials SET Quantity = Quantity + ####

If you're able to run queries directly in your database (exactly how will depend on what kind of database it is), you can test your SQL separately first. I think that's a good idea; it's what I do here at work.

I also suggest you consider using a parameterized query. They're more secure and can improve performance.

Some recommended reading, based on the rest of your code:

• Establishing the Connection (ADO.NET)
• Exception Handling Best Practices in .NET