C# OledbCommand returning invalid data

See more:

C#

SQL

ADO.NET

MS-Access

Fellas,

I got stuck up in a piece of code and it has been troubling me for quite some time now. I would really like your help on this one.

What I'm trying to do is to connect to an Microsoft Access database, create an SQL statement, create an OledbCommand, add the OledbParameters and try to execute the query. However, the query is returning incorrect data.

First off, here's my code:

string dbPath = "D:\\reports.mdb";

List<string> emails = new List<string>();
emails.Add("user1@e2k10.lcl");
emails.Add("aliasuser1@e2k10.lcl");

StringBuilder sbContactsLastComm = new StringBuilder();
sbContactsLastComm.Append("SELECT ");
sbContactsLastComm.Append("MAX([meces_processed].[datetime]) ");

sbContactsLastComm.Append("FROM ");
sbContactsLastComm.Append("[meces_processed], ");
sbContactsLastComm.Append("[meces_users], ");
sbContactsLastComm.Append("[meces_emailusers] ");

sbContactsLastComm.Append("WHERE ");
sbContactsLastComm.Append("[meces_emailusers].[userid] = [meces_users].[id] ");
sbContactsLastComm.Append("AND [meces_emailusers].[emailid] = [meces_processed].[uniqueguid] ");
sbContactsLastComm.Append("AND [meces_users].[emailaddress] = ? ");
sbContactsLastComm.Append("AND [meces_processed].[uniqueguid] IN ");
sbContactsLastComm.Append("( ");

sbContactsLastComm.Append("SELECT DISTINCT ");
sbContactsLastComm.Append("[meces_processed].[uniqueguid] ");

sbContactsLastComm.Append("FROM ");
sbContactsLastComm.Append("[meces_emailusers], ");
sbContactsLastComm.Append("[meces_users], ");
sbContactsLastComm.Append("[meces_processed] ");

sbContactsLastComm.Append("WHERE ");
sbContactsLastComm.Append("[meces_emailusers].[userid] = [meces_users].[id] ");
sbContactsLastComm.Append("AND [meces_emailusers].[emailid] = [meces_processed].[uniqueguid] ");
sbContactsLastComm.Append("AND [meces_users].[emailaddress] IN ( ");

for (int i = 0; i < emails.Count; ++i)
	sbContactsLastComm.Append("?, ");

sbContactsLastComm.Remove(sbContactsLastComm.Length - 2, 2);
sbContactsLastComm.Append(" ) ) ");

using (var conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath))
{
	conn.Open();

	OleDbCommand cmd = new OleDbCommand(sbContactsLastComm.ToString(), conn);
	cmd.Parameters.Add(new OleDbParameter("contact", "user@domain.com"));


	for (int i = 0; i < emails.Count; ++i)
	    cmd.Parameters.Add(new OleDbParameter("email" + i, emails[i]));

	foreach (OleDbParameter p in cmd.Parameters)
		Console.WriteLine(p.ParameterName + " = " + p.Value);

	object o = cmd.ExecuteScalar();

	conn.Close();
}

Secondly, here's what I've tried:

I outputted the sql statement and it's correct. I outputted the OledbCommand parameters and they're correct too.

What I've also tried is I put the sql into Microsoft Access and replaced the ?s with the actual parameters and the statement executed successfully. Then I also tried hardcoding the paramters into the sql instead of through the use of OledbParameters and it worked fine as well.

I don't want to end up having the hardcode my sql statements as I would like to use OledbParameters for obvious reasons.

Any help / pointers would be greatly appreciated!!

Edit: I've tried the exact same thing using SqlServer (and SqlConnection etc.) and this worked perfectly fine. Still no luck with Access though! Any help / pointer would be really appreciated!!