Member Avatar for wilsonchama

Hi guys,

I am trying to update a single column in an already existing record with a value in a textbox in Access database using VB.NET 2008 express edition.

This is how it should happen, combo box is drawing product IDs from the table and two textboxes display product description and quantity respectively, based on the product ID selected in the combobox. the product quantity is added to the new quantity representing the number of goods bought for the product whose id is selecetd in the combobox.

When the total quantity is calculated, it is displayed in the textbox called txtTotal.This is working very wel. but the problem is with the Update command. The program should replace the existing value with the value in txtTotal based on the ID selected in the comboc. But when I run it, it is producing an error saying "No value given for one or more required parameters."
The update command looks as follows;

Private Sub UpdateQuantity()

        myConnToAccess.Open()
        Dim cmdUpdate As New OleDb.OleDbCommand
        cmdUpdate.CommandText = "UPDATE tblProducts SET proQuantity='" & txtTotalqty.Text & "' WHERE pID =" & cmbID.Text & ";"


        cmdUpdate.CommandType = CommandType.Text
        cmdUpdate.Connection = myConnToAccess
        cmdUpdate.ExecuteNonQuery()


        myConnToAccess.Close()

    End Sub

ANY HELP WILL BE APPRECIATED.

  • 4 Contributors
  • 4 Replies
  • 1K Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by JerrimePatient
Member Avatar for Reverend Jim

Assuming that proQuantity is numeric, try

cmdUpdate.CommandText = "UPDATE tblProducts SET proQuantity=" & txtTotalqty.Text & " WHERE pID =" & cmbID.Text & ";"
Member Avatar for wilsonchama

I have tried the code you suggested Jim, but there still is no luck.
Any more suggestions?
Thanks in advance.

Member Avatar for Santanu.Das

WHERE pID =" & cmbID.Text & ";"

Does visual basic accept ";" Semicolon delimited code lines?

if proQuantity is numeric then you can use the Val() function to convert string to numeric type.
The SQL Statement should be

cmdUpdate.CommandText = "UPDATE tblProducts SET proQuantity=" & Val(txtTotalqty.Text) & " WHERE pID ='" & cmbID.Text & "'"

Hope it can help you.

Member Avatar for JerrimePatient

change line 5 from cmdUpdate.CommandText = "UPDATE tblProducts SET proQuantity='" & txtTotalqty.Text & "' WHERE pID =" & cmbID.Text & ";"
to cmdUpdate.CommandText = "UPDATE tblProducts SET proQuantity='" & txtTotalqty.Text & "' WHERE pID = '" & cmbID.Text & "'"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.