Search code examples
sqlvb.netoledbdataadapter

Data adapter troubles

When I run the code the line which filled my datatable says that there is no value given for one or more parameters

Order = New OleDb.OleDbDataAdapter("SELECT * FROM Orders WHERE 
Driver_ID = " & ID, DBREF)
Order.Fill(dataODtable)
DGVorders.DataSource = dataODtable

The code says this:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Below is an image link to the database and table it is referencing. (Database orders table)

enter image description here

If I try run the code without the where statement it runs without crashing.

Solution

  • The field DRIVER_ID is clearly a string, as such you need single quotes around the value you want to use for the WHERE clause.
    But that would be wrong for a long list of reasons (Sql Injection, Parsing errors, automatic type conversion with incompatible locales).

    So you really need to start using parameterized queries as soon as possible to avoid these misteps

    Dim cmdText = "SELECT * FROM Orders WHERE Driver_ID = @drvID"
Order = New OleDb.OleDbDataAdapter(cmdText, DBREF)
Order.SelectCommand.Parameters.Add("@drvID", OleDbType.VarWChar).Value = ID
Order.Fill(dataODtable)
DGVorders.DataSource = dataODtable

    Now the query is no more built concatenating together strings pieces (the main source for sql injection hacks) but you create a parameter object of the correct type and pass it to the database engine that will use it when needed.
    Another benefit is the more clear code you get. In this case perhaps is not very evident but with more complex queries you will have a more clear understanding of what you are asking to do to the database.