SqlDataSource和存储过程调用问题 [英] SqlDataSource and stored procedure call issue

问题描述

我偶然发现了一个问题，无法独自解决.希望有人可以帮助我解决它.

I've stumbled upon an issue and can't figure it out on my own. Hope someone could help me resolve it.

所以，我在SQL Server 2005数据库中有一个简单的存储过程

So, I have a simple stored procedure in a SQL Server 2005 database

CREATE PROCEDURE spTest
  @pin varchar(128)
AS
BEGIN
  SELECT @Pin as Param
END

以及一个在应用程序(VS2008)中具有 SqlDataSource 和 GridView 控件的asp.net页面

and an asp.net page with a SqlDataSource and a GridView control in an application (VS2008)

<asp:SqlDataSource 
  ID="sds2" 
  runat="server" 
  ConnectionString="..."
  SelectCommand="spTest"      
  SelectCommandType="StoredProcedure"
  >
  <SelectParameters>
    <asp:QueryStringParameter Name="pin" QueryStringField="pin" DbType="String"/>
  </SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gv" runat="server" DataSourceID="sds2"></asp:GridView>

如您所见，代码很简单.不过，如果我不打扰，请在网址上指定图钉( .../Default.aspx 而不是 .../Default.aspx?pin = somevalue )或指定一个空行( .../Default.aspx?pin = )没有对存储过程的任何调用(我使用SQL Server Profiler对其进行了检查).

As you can see, the code is straightforward. Nevertheless, if I don't bother specify the pin on the url (.../Default.aspx instead of .../Default.aspx?pin=somevalue) or specify an empty line (.../Default.aspx?pin=) there is no any call to the stored procedure (I check it with SQL Server Profiler).

此外，如果我将 QueryStringParameter 替换为简单的

Moreover, if I replace the QueryStringParameter with a simple

<asp:Parameter Name="pin" DbType="String" />

并且不指出 DefaultValue 值，这种情况会重复出现，并且不会对存储过程进行任何调用.这种行为的原因是什么?

and do not point out DefaultValue value, the situation repeats and no calls to the stored procedure are made. What is the reason of such a behaviour?

对于asp.net来说我是一个新手，可能会忽略一些东西，但是我什至尝试以编程方式而不是声明方式在代码隐藏文件中执行相同的操作，结果是相同的.我唯一能发现的是，在这种情况下，会触发 SqlDataSource 的 Selecting 事件，但不会触发 Selected .也许会发生某种错误?

I'm quite a new to the asp.net and possibly overlook something, but I even tried to do the same in code-behind file programmatically instead of declaratively and the result is the same. The only thing I could find out is that in such case a Selecting event of the SqlDataSource is fired, but the Selected is not. Maybe some kind of an error happens?

无论如何，我们将不胜感激任何帮助.

Anyway, any kind of help would be greatly appreciated.

推荐答案

SqlDataSource 对象具有名为

The SqlDataSource object has a property called CancelSelectOnNullParameter. Its default value is true, so I think the behavior you're seeing is expected, albeit not obvious. Try setting this property to false.

<asp:SqlDataSource 
  ID="sds2" 
  runat="server" 
  ConnectionString="..."
  SelectCommand="spTest"      
  SelectCommandType="StoredProcedure"
  CancelSelectOnNullParameter="false"
  >

此外，您可能会找到 Parameter 类( QueryStringParameter 对此进行扩展)的noreferrer> ConvertEmptyStringToNull 属性是否有用，取决于您存储的过程是否/如何进行处理 null 值.其默认值也为 true .

Additionally, you may find the ConvertEmptyStringToNull property of the Parameter class (QueryStringParameter extends this) to be of some use, depending on if/how your stored proc handles null values. Its default value is true as well.

这篇关于SqlDataSource和存储过程调用问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！