none
tableadapter will not return null values RRS feed

Answers

  • 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());
                }

     

     

    Friday, July 6, 2007 11:48 PM

All replies

  • I believe there were some fixes to the dataset designer in Visual Studio 2005 service pack 1
    Tuesday, July 3, 2007 10:42 AM
  • I'm running sp1.

     

    Thanks,

     

    Sam

    Tuesday, July 3, 2007 2:05 PM
  • Can anyone tell me if this bug/limitation exists in VS (i.e. not VS Express).

     

    Thanks

    Wednesday, July 4, 2007 5:49 AM
  • This is a problem in all versions off vs it seems, i am trying to figure this out in vs 2005 proffesional
    Wednesday, July 4, 2007 2:46 PM
  • 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

    Wednesday, July 4, 2007 10:21 PM
  • I am not seeing this problem on my machine.   What kind of database are you using? TableAdapters are still around in orcas.
    Thursday, July 5, 2007 11:43 PM
  • 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.

     

    Friday, July 6, 2007 6:28 AM
  • Great.  It's broken in Orcas too. Sad
    Friday, July 6, 2007 9:36 PM
  • 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());
                }

     

     

    Friday, July 6, 2007 11:48 PM
  • >>  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>

     

    • Proposed as answer by superjose128 Tuesday, May 26, 2009 8:07 AM
    Sunday, July 8, 2007 1:23 AM
  • 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.

    ;-)

    • Proposed as answer by swisself Thursday, December 8, 2011 8:45 AM
    Tuesday, May 26, 2009 8:15 AM
  • >>  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.)
    Wednesday, November 13, 2019 3:05 AM