Not show rows in data grid view where a certain column is blank

[seano]

hi all im trying to make the datagridview only show the rows that have data in a certain column, for example i want to only show the rows that have data in the column "OurPartNo", if the "OurPartNo" column doesnt have data then not to display the row.I hope that makes sense,

thanks.

 

[IanRyder]

Hi,

Can you please explain how you are populating your DataGridView with your data. Is this through code or are you using a DataSet that has been setup within the VS IDE? This will determine the best way to advise you with either filters or Where clauses.

Cheers,

Ian

 

[seano]

Hi,

Can you please explain how you are populating your DataGridView with your data. Is this through code or are you using a DataSet that has been setup within the VS IDE? This will determine the best way to advise you with either filters or Where clauses.

Cheers,

Ian

yep, im using a select query through code, and then putting it into a datatable and then bind it to a data grid.

 

[jmcilhinney]

If you don't need all the data in the first place then add a WHERE clause to your query to exclude the record you don't need, e.g.

SELECT *
FROM Child
WHERE ParentID = 1

That will filter out any record where the ParentID is not equal to 1. If you do need all the data but need to filter it locally, bind your DataTable to a BindingSource and bind that to your grid. You can then set the Filter property of the BindingSource at any time, e.g.

myBindingSource.Filter = "ParentID = 1"

 

[seano]

If you don't need all the data in the first place then add a WHERE clause to your query to exclude the record you don't need, e.g.

SELECT *
FROM Child
WHERE ParentID = 1

That will filter out any record where the ParentID is not equal to 1. If you do need all the data but need to filter it locally, bind your DataTable to a BindingSource and bind that to your grid. You can then set the Filter property of the BindingSource at any time, e.g.

myBindingSource.Filter = "ParentID = 1"

i need to filter out all the records that are blank so myBindingSource.Filter = "ParentID <> '' "?

 

[jmcilhinney]

Just as in SQL code, you use "IS NULL" or "IS NOT NULL", depending on which records you want to include and exclude.

 

[IanRyder]

Hi,

To expand upon jmcilhinney's last comment I would use:-

"WHERE (ParentID IS NOT NULL AND NOT (ParentID = N''))" - notice the single quotation marks without a space within the double quotation marks.

This will ensure that all records are returned that are populated with data. i.e. Not Null Values and Not empty fields.

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

Hi,

To expand upon jmcilhinney's last comment I would use:-

"WHERE (ParentID IS NOT NULL AND NOT (ParentID = N''))" - notice the single quotation marks without a space within the double quotation marks.

This will ensure that all records are returned that are populated with data. i.e. Not Null Values and Not empty fields.

Hope that helps.

Cheers,

Ian

The N would be valid syntax in a query for certain databases but not when setting the Filter of a BindingSource. Also, comparing a column value to an empty string would only be valid for a text column. Finally, I kind of disagree with that suggestion anyway because, for it to be required, you'd have to be allowing dodgy data into the database in the first place. NULLs and empty strings are different things and shouldn't really be treated as equivalent from a data perspective.

 

[IanRyder]

Hi jmcilhinney,

I agree with points one and two but sort of disagree with your final point.

To demonstrate (this is for SQL Server 2008 R2) enter a record in a data table in SQL server which allows null values in a field. Leave this field as a null value when entering the record. You can see that the field is null. Now enter some data in the field and save the record. Now remove the data from the field and save the record again.

As you can see SQL server does not return the field to a null value but retains an empty field. I know you can set the field to NULL again when coding within VS but the comment I made would accommodate this data entry point.

Would you agree with that?

Cheers,

Ian

 

[jmcilhinney]

Hi jmcilhinney,

I agree with points one and two but sort of disagree with your final point.

To demonstrate (this is for SQL Server 2008 R2) enter a record in a data table in SQL server which allows null values in a field. Leave this field as a null value when entering the record. You can see that the field is null. Now enter some data in the field and save the record. Now remove the data from the field and save the record again.

As you can see SQL server does not return the field to a null value but retains an empty field. I know you can set the field to NULL again when coding within VS but the comment I made would accommodate this data entry point.

Would you agree with that?

Cheers,

Ian

No I wouldn't agree. In SQL Server Management Studio, if you leave a field blank then you are explicitly setting that field to an empty string. If you want a field to be NULL then you actually have to type NULL, all in upper case, into the field. You'll notice when you do that that the text becomes italicised, indicating that there is no value and not the text "NULL".

 

[IanRyder]

Ha, ha,

Seems stupid that I did not know that, but Point taken and you learn somehing new every day, even if you have been using something for years.

Cheers,

Ian