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:
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?
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.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
Member
52 Points
252 Posts
How can I get past Null value in a column?
Jul 18, 2013 06:28 AM|Liquidmetal|LINK
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.
Member
12 Points
51 Posts
Re: How can I get past Null value in a column?
Jul 18, 2013 08:46 AM|Marv99|LINK
Do you mean no rows are returned when the column in the database contains NULL or when the var deteye is null?
Member
52 Points
252 Posts
Re: How can I get past Null value in a column?
Jul 18, 2013 09:16 AM|Liquidmetal|LINK
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?
Member
52 Points
252 Posts
Re: How can I get past Null value in a column?
Jul 18, 2013 10:53 AM|Liquidmetal|LINK
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.
Member
12 Points
51 Posts
Re: How can I get past Null value in a column?
Jul 18, 2013 11:47 AM|Marv99|LINK
That's what I was going to suggest :)
Great news it's fixed!
All-Star
52493 Points
15665 Posts
Re: How can I get past Null value in a column?
Jul 18, 2013 12:03 PM|oned_gk|LINK
Suwandi - Non Graduate Programmer
Member
215 Points
29 Posts
Re: How can I get past Null value in a column?
Jul 19, 2013 03:31 AM|Javen Yao - MSFT|LINK
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.
Feedback to us