locked
How can I get past Null value in a column? RRS feed

  • Question

  • User743508062 posted

    I have a column called deteye and contains 2 states for a particular lens either it will contain the values for right and left lens or in more modern sybstrates a null value as this lens puck can be shaped either left or right.

    I need to be able to filter the lens for R or L if it contains that value or nothing if null.

    When I use the following:

    var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 AND deteye LIKE @2 ORDER BY detdia, detbase");

    It return no values at all when there is a null value in deteye.

    Hence I wrote this code:

     WebGrid grid = null; 
    
        var eye2 = db.QueryValue(q2,lenscode);
    
        var detdia = "%" + Request["detdia"] + "%";
        var deteye = "%" + Request["deteye"] + "%";
    
        if (eye2 != null)
        {
            var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 AND deteye LIKE @2 ORDER BY detdia, detbase");
            var data2 = db.Query(query2, lenscode, detdia, deteye);
        var columns = new[]{"lenscode", "detdia", "deteye", "detbase", "[0]", "[0.25]","[0.50]","[0.75]","[1.00]","[1.25]","[1.50]","[1.75]","[2.00]","[2.25]","[2.50]","[2.75]","[3.00]","[3.25]","[3.50]","[3.75]","[4.00]"};
        grid = new WebGrid(data2, ajaxUpdateContainerId : "grid", rowsPerPage:14);
            }
    
        else
    
        {
            var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 ORDER BY detdia, detbase");
            var data2 = db.Query(query2, lenscode, detdia, deteye);
        var columns = new[]{"lenscode", "detdia", "deteye", "detbase", "[0]", "[0.25]","[0.50]","[0.75]","[1.00]","[1.25]","[1.50]","[1.75]","[2.00]","[2.25]","[2.50]","[2.75]","[3.00]","[3.25]","[3.50]","[3.75]","[4.00]"};
        grid = new WebGrid(data2, ajaxUpdateContainerId : "grid", rowsPerPage:14);
        }

    However, this does nothing and if a lens has null for deteye then no data is displayed in the webgrid:

     @grid.GetHtml(
            tableStyle: "table", 
            headerStyle: "header", 
            footerStyle:"footer",
            alternatingRowStyle: "alternate",
            columns: grid.Columns(
                     /*   grid.Column("lenscode", "LCode", style:"twentypx"), */
                        grid.Column("detdia","Dia", style:"tenpx"),
                        grid.Column("deteye","Eye", style:"tenpx"),
                        grid.Column("detbase","Base", style:"twentypx"),
                        grid.Column("0", "0.00", style:"tenpx"),
                        grid.Column("0.25", style:"tenpx"),
                        grid.Column("0.50", style:"tenpx"),
                        grid.Column("0.75", style:"tenpx"),
                        grid.Column("1.00", style:"tenpx"),
                        grid.Column("1.25", style:"tenpx"),
                        grid.Column("1.50", style:"tenpx"),
                        grid.Column("1.75", style:"tenpx"),
                        grid.Column("2.00", style:"tenpx"),
                        grid.Column("2.25", style:"tenpx"),
                        grid.Column("2.50", style:"tenpx"),
                        grid.Column("2.75", style:"tenpx"),
                        grid.Column("3.00", style:"tenpx"),
                        grid.Column("3.25", style:"tenpx"),
                        grid.Column("3.50", style:"tenpx"),
                        grid.Column("3.75", style:"tenpx"),
                        grid.Column("4.00", style:"tenpx")
                        )
                )

    What am I doing wrong and how can I rectify this issue, many thanks.


    Thursday, July 18, 2013 6:28 AM

Answers

  • User743508062 posted

    I think I fixed it!

    Like this:

      var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 AND (deteye LIKE @2 OR deteye IS NULL) ORDER BY detdia, detbase");

    The webgrid is poulating correctly with the required rows.

    Thanks everyone for the help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2013 10:53 AM

All replies

  • User1546138644 posted

    Do you mean no rows are returned when the column in the database contains NULL or when the var deteye is null?

     

    Thursday, July 18, 2013 8:46 AM
  • User743508062 posted

    Thanks for the help, when deteye is null for a particular lens then no rows are returned when I use the:

     ... deteye LIKE @2

    as in :

    var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 AND deteye LIKE @2 ORDER BY detdia, detbase");

    however if I remove this code then I get rows being displayed:

    var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 ORDER BY detdia, detbase");

    Hence, I have used an if else to use the appropriate code when deteye is not null

    if (eye2 != null)
        {
            var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 AND deteye LIKE @2 ORDER BY detdia, detbase");
            var data2 = db.Query(query2, lenscode, detdia, deteye);
        var columns = new[]{"lenscode", "detdia", "deteye", "detbase", "[0]", "[0.25]","[0.50]","[0.75]","[1.00]","[1.25]","[1.50]","[1.75]","[2.00]","[2.25]","[2.50]","[2.75]","[3.00]","[3.25]","[3.50]","[3.75]","[4.00]"};
        grid = new WebGrid(data2, ajaxUpdateContainerId : "grid", rowsPerPage:14);
            }
    
        else
    
        {
            var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 ORDER BY detdia, detbase");
            var data2 = db.Query(query2, lenscode, detdia, deteye);
        var columns = new[]{"lenscode", "detdia", "deteye", "detbase", "[0]", "[0.25]","[0.50]","[0.75]","[1.00]","[1.25]","[1.50]","[1.75]","[2.00]","[2.25]","[2.50]","[2.75]","[3.00]","[3.25]","[3.50]","[3.75]","[4.00]"};
        grid = new WebGrid(data2, ajaxUpdateContainerId : "grid", rowsPerPage:14);
        }

    However this is not working and I cannot understand why?


    Thursday, July 18, 2013 9:16 AM
  • User743508062 posted

    I think I fixed it!

    Like this:

      var query2 = ("Select * from dbo.qryRangeXtab Where lenscode=@0 AND detdia LIKE @1 AND (deteye LIKE @2 OR deteye IS NULL) ORDER BY detdia, detbase");

    The webgrid is poulating correctly with the required rows.

    Thanks everyone for the help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 18, 2013 10:53 AM
  • User1546138644 posted

    That's what I was going to suggest :)

    Great news it's fixed!

     

    Thursday, July 18, 2013 11:47 AM
  • User-1716253493 posted
    Isnull(deteye, '') LIKE @2
    Thursday, July 18, 2013 12:03 PM
  • User38443587 posted

    Hi Liquidmetal, I’m glad to hear that you have solved this issue by yourself, and it is very appreciated to share your solution to us. It will be helpful for others, and welcome to post your question on this forum in your future programming.

    Friday, July 19, 2013 3:31 AM