当使用存储在SqlDataSource的过程不显示gridview的 [英] Gridview is not displayed when using stored procedure in SQLDatasource
问题描述
我碰到这个才迷迷糊糊，我知道这是一个常见的问题。一个相关的问题是的GridView是空的。但它并没有解决我的问题。
在理想情况下我要的是更新取决于一个文本框中的值我的GridView当我点击查找按钮。它为我在我的其他网页的工作。但是，我在这里有一些问题。我并没有改变任何默认参数。这里是GridView控件
＆LT; ASP：GridView控件ID =GridView1=服务器AllowPaging =真
的DataSourceID =SqlDataSource1的AutoGenerateColumns =FALSE
的DataKeyNames =ID＆GT;
＆LT;柱体和GT;
＆LT; ASP：BoundField的数据字段=ID的HeaderText =IDInsertVisible =FALSE
只读=真SORTEX pression =ID/＆GT;
＆LT; ASP：BoundField的数据字段=公司名称的HeaderText =公司名称
SORTEX pression =公司名称/＆GT;
＆LT; ASP：BoundField的数据字段=contactfirstname的HeaderText =contactfirstname
SORTEX pression =名字/＆GT;
＆LT; ASP：BoundField的数据字段=contactlastname的HeaderText =contactlastname
SORTEX pression =姓氏/＆GT;
＆LT; ASP：BoundField的数据字段=PHONENUMBER的HeaderText =PHONENUMBER
SORTEX pression =PHONENUMBER/＆GT;
＆LT; ASP：BoundField的数据字段=的ContactID的HeaderText =的ContactID
SORTEX pression =contactno/＆GT;
＆LT; /专栏＆GT;
＆LT; / ASP：GridView的＆GT;
在我的查找按钮单击事件。我用这code
保护无效btnFind_Click（对象发件人，EventArgs的发送）
{
SqlDataSource1.DataBind（）; / *编辑：我不需要这个，我意识到* /
}
但它不会加载任何东西。整个gridview的DOS不会出现。如果我使用select语句时，GridView确实出现与结果。我测试了我的StoredProcedure GridView的内部和它的作品的方式应该。不知道究竟可以在这里是什么问题？任何人碰到它？
我觉得我读的地方，如果存储过程返回0的结果，GridView控件将不会显示。
我的SqlDataSource
＆LT; ASP：SqlDataSource的ID =SqlDataSource1=服务器
的ConnectionString =下;％$的ConnectionStrings：MyDBconn％＆gt;中
的SelectCommand =usp_GetContactNoSelectCommandType =StoredProcedure的＆GT;
＆LT; SelectParameters＆GT;
＆LT; ASP：FormParameter默认值=FormField =txtCompanyName
NAME =公司名称类型=字符串/＆GT;
＆LT; ASP：FormParameter FormField =txtFirstNameNAME =名字类型=字符串/＆GT;
＆LT; ASP：FormParameter FormField =txtLastNameNAME =姓氏类型=字符串/＆GT;
＆LT; ASP：FormParameter FormField =txtPhoneNAME =手机类型=字符串/＆GT;
＆LT; ASP：FormParameter FormField =txtContactIDNAME =contactNo类型=字符串/＆GT;
＆LT; / SelectParameters＆GT;
＆LT; / ASP：SqlDataSource的＆GT;
StoredProcedure的是以下。请注意，我可能已经改变了一些名字上面出于安全原因，但下面是原来的领域。
ALTER PROCEDURE [DBO]。[usp_GetContactIDs]
- 添加参数的存储过程在这里
@companyname VARCHAR（255）= NULL，
@firstName VARCHAR（255）= NULL，
@LastName VARCHAR（255）= NULL，
@phone VARCHAR（10）= NULL，
@contactid VARCHAR（15）= NULL
如
开始
- SET NOCOUNT ON加入到prevent额外的结果集，从
- 与SELECT语句的干扰。
SET NOCOUNT ON;
声明@sql VARCHAR（1000）; 设置@sql =选择编号，公司名称，contactfirstname，contactlastname，PHONENUMBER，从使用ContactID WHERE OET ID不为空 如果（@companyname不为null）
设置@sql = @sql +'和公司名称='''+ @companyname +''''; 如果（@firstName不为null）
设置@sql = @sql +和contactfirstname ='''+ @firstName +''''; 如果（@LastName不为null）
设置@sql = @sql +和contactlastname ='''+ @LastName +''''; 如果（@phone不为null）
设置@sql = @sql +'和PhoneNumber ='''+ + @phone''''; 如果（@contactid不为null）
设置@sql = @sql +和使用ContactID ='''+ @contactid +'''';
EXEC（@sql）
结束
我花了一天多时间来解决它。 的问题是母版页。当我删除了该页面的一切母版页的罚款。发生了什么事是txtFirstName控制得在页面上，但它从来没有发现，因为我使用的是母版页。因此，它正经过每一次空值。它没有产生任何错误（这是应该），，使得调试非常棘手。
当时有一堆，你需要看看其他的属性。重要的是
在SQL数据源，数据源configur，参数部分：选择参数并单击显示高级选项，ConvertEmptyStringToNull。您可能需要将其更改为false。
SQLDatasourc - >性质在 - > CancelSelectOnNullParamter =它更改为false
I stumbled across this before and I know it is a common problem. One related question is GridView is empty. But it does not solve my problem.
Ideally what I want is update my gridview depending on the value in a textbox when I click the 'Find' button. It does work for me in my other page. But I have some problem here. I have not changed any default parameters. Here is the gridview
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
DataSourceID="SqlDataSource1" AutoGenerateColumns="False"
DataKeyNames="ID">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="companyname" HeaderText="companyname"
SortExpression="companyname" />
<asp:BoundField DataField="contactfirstname" HeaderText="contactfirstname"
SortExpression="firstname" />
<asp:BoundField DataField="contactlastname" HeaderText="contactlastname"
SortExpression="lastname" />
<asp:BoundField DataField="phonenumber" HeaderText="phonenumber"
SortExpression="phonenumber" />
<asp:BoundField DataField="contactid" HeaderText="contactid"
SortExpression="contactno" />
</Columns>
</asp:GridView>
In my "Find" Button click event. I am using this code
protected void btnFind_Click(object sender, EventArgs e)
{
SqlDataSource1.DataBind(); /* Edit: I don't need this, I realized */
}
But it does not load anything. The whole gridview dos not appear. If I use select statement, the gridview does appear with results. I have tested my storedprocedure inside the gridview and it works the way it should. Don't know what exactly could be the problem here? Anyone came across it?
I think I read somewhere if the stored procedure returns 0 results, gridview will not be displayed.
My SQLDataSource
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDBconn %>"
SelectCommand="usp_GetContactNo" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:FormParameter DefaultValue="" FormField="txtCompanyName"
Name="companyname" Type="String" />
<asp:FormParameter FormField="txtFirstName" Name="firstname" Type="String" />
<asp:FormParameter FormField="txtLastName" Name="lastname" Type="String" />
<asp:FormParameter FormField="txtPhone" Name="phone" Type="String" />
<asp:FormParameter FormField="txtContactID" Name="contactNo" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
StoredProcedure is below. Note I might have changed some names above for security reasons but below is the original fields.
ALTER PROCEDURE [dbo].[usp_GetContactIDs]
-- Add the parameters for the stored procedure here
@companyname varchar(255) = NULL,
@firstname varchar(255) = NULL,
@lastname varchar(255) = NULL,
@phone varchar(10) = NULL,
@contactid varchar(15) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @sql varchar(1000);
set @sql = 'select ID, companyname,contactfirstname,contactlastname,phonenumber, contactid from oet WHERE ID is not null'
if(@companyname is not null)
set @sql = @sql + ' AND companyname = '''+ @companyname + '''';
if(@firstname is not null)
set @sql = @sql + ' AND contactfirstname = '''+ @firstname + '''';
if(@lastname is not null)
set @sql = @sql + ' AND contactlastname = '''+ @lastname + '''';
if(@phone is not null)
set @sql = @sql + ' AND phonenumber = '''+ @phone + '''';
if(@contactid is not null)
set @sql = @sql + ' AND contactid = '''+ @contactid + '''';
exec (@sql)
END
It took me more than a day to solve it. The problem was Master pages. when I removed the master page from the page everything worked fine. What was happening is txtFirstName control was on the page, but it was never found since I was using a master page. As a result it was passing a null value every time. It did not produce any error (which is should), that made debugging very tricky.
There were a bunch of other properties that you need to look at. Important ones are
In SQL DataSource, configur DataSource, Parameter section : select the parameter and click on 'show advance options', ConvertEmptyStringToNull. You may need to change it to false.
SQLDatasourc -> Properites -> CancelSelectOnNullParamter = change it to false
这篇关于当使用存储在SqlDataSource的过程不显示gridview的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
