从OleDbCommand返回值 [英] Return value from OleDbCommand

问题描述

sqlQuery = "SELECT [ID] from [users] WHERE CallerName=@CallerName";

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
cmd = new OleDbCommand(sqlQuery, conn);
cmd.CommandText = sqlQuery;
cmd.Parameters.Add("@CallerName", OleDbType.VarChar).Value = labelProblemDate.Text.Trim();
cmd.Parameters["@CallerName"].Value = name;
cmd.ExecuteNonQuery();          
conn.Close();

有人告诉我这是使用参数从SELECT查询中读取数据的方法，但是它不起作用.我想我做错了.

I was told that this is how to read data from a SELECT query using Parameters but it's not working. I think I did something wrong.

我正在使用WinForms和Microsoft Access 2007

I am using WinForms and Microsoft Access 2007

推荐答案

您的答案似乎很正确，但我想指出示例代码中的一些内容:

It looks like you have your answer, but I wanted to point out a few things from your example code:

sqlQuery = "SELECT [ID] from [users] WHERE CallerName=@CallerName";

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
cmd = new OleDbCommand(sqlQuery, conn);
cmd.CommandText = sqlQuery;
cmd.Parameters.Add("@CallerName", OleDbType.VarChar).Value = labelProblemDate.Text.Trim();
cmd.Parameters["@CallerName"].Value = name;
cmd.ExecuteNonQuery();
conn.Close();

首先，请注意您的SQL查询使用的是Microsoft SQL语法，并且Microsoft Access倾向于使用略有不同的语法.不用将列名括在方括号中，而是使用波浪号:

First, note that your SQL Query is using Microsoft SQL syntax and that Microsoft Access prefers a slightly different syntax. Instead of wrapping your column names in square brackets, use the tilde mark:

sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=@CallerName";

接下来，在您的SQL查询中，请注意Microsoft Access不接受命名参数.您上面使用@CallerName的SQL文本将毫无问题地执行，但是所有OleDb对象将看到的是:

Next, in your SQL Query, be aware that Microsoft Access does not accept named parameters. Your SQL text above using @CallerName will execute with no problem, but all the OleDb object will see is this:

sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=?";

如果稍后决定使用存储过程而不是文本SQL，请记住调用

If, at some point later on, you decide to go with Stored Procedures instead of text SQL, remember to call Prepare() on your OleDbCommand after adding your parameters and before executing the command.

如果有多个参数，请确保按照在SQL文本中调用它们的相同顺序，将这些参数添加到OleDbCommand中. OleDb不在乎您如何命名它们，但您可以自己使用它们来为您提供帮助；它不在查询中使用. @CallerName不会尝试与您的SQL文本中的任何内容匹配.

If you have multiple parameters, ensure that you add these parameters to your OleDbCommand in the same order that you called them in your SQL text. OleDb does not care what you name them, but you can use them for yourself, to aid you; it is NOT used in the query. @CallerName will make no attempt to match up with anything in your SQL text.

接下来，我想看看您对OleDbParameter项目的用法.在下面的两行中，您要在OleDbCommand中添加一(1)个参数，其值为 labelProblemDate.Text.Trim()，然后在下一行中，您重新分配将该参数的值赋给名为name的变量(我们不知道).用一个值声明参数然后将其重新分配给其他值对您不利.

Next, I wanted to look at your usage of the OleDbParameter item. In the two lines below, you are adding one (1) parameter to your OleDbCommand with the value labelProblemDate.Text.Trim() and in the very next line you are re-assigning that same parameter's value to a variable (that is unknown to us) called name. It does no good for you to declare the parameter with one value then re-assign it to something else.

您本可以使用下面的修改后的代码片段并获得相同的结果(请记住添加size字段，如下所示并在您的数据库中指定):

You could have used the modified snippet below and gotten the same results (remember to add the size field, as shown below and specified in your database):

cmd.Parameters.Add("@CallerName", OleDbType.VarChar, 255).Value = labelProblemDate.Text.Trim();
// cmd.Parameters["@CallerName"].Value = name;

类似地，您的OleDbCommand是使用sqlQuery参数创建的，因此无需指定命令的CommandText属性:

Similarly, your OleDbCommand is being created with your sqlQuery parameter, so specifying your command's CommandText property is unnecessary:

cmd = new OleDbCommand(sqlQuery, conn);
//cmd.CommandText = sqlQuery;

最后，正如其他人所说，如果要按照SQL语句的建议查询数据，则必须读取数据而不是调用ExecuteNonQuery()(注意，它称为非查询 ).

Finally, as others have said, if you want to query your data as your SQL statement suggest, you must read the data in as opposed to calling ExecuteNonQuery() (notice it is called Non Query).

总而言之，我在这里写出来:

To sum it up, I have written it out here:

sqlQuery = "SELECT `ID` from `users` WHERE `CallerName`=?";
int result = 0;
OleDbConnection conn = new OleDbConnection(connectionString);
try {
  conn.Open();
  var cmd = new OleDbCommand(sqlQuery, conn);
  //cmd.CommandText = sqlQuery; This command was specified by your initializer
  cmd.Parameters.Add("?", OleDbType.VarChar, 255).Value = labelProblemDate.Text.Trim();
  //cmd.Parameters["@CallerName"].Value = name; Possible bug here
  using (OleDbDataReader reader = cmd.ExecuteReader())
  {
    if(reader.HasRows)
    {
      reader.Read();
      result = reader.GetInt32(0);
    }
  }
} finally {
  conn.Close();
}
return result;

始终将Close放在finally块中，以防您的程序由于任何原因引发错误.这样可以防止您的应用程序崩溃并使文件保持打开状态.我发现，using子句不一定会在连接完成时关闭连接(就像他们应该做的那样).

Always put the Close in a finally block in case your program throws an error for any reason. This prevents your application from crashing and leaving the file open. A using clause, I have found, does not necessarily close a connection when it is done (like they are supposed to do).

我希望这会有所帮助.我现在正在刷新对OleDb的了解，并想指出一些事情.

I hope this helps. I'm refreshing my knowledge of OleDb at the moment, and wanted to point out a few things.

这篇关于从OleDbCommand返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！