parameterized query not working in VB

Question

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.

Oded · Accepted Answer · 2012-04-15 20:34:17Z

3

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= ? " &

edited Apr 15, 2012 at 20:34

answered Apr 15, 2012 at 20:21

Oded

501k102 gold badges900 silver badges1k bronze badges

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

2 Comments

Tomcat Over a year ago

Thanks. I did this, and now it is once again giving me an error on the execute reader saying that no value is given for one or more required parameters...

Oded Over a year ago

@user1335008 - I missed that you are using OleDb. Answer updated.

Collectives™ on Stack Overflow

parameterized query not working in VB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related