Not all variables bound when passing parameter

Question

I try to pass values to data adapter but I get the error "not all variables bound":

Dim strSql As String = "SELECT * FROM table WHERE A_DT>:a AND B_DT<:b"
Dim conn As New OleDb.OleDbConnection(strConn)
Dim datData As New OleDb.OleDbDataAdapter(strSql, CType(conn, OleDb.OleDbConnection))
Dim dtbdata As New DataTable
datData.SelectCommand.Parameters.Add(":a", OleDb.OleDbType.DBDate).Value = Date.Parse(A_Date)
datData.SelectCommand.Parameters.Add(":b", OleDb.OleDbType.DBDate).Value = Date.Parse(B_Date)
datData.Fill(dtbdata) '<--- Error happen in this line

Answer 1

Note that OleDb does not support named parameters, you will need to use "?" instead of the named parameter then pass the parameters in order, your code should look something like this:

Dim strSql As String = "SELECT * FROM table WHERE A_DT>? AND B_DT<?"
Dim conn As New OleDb.OleDbConnection(strConn)
Dim datData As New OleDb.OleDbDataAdapter(strSql, CType(conn, OleDb.OleDbConnection))
Dim dtbdata As New DataTable
datData.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@a", Date.Parse(A_Date)))
datData.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@b", Date.Parse(B_Date)))
datData.Fill(dtbdata)

If you still insist on using named parameters, I would recommend using SqlClient instead.