已回答 ExecuteReader无法正常工作

[PDS8475]

我有一个名为Sites的数据库表，其中有SiteName和SiteAddress列
以下代码是按钮单击事件。
发布之前，我有意从连接字符串中删除了数据库密码。
当我单步执行代码时，可以看到连接状态打开。
但是，阅读器保持为空，并且在调用ExecuteReader时，它会跳转到catch块。
catch块给出异常消息"由于未提供一个或多个必需参数的值而失败"

在我看来，该查询是错误的，因此读者无法阅读，但是我看不到它是怎么错的。我尝试将表和列的名称复制并粘贴到查询中，以确保它们正确无误，我也在线检查了其他更新查询，这似乎是正确的。
任何人都可以告诉我发生了什么事，因为我花了两天时间尝试各种不同的方法，但我却无处可寻。

            string sn = SiteName_textBox.Text;
            string sa = SiteAddress_textBox.Text;
             尝试 
            {
               OleDbDataReader read;
               OleDbConnection connection = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|\Admins.mdb; Jet OLEDB:Database Password = ");
               
               string my_query = "UPDATE Sites SET SiteAddress = @sa WHERE SiteName = @sn";
               OleDbCommand comm = new OleDbCommand(my_query, connection);
               connection.Open();
               read = comm.ExecuteReader();
               if (read.HasRows)
               {
                   while (read.Read())
                   {
                      comm.Parameters.AddWithValue("@sn", SiteName_textBox.Text);
                      comm.ExecuteNonQuery();
                   }
               }
               read.Close();
               connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to " + ex.Message);
            }

 

[金西尼]

Your code makes no sense. As you'd expect, the ExecuteReader method is used to read data, which requires a SELECT statement. If that's not what you're doing then don't call that method. If you want execute an UPDATE statement then just call ExecuteNonQuery. There's nothing to read so don;t 尝试 to read anything.

 

[PDS8475]

Your code makes no sense. As you'd expect, the ExecuteReader method is used to read data, which requires a SELECT statement. If that's not what you're doing then don't call that method. If you want execute an UPDATE statement then just call ExecuteNonQuery. There's nothing to read so don;t 尝试 to read anything.

如果我有

            string sn = SiteName_textBox.Text;
            string sa = SiteAddress_textBox.Text;
             尝试 
            {
               OleDbConnection connection = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|\Admins.mdb; Jet OLEDB:Database Password = ");
               string my_query = "UPDATE Sites SET SiteAddress = @sa WHERE SiteName = @sn";
               OleDbCommand comm = new OleDbCommand(my_query, connection);
               connection.Open();
               comm.Parameters.AddWithValue("@sn", SiteName_textBox.Text);
               comm.Parameters.AddWithValue("@sa", SiteAddress_textBox.Text);
               comm.ExecuteNonQuery();
               connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to " + ex.Message);
            }

换句话说，把与读者有关的所有东西都拿出来。我没有出现异常错误，但是表记录仍然没有得到更新。

 

[跳伞]

从OleDbParameter 文件资料 说：

OLE DB.NET Framework数据提供程序使用带有问号（？）而不是命名参数的位置参数。

Ironically, the sample code in the 文件资料 uses @name rather than the positional ?.

 

[跳伞]

从一个 2008年博客文章，显然顺序很重要。尝试交换第9行和第10行，以便按照与查询中相同的顺序添加参数。

 

[PDS8475]

谢谢你们俩。我没有意识到参数的顺序很重要。

 

[跳伞]

对于大多数理智的数据库，顺序不重要。不幸的是，Microsoft必须保持Access的生命力，并且在Access的年代以及AccessB的OLEDB适配器的种类繁多的情况下，他们尽其所能。 OLEDB失去了ODBC与OLEDB之战。

如果您确实不需要Access数据库，请考虑使用SQLite或SQL Express。

 

[金西尼]

