We currently use ADO.Net in a couple of internal products, and one of those products must query a Microsoft Access database with OleDB. The problem we have right now is that one of the query does not produce the same results when executed by OleDbDataAdapter.Fill and when executed directly in Microsoft Access' SQL View.
The query looks like this:
SELECT DISTINCT t1.*
FROM tableOne AS t1
INNER JOIN tableTwo AS t2 ON t2.tableOne_no = t1.tableOne_no
WHERE t1.status = 'A'
AND t2.tableThree_no = @p_tableThree_no
AND t2.status = 1
AND (t2.startDate IS NULL OR (YEAR(t2.startDate) <= @p_year AND MONTH(t2.startDate) <= @p_month))
AND (t2.endDate IS NULL OR (YEAR(t2.endDate) >= @p_year AND MONTH(t2.endDate) >= @p_month))
We query the database with the following code, using Microsoft ACE OLEDB 12 and ADO.Net:
Dim oDataSet As New DataSet()
Using oSqlConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\NetworkDrive\database.mdb;User Id=admin;Password=password;")
Using oSqlCommand As New OleDbCommand(p_sQuery, oSqlConnection)
oSqlCommand.Parameters.AddWithValue("@p_tableThree_no", 1111)
oSqlCommand.Parameters.AddWithValue("@p_year", 2013)
oSqlCommand.Parameters.AddWithValue("@p_month", 7)
oSqlCommand.CommandType = CommandType.Text
Using oDataAdapter As New OleDbDataAdapter()
oDataAdapter.SelectCommand = oSqlCommand
oSqlConnection.Open()
oDataAdapter.Fill(oDataSet)
End Using
End Using
End Using
Somehow, executing the query from OleDB doesn't give the same number of results than executing the same query in Microsoft Access (Access having the correct number of results). When executing the same query with different @p_tableThree_no values, everything seems okay. Is it possible that specific values from some Text field would cause OleDB to ignore rows in that specific case? There is no error message and the code runs successfully, only with wrong results.
Searching for this problem on Google and StackOverflow provided little to no help, as the only solutions I found are for problems with LIKE statements (using % instead of *) and with parameters names conflicts, which are not relevant to my situation.
Am I missing something? Is the query too complex for OleDB? Should I wrap something in parentheses? I have no idea what I'm doing wrong.
Turns out the query was wrong to begin with, and nobody (including myself) pinpointed it at first glance. I was rewriting the query from stratch when I noticed that MONTH(t2.startDate) <= @p_month didn't make any sense if the date is, for example, 2012-11 compared to 2013-07. I still don't know why the query results were "wrongly correct" when executing it directly in Access and providing parameters in popup windows, but that's another mystery I'm not willing to solve. I'm accepting @HansUp 's answer since he did provide me a query that was the same on both sides and he was the one that made me doubt about the SQL itself.
Define the parameters within the SQL statement, then supply the values from your code as before.
Don't fret about the parameter names in Parameters.AddWithValue. OleDb ignores the parameter names ... you could do .AddWithValue("Hello World!", 1111) and it wouldn't change anything. However you must supply the parameters in the order Access expects, and I'm hopeful adding a PARAMETERS clause will avoid confusion there.
PARAMETERS
p_tableThree_no Long,
p_year Long,
p_month Long;
SELECT DISTINCT t1.*
FROM
tableOne AS t1
INNER JOIN
tableTwo AS t2
ON t2.tableOne_no = t1.tableOne_no
WHERE
t1.status = 'A'
AND t2.tableThree_no = p_tableThree_no
AND t2.status = 1
AND (t2.startDate IS NULL
OR (YEAR(t2.startDate) <= p_year AND MONTH(t2.startDate) <= p_month))
AND (t2.endDate IS NULL
OR (YEAR(t2.endDate) >= p_year AND MONTH(t2.endDate) >= p_month))