SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 ASP.NET
 Select code does not work behind code of aspx page
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dejanc
Starting Member

Slovenia
8 Posts

Posted - 07/03/2012 :  17:04:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/03/2012 :  17:24:58  Show Profile  Reply with Quote
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
Go to Top of Page

dejanc
Starting Member

Slovenia
8 Posts

Posted - 07/04/2012 :  07:40:56  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000