locked
IS NULL not worked in text filed RRS feed

  • Question

  • User-362523831 posted

    Hello all ,
    I uesd Hans solution in this link for advance search in my select query :

    https://forums.asp.net/t/1977863.aspx?Advance+Search+multiple+table

    I use " IS NULL " for my text filed , but it is not work
    ( Et_Apartments.EstateCode =@EstateCode OR @EstateCode IS NULL)

    as soon as i change my code like below it works :
    ( Et_Apartments.EstateCode like '%' +@EstateCode+ '%' OR @EstateCode IS NULL)

    but I don't want use like '%' because I need to show search result based on what exactlay user entered.

    Sunday, June 14, 2015 7:18 AM

Answers

  • User61956409 posted

    Hi Farzaneh.Talebi,

    Thanks for your post.

    I create the following sample to reproduce your problem, the code works fine on my side, please refer to it.

    Table data:

    Web page:

    <div>
        <asp:TextBox ID="txtname" runat="server"></asp:TextBox><asp:Button ID="btnselect" runat="server" Text="Select" OnClick="btnselect_Click" />
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    

    Code behind :

    protected void btnselect_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("myconnectionstring");
        string sqlstr1 = "select * from ChartInfo where [Name]= @name or @name is null";
    
        SqlCommand comm = new SqlCommand(sqlstr1, conn);
    
        string name = txtname.Text.ToString();
    
        if (name=="")
        {
            comm.Parameters.AddWithValue("@name", DBNull.Value);
        }
        else
        {
            comm.Parameters.AddWithValue("@name", txtname.Text);
        }
    
        SqlDataAdapter adp1 = new SqlDataAdapter(comm);
    
        DataTable dt1 = new DataTable();
        adp1.Fill(dt1);
    
        GridView1.DataSource = dt1;
        GridView1.DataBind();
    } 
    

    Result:

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 15, 2015 3:41 AM

All replies

  • User-1199946673 posted

    Farzaneh.Talebi

    I use " IS NULL " for my text filed , but it is not work
    ( Et_Apartments.EstateCode =@EstateCode OR @EstateCode IS NULL)

    as soon as i change my code like below it works :
    ( Et_Apartments.EstateCode like '%' +@EstateCode+ '%' OR @EstateCode IS NULL)

    but I don't want use like '%' because I need to show search result based on what exactlay user entered.

    If the second query is working, I don't see why the first query would fail if you want the result to be exactly what the user entered?

    Sunday, June 14, 2015 5:15 PM
  • User-362523831 posted

    Thanks for your answer. but as I said first query doesn't work , I don't know why!!!

    I uploaded a sample of my project . could you help me please?

    http://kaminet.ir/download/AtaSite.rar

    Monday, June 15, 2015 2:27 AM
  • User61956409 posted

    Hi Farzaneh.Talebi,

    Thanks for your post.

    I create the following sample to reproduce your problem, the code works fine on my side, please refer to it.

    Table data:

    Web page:

    <div>
        <asp:TextBox ID="txtname" runat="server"></asp:TextBox><asp:Button ID="btnselect" runat="server" Text="Select" OnClick="btnselect_Click" />
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    

    Code behind :

    protected void btnselect_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("myconnectionstring");
        string sqlstr1 = "select * from ChartInfo where [Name]= @name or @name is null";
    
        SqlCommand comm = new SqlCommand(sqlstr1, conn);
    
        string name = txtname.Text.ToString();
    
        if (name=="")
        {
            comm.Parameters.AddWithValue("@name", DBNull.Value);
        }
        else
        {
            comm.Parameters.AddWithValue("@name", txtname.Text);
        }
    
        SqlDataAdapter adp1 = new SqlDataAdapter(comm);
    
        DataTable dt1 = new DataTable();
        adp1.Fill(dt1);
    
        GridView1.DataSource = dt1;
        GridView1.DataBind();
    } 
    

    Result:

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 15, 2015 3:41 AM
  • User-362523831 posted

    Thanks , it is work like a charm!

    But what about YES/NO fields?

    I tried this one :

    Et_Apartments.Parking = @Parking

    but it doesn't work.

    Monday, June 15, 2015 5:27 AM