Can't get my null values!

[joe_pool_is]

My TableAdapter links to a table with null values in some of the cells.

 

Whenever I preview the data in the TableAdapter, null value fields are displayed.

 

Whenever I try to populate or do a query using the values, rows with null values never even seem to show up!

 

Has anyone ever encountered something like this? Any idea how to get around it?

[Machaira]

Whenever I try to populate or do a query using the values, rows with null values never even seem to show up!

 

Not sure what you mean by this. Can you show an example?

[joe_pool_is]

Not sure what you mean by this. Can you show an example?

I have a TableAdapter linked to an SQL Server 2000 table. Some of the cells have null string values.

 

In VS2005, I can preview the data linked by the TableAdapter in design mode, and all fields are displayed - even the ones with cells containing null values (they just show "null" in the cell).

 

When I run my application, I create a DataTable and use an SqlDataAdapter to fill the DataTable using the TableAdapter.

 

To search my data, I use the DataTable's Select method; however, any row that contains a null value does not show up in the returned DataRows.

 

Since the TableAdapter sits in the IDE's design mode, so I don't know *how* to show the code.

 

However, I found a solution here: http://tinyurl.com/28u3xg

 

Basically, I edited the SELECT statement that my TableAdapter used. If the row IS NULL, I replace the value with a single space.

 

If anyone knows of any issues I'm likely to run into because of this, please let me know.

[Machaira]

Replacing a null with a space is a hack IMO.

 

What is the search criteria that you're using that isn't returning the expected results? Are you saying you're searching on a column that has a value in the search criteria and it's not being returned because another column in that row is null?

[joe_pool_is]

My TableAdapter is set to return all values:

SELECT [NAME],[NUM],[DATE],...,[etc.]
FROM dbo.EmployeeTable

That's all. Nothing special. All values are strings, which was not my design, but company can't stop production to change all the applications that use it right now.

 

I can go to the TableAdapter in the .xsd file, right click on the "Fill, GetData()" method, select "Preview Data," and the rows where the DATE values are NULL show up right beside everything else on the screen. When the form loads, it Fills the DataGridView, and all of the data shows up - even rows where DATE=NULL.

 

Now comes the Gotcha:

 

I have DataTables defined that map to each of the tables in the DataSet:

DataTable m_empDT = this.DataSet1.EmployeeTable;

To search data in my tables, I fill an array of DataRow values:

DataRow[] Rows = m_empDT.Select("NAME='SMITH'");

There are multiple rows where NAME=SMITH, and some of them contains a DATE=NULL. But, whenever I step through the rows that were returned, the SMITH with DATE=NULL does not show up:

foreach (DataRow row in Rows)
{
 Console.WriteLine(row.ToString());
}

What? You're crazy!

Right?

 

So, yeah, I had to use a hack, but until someone can lead me on how to display this data... Hack, hack, hack!

[PlausiblyDamp]

Just as a quick idea - would it not be easier to use DataViews rather than selecting the actual datarows?

[Machaira]

Is it possible that the null date rows have an extra space or something at the end of the 'Smith'? I can't see how the null value in a field would affect the query.