Select code does not work behind code of aspx page

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Select code does not work behind code of aspx page

Author

Topic

dejanc
Starting Member

8 Posts

Posted - 2012-07-03 : 17:04:46

Hello,
I'm trying to filter gridview with query written behind code of page, which not works. I'm working with parameters I have tested code as query in sql server, and from there works fine. I'm not sure what I'm doing wrong.

What works, what does not.
This Select code works.

Imports System.Data 
Imports System.Data.SqlClient 
Partial Class zest 
    Inherits System.Web.UI.Page 
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 
        SqlDataSource1.SelectCommand = "Select * From test Where FirstName = @FirstName and SecondName = @SecondName" 
        SqlDataSource1.SelectParameters.Add("FirstName", DropDownListFirstName.Text) 
        SqlDataSource1.SelectParameters.Add("SecondName", DropDownListSecondName.Text) 
        SqlDataSource1.DataBind() 
    End Sub 
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load 
        SqlDataSource1.SelectParameters.Clear() 
    End Sub 
End Class

Then I was try to extend sql, and code not works, as query not show any results. Gridview is empty. Test query from sql server return all records, and no idea why not works here.

Imports System.Data 
Imports System.Data.SqlClient 
Partial Class zest 
    Inherits System.Web.UI.Page 
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 
        SqlDataSource1.SelectCommand = "Select * From test Where FirstName = @FirstName and SecondName = @SecondName OR @FirstName IS NULL AND @SecondName IS NULL" 
        SqlDataSource1.SelectParameters.Add("FirstName", DropDownListFirstName.Text) 
        SqlDataSource1.SelectParameters.Add("SecondName", DropDownListSecondName.Text) 
        SqlDataSource1.DataBind() 
    End Sub 
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load 
        SqlDataSource1.SelectParameters.Clear() 
    End Sub 
End Class

Here is also page.aspx.


<form id="form1" runat="server"> 
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager> 
<br /> 
<asp:DropDownList ID="DropDownListFirstName" runat="server"> 
    <asp:ListItem></asp:ListItem> 
    <asp:ListItem>John</asp:ListItem> 
    <asp:ListItem>Paul</asp:ListItem> 
    <asp:ListItem>James</asp:ListItem> 
</asp:DropDownList> 
<br /> 
<asp:DropDownList ID="DropDownListSecondName" runat="server"> 
    <asp:ListItem></asp:ListItem> 
    <asp:ListItem>Brown</asp:ListItem> 
    <asp:ListItem>White</asp:ListItem> 
</asp:DropDownList> 
<br /> 
<asp:DropDownList ID="DropDownListTown" runat="server"> 
    <asp:ListItem></asp:ListItem> 
    <asp:ListItem>New York</asp:ListItem> 
    <asp:ListItem>London</asp:ListItem> 
    <asp:ListItem>Paris</asp:ListItem> 
</asp:DropDownList> 
<br /> 
<asp:Button ID="Button1" runat="server" Text="Button" /> 
<br /> 
<asp:UpdatePanel ID="UpdatePanel1" runat="server"> 
<ContentTemplate> 
 
<div> 
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" 
 EmptyDataText="There are no data records to display."> 
 <Columns> 
 <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
 SortExpression="FirstName" /> 
 <asp:BoundField DataField="SecondName" HeaderText="SecondName" 
 SortExpression="SecondName" /> 
 <asp:BoundField DataField="Town" HeaderText="Town" SortExpression="Town" /> 
 </Columns> 
 </asp:GridView> 
 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:database21ConnectionString %>" 
 SelectCommand="SELECT [FirstName], [SecondName], [Town] FROM [test]"> 
  </asp:SqlDataSource> 
 </div> 
</ContentTemplate> 
</asp:UpdatePanel> 
</form>

Thanks in advance for help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-03 : 17:24:58

If both select queries work correctly when run from SSMS but not from your client application, I don't have any suggestions to offer. But two things to look at:

a)assuming that the first query works correctly from both SSMS and from client application, and second query does not, then may be this is the select query you are looking for?

Select * From test 
Where 
   (FirstName = @FirstName OR @FirstName IS NULL) 
and 
   (SecondName = @SecondName OR @SecondName IS NULL)

Take a look at the operator precedence here: http://msdn.microsoft.com/en-us/library/ms190276.aspx

b) check if the aspx page is actually sending a null, or is it sending an empty string? If it is sending an empty string, change your query to allow for that by using nullif function:


-- change this
@FirstName IS NULL
-- to
NULLIF(@FirstName,'') IS NULL

dejanc
Starting Member

8 Posts

Posted - 2012-07-04 : 07:40:56

Hi,
and thanks for reply, help.

Here is a solution:
SqlDataSource1.SelectCommand = "Select * From test WHERE (FirstName = @FirstName Or NULLIF(@FirstName,'') IS NULL)"
SqlDataSource1.SelectParameters.Add("FirstName", DropDownListFirstName.Text)
SqlDataSource1.CancelSelectOnNullParameter = False
SqlDataSource1.DataBind()

I was need to add CancelSelectOnNullParameter property.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources