syntax error in update statement on Vb.net

Question

I want to check from the database if a product code exist in database before and if it does it should update d database but it is displaying error in update statement Help, below is my code con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\pharmacy.mdb") con.Open() Dim ct1 As String = "select * from stock where code= '" & TxtCode.Text & "'"

        cmd = New OleDbCommand(ct1)
        cmd.Connection = con
        rdr = cmd.ExecuteReader()

        If rdr.Read Then

            con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\pharmacy.mdb")
            con.Open()

            Dim cb As String = "UPDATE stock SET company = '" & CmbCompany.Text & "', Productname = '" & TxtPrdtName.Text & "',  [quantity]= quantity + '" & TxtQuantity.Text & "', unitPrice = '" & TxtunitPrice.Text & "', ddate = '" & TxtDate.Text & "', batchNo = '" & TxtBatchNo.Text & "', Type = '" & CmbType.Text & "', expDate '" & dtpExpirationDate.Text & "' where code = '" & TxtCode.Text & "'"

            cmd = New OleDbCommand(cb)

            cmd.Connection = con


            cmd.ExecuteReader()
            MessageBox.Show("Successfully updated", "Product Details", MessageBoxButtons.OK, MessageBoxIcon.Information)

            rdr.Close()
            con.Close()

Do yourself a favor and perform a google search for "parameterized query vb.net" — Anthony Pegram, Sep 09 '14 at 14:19

score 0 · Answer 1 · edited May 23 '17 at 11:49

Please use parameters in your command to protect it from malicious data. See the example here.

Most likely, your current problems will vanish, too.

Right now it's impossible to help you because you could get any number of errors depending on your variable user input.

Just image someone would put x'; DROP DATABASE; -- in one of your textboxes.

As a quick fix, remove the single quotes around your user input you are adding to quantity because that's probably a numeric field and will fail if set in quotes. But please do not stop there. Fix your code. That means use parameters in your queries.

syntax error in update statement on Vb.net

1 Answers1