in C# OleDbDataAdapter.fill method not giving any data or error

Question

I am using a data adapter to pull data from an access database (see below code). When I run the SQL in the Access database I get the expected data. However when I step through the code the fill method produces only the table definition but no rows.

I have used this procedure many times in the past and it still works for those calls.

Again the SQL in access returns the correct data and in C# I don't get ANY error message but I don't get the data either. Had anyone seen this before?

`
public void GetQueries(ref DataTable tSQL, String tool, string Filter, OleDbConnection lConn) { OleDbDataAdapter dadapt = new OleDbDataAdapter(); //Data Adapter for Access String lSQL = "";

        //assign the connection to the processing mdb
        //lAccProcSQL.Connection = lConn;

        //Pull the queries to be executed
        lSQL = "SELECT * FROM tblSQL WHERE Active = TRUE AND ToolCode = '" +
            tool + "' and type not in (" + Filter + ") ORDER BY QueryNum";

        //Set the adapter to point to the tblSQL table
        dadapt = new OleDbDataAdapter(lSQL, lConn);

        //clear tables in case of rerun
        tSQL.Clear();

        //Fill working queries data table
        dadapt.Fill(tSQL);

    }`

Answer 1

Are you sure that the filter that you've defined in the WHERE clause will evaluate to true on certain rows ?

Why don't you use parameters instead of string concatenation ? Are you sure that Active = True will evaluate to true ? As far as I know, True is represented by -1 in Access.

So, why don't you try it like this:

var command = new OleDbCommand();
command.Connection = lConn;
command.CommandText = "SELECT * FROM tblSql WHERE Active = -1 AND ToolCode = @p_toolCode AND type NOT IN (" + filter + ") ORDER BY querynum";
command.Parameters.Add ("@p_toolCode", OleDbType.String).Value = tool;
datapt = new OleDbDataAdapter();
datapt.SelectCommand = command;
dadapt.Fill (tSql);