C＃OledbCommand返回无效数据 [英] C# OledbCommand returning invalid data

问题描述

Fellas，



我被困在一段代码中，现在已经困扰了我很长一段时间了。我真的很想得到你的帮助。



我要做的是连接到Microsoft Access数据库，创建SQL语句，创建OledbCommand，添加OledbParameters并尝试执行查询。但是，查询返回的数据不正确。



首先，这是我的代码：

  string  dbPath =   D：\\reports.mdb; 
 
 List< string> emails =  new 列表< string>（）; 
 emails.Add（  [e-mail]）; 
 emails.Add（  [e-mail]）; 
 
 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（ ）））; 
 
 使用（ 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，  [e-mail]））; 
 
 
  for （ int  i =  0 ; i <  emails.Count; ++ i）
 cmd.Parameters.Add（ new  OleDbParameter（  email + i，电子邮件[i ]））; 
 
  foreach （OleDbParameter p  in  cmd.Parameters）
 Console .WriteLine（p.ParameterName +   = + p.Value）; 
 
  object  o = cmd.ExecuteScalar（）; 
 
 conn.Close（）; 
} 

其次，这是我尝试过的：



我输出了sql语句，这是正确的。我输出了OledbCommand参数，它们也是正确的。



我也尝试过将sql放入Microsoft Access并用实际参数替换？s并成功执行语句。然后我也尝试将参数硬编码到sql而不是通过使用OledbParameters，它也运行良好。



我不想最终得到硬编码我的sql语句，因为我想使用OledbParameters是显而易见的原因。



任何帮助/指针都将不胜感激!!



编辑：我试过的确切使用SqlServer（和SqlConnection等）同样的事情，这非常好。虽然Access仍然没有运气！任何帮助/指针都会非常感激!!

解决方案

使用命名参数 [ ^ ]并使用 OledbParameterCollection.AddWithValue（） [ ^ ]方法。



如需了解更多信息，请参阅：

关于使用代码中的参数的一切 [ ^ ]

ADO.NET：OleDbCommand参数化查询序列规则 [ ^ ]

ADO.NET代码示例 [ ^ ]

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("[e-mail]");
emails.Add("[e-mail]");

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", "[e-mail]"));


	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!!

解决方案

Use named parameters[^] and pass it using OledbParameterCollection.AddWithValue()[^] method.

For further information, please see:
Everything About Using Parameters from Code[^]
ADO.NET : OleDbCommand Parameterized Query Sequence Rule[^]
ADO.NET Code Examples[^]

这篇关于C＃OledbCommand返回无效数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！