By the way, you should be closing your connection in a finally block rather than in the 尝试 block. As it is, your connection will remain open if an exception is thrown. Better still, create it with a 使用 statement and then it is guaranteed to be closed at the end of the block, even if an exception is thrown and not caught.

 

[羊皮]

只是为了好玩，我想提出一个兴趣点，并就以下方面进行健康的讨论： 尝试/捕获+使用正确的语法 希望这些评论也将有助于我们的运营商确定最佳做法。我知道我们每个人都有自己的喜好，所以我想知道您对语法用法应该怎么看。为了方便起见，我将该问题带入主题，并添加finally语句以确保完整性，因为原始问题中未使用该语句。 ：
哪一个

             尝试 
            {
                 使用  (var myObject = new MyClass())
                {
                    // something here...
                }
            }
            catch (Exception ex)
            {
                // Handle exception

            }
            finally
            {
                // Finish up
            }

要么

             使用  (var myObject = new MyClass())
            {
                 尝试 
                {
                    // something here...
                }
                catch (Exception ex)
                {
                    // Handle exception
                }
                finally
                {
                    //Finish up
                }
            }

请注意，原始主题中最终没有提及，因此我在此处添加了它们。应该 使用 来之前 尝试 ，还是之后？我大部分时间都遵循第一个原理块，因为这对我很有帮助，但是我可以看到在该主题上提出的两个论点背后的原因。你怎么认为？钟声不要害羞

 

[跳伞]

我倾向于使用前者来编写代码，这是我C ++时代的遗留物。

在C＃7.0之前，我会这样写：

public IEnumerable<Record> DoSomething(int someParam, string otherParam)
{
     尝试 
    {
        return DoSomethingReally(someParam, otherParam);
    }
    catch (SomeException ex)
    {
        Log.Error(ex);
        // throw or return empty depending on if error is recoverable.
    }
}

IEnumerable<Record> DoSomethingReally(int someParam, string otherParam)
{
     使用  (var someObj = new Some(someParam))
     使用  (var otherObj = new Other(otherParam))
    {
        // do things here
    }
}

在C＃7.0中，我越来越多地使用这种样式：

public IEnumerable<Record> DoSomething(int someParam, string otherParam)
{
     尝试 
    {
        return DoSomethingReally();
    }
    catch (SomeException ex)
    {
        Log.Error(ex);
        // throw or return empty depending on if error is recoverable.
    }

    IEnumerable<Record> DoSomethingReally()
    {
         使用  (var someObj = new Some(someParam))
         使用  (var otherObj = new Other(otherParam))
        {
            // do things here
        }
    }
}

在C＃8.0中很有可能，我什至会过渡到这种样式：

public IEnumerable<Record> DoSomething(int someParam, string otherParam)
{
     尝试 
    {
        return DoSomethingReally();
    }
    catch (SomeException ex)
    {
        Log.Error(ex);
        // throw or return empty depending on if error is recoverable.
    }

    IEnumerable<Record> DoSomethingReally()
    {
         使用  var someObj = new Some(someParam);
         使用  var otherObj = new Other(otherParam);
        // do things here
    }
}

要么 if the DoSomethingReally() is really short, then:

public IEnumerable<Record> DoSomething(int someParam, string otherParam)
{
     尝试 
    {
         使用  var someObj = new Some(someParam);
         使用  var otherObj = new Other(otherParam);
        // do things here
    }
    catch (SomeException ex)
    {
        Log.Error(ex);
        // throw or return empty depending on if error is recoverable.
    }
}

无论如何，关键是我尽力保持所有"do something" code focused on doing the operation along the happy path. Any errors or exception handling is outside of that happy path and is handled elsewhere. I broke out the DoSomethingReally() to 尝试 to reduce the indentation depth, although admittedly the local methods of C＃ 7.0 doesn't really help much in that regard.

 

[金西尼]

I would tend to keep the code inside a 尝试 block to only that that can throw an exception. Unless a constructor can reasonably throw an exception, I would have the 使用 outside the 尝试 .