Search code examples
sqlvb.netparameterized-query

parameterized query not working in VB

statement = "SELECT OrderID, (SELECT VendName FROM Vendors WHERE Vendors.VendorID = Orders.VendorID) " &
                        ",OrderDt, RcvdDt, OrderTotal " &
                        "FROM Orders " &
                        "WHERE VendName=? " &
                        "ORDER BY OrderDt DESC"

Dim cmd As New OleDbCommand(statement, connection)
cmd.Parameters.AddWithValue("VendName", txtVendorFilter.Text)
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.Default)

I was trying to do this before by simply concatenating the textbox value right into the SQL and I was getting a "No values given for required parameters", and read that that I should use parameterized queries instead. So I tried this and it doesn't give me any errors, but the reader never has anything in it. I've never used parameterized queries before, so I'm a bit lost as to why this isn't working.

Edit: I've changed the above code to account for OLEDB from what I briefly read on how it should work, and it's giving me the "no values given for required parameters" again.

Solution

  • One problem is here:

    "WHERE VendName='@x' " &

    Drop the ' marks - the parameterization will take care of this for you:

    "WHERE VendName= @x " &

    Using the ' in the query means that '@x' is treated as a string type, not a parameter name.

    Additionally, since you are using OleDb, names parameters are not supported. You need to use ? to signify a parameter in the query:

    "WHERE VendName= ? " &