tableadapter will not return null values

Question

I am trying to change the NullValue property of a field to return a null if the field is null.  Of course, this causes the followng error msg even though the underlying table allows null values:

 

"For columns not defined as System.String, the only valid value is (Throw exception)."

 

I see where others have had the same problem:

 

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=436677&SiteID=1

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=935074&SiteID=1

 

The preceeding threads are marked as "Answered" however the only answer I see is the acknowlgement of a bug.  Has this been fixed? Where is the documentation?

 

Thanks

Answer 1

I believe there were some fixes to the dataset designer in Visual Studio 2005 service pack 1

Answer 2

I'm running sp1.

 

Thanks,

 

Sam

Answer 3

Can anyone tell me if this bug/limitation exists in VS (i.e. not VS Express).

 

Thanks

Answer 4

This is a problem in all versions off vs it seems, i am trying to figure this out in vs 2005 proffesional

Answer 5

Actually I'm a little concerned by the fact that this does not work and nobody knows or cares.  One would think a glitch on a low level component like this would have been identified and fixed long ago.  I am still fairly new to ADO and I want to learn to use the tools provided by VS to build things as quickly and efficiently as possbile.  The silence regarding this issue is deafening - it makes me wonder if a) I'm not seeing an obvious fix, or b) no one has embraced this technology.  Is the concept of tableadapters still used in Orcas?  Should I jusk skip this and move forward?

 

Thanks

Answer 6

I am not seeing this problem on my machine.   What kind of database are you using? TableAdapters are still around in orcas.

Answer 7

Database is local SQL Server database.

Are you able to set the NullValue property for a datetime column to something other than throw exception?

I get this error msg: For columns not defined as System.String, the only valid value is (Throw exception).

 

My code is a simple foreach loop:

 

Code Snippet

foreach (EDataSet.JobsRow jobRow in this.eDataSet.Jobs)

{

if (jobRow.RowState == DataRowState.Added)

this.jobsTableAdapter.InsertJob(jobRow.JobName,jobRow.JobType <snip>

 

 

Which calles this code generated by vs

 

Code Snippet

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

public System.DateTime LastRunDate {

get {

try {

return ((global::System.DateTime)(this[this.tableJobs.LastRunDateColumn]));

}

catch (global::System.InvalidCastException e) {

throw new global::System.Data.StrongTypingException("The value for column \'LastRunDate\' in table \'Jobs\' is DBNull.", e);

}

 

 

the above code throws an error when the column is null.

 

Answer 8

Great.  It's broken in Orcas too.

Answer 9

I do get an exception if I try and set the NullValue property for a date time column.  You need to set the columns AllowDBNull property to true.

 

You need to check the IsColumnNameNull to see if the value is null before you try and get the value of datetime column

 

 

Code Snippet

            NorthwindTableAdapters.OrdersTableAdapter ta = new NorthwindTableAdapters.OrdersTableAdapter();
            Northwind.OrdersDataTable dt = new Northwind.OrdersDataTable();
            ta.Fill(dt);

            foreach (Northwind.OrdersRow dr in dt.Rows)
            {
                System.Diagnostics.Debug.WriteLine(dr.IsShippedDateNull() ? "Null" : dr.ShippedDate.ToShortDateString());
            }

 

 

Answer 10

>>  You need to set the columns AllowDBNull property to true.

Already done.

 

Your code works.  Thanks.

 

For those who want to use this with a datetime field here is what you have to do:

 

Code Snippet

Nullable<DateTime> nullDate = null;

foreach (EDataSet.JobsRow jobRow in this.eDataSet.Jobs)

{

if (jobRow.RowState == DataRowState.Added)

this.jobsTableAdapter.InsertJob(jobRow.JobName,jobRow.JobType,

jobRow.IsLastRunDateNull()? nullDate : jobRow.LastRunDate, <snip>

 

Answer 11

Hi Sam2,

you said:  "For columns not defined as System.String, the only valid value is (Throw exception)."

IT'S NOT TRUE.

You can specify a String value for a DataColumn.NullValue of any type (Date, Int, etc.)

But the string you write NullValue needs a format depending on the data type.

The format for each data type are in the XML Schema Specification:

http://www.w3.org/TR/xmlschema-2/#built-in-datatypes

For example, if you have a DataColumn of type Date, put this String in the NullValue at the VB Designer (without quotes!):

1900-01-01T00:00:00

and that will be converte to a valid default Date for null columns instead of throwing an exception.

This is true for each data type defined in de XML Schema Specification.

;-)

Answer 12

>>  You need to set the columns AllowDBNull property to true.

Already done.

 

Your code works.  Thanks.

 

For those who want to use this with a datetime field here is what you have to do:

 

Code Snippet

Nullable<DateTime> nullDate = null;

foreach (EDataSet.JobsRow jobRow in this.eDataSet.Jobs)

{

if (jobRow.RowState == DataRowState.Added)

this.jobsTableAdapter.InsertJob(jobRow.JobName,jobRow.JobType,

jobRow.IsLastRunDateNull()? nullDate : jobRow.LastRunDate, <snip>

 

 When did TableAdapters get an InsertJob() method?  (At least in VS 2017--there is no such animal, of which I am aware.)