none
TableAdapter not accepting dbnull values RRS feed

  • Question

  • Hello there,

    I have a simple forms-based application using a DataSet (xsd file). In there I have a few DataTable instances with its TableAdapter and Select,Insert, Update statements.

    Problem is that when calling the InsertQuery statement the TableAdapter is not accepting null values for a datetime field in the corresponding table.

    Here the SQL insert statement:

    INSERT INTO ICCDT_TM_SCH_DTL
                      (SYSCODE, ITEM_LIST_NO, NO_ENGLISH, NO_FRENCH,        NO_SPANISH, DATE_SENT, DATE_RECD, NO_RECD, STATUS,
           DATE_OUT_OF_CABINET, COMMENT, DATE_FILED)
    VALUES (@SYSCODE,@ITEM_LIST_NO,@no_english,@no_french,@no_spanish, @date_sent, @date_recd, @no_recd, @status,
     @date_out_of_cabinet, @comment, @date_filed)

    And the call to this SQL statement from the VB code:

    Me.GridView_DetailsTableAdapter.InsertQuery(
                    mstrSyscode,
                    CByte(cmbItemListNo.SelectedValue),
                    no_english,
                    no_french,
                    no_spanish,
                    dtpDateSent.Value.ToShortDateString(),
                dtpDateRecd.Value.ToShortDateString(),
                txtNoRecd.Text,
                txtStatus.Text,
                dtpDateOutOfCabinet.Value.ToShortDateString(),
                txtComment.Text,
                dtpDateFiled.Value.ToShortDateString())

    What I'm looking for is: How do I send a null value to any of the date fields?

    I have tried: Nothing, dbnull.value, ctype(nothing,date), " ", etc... all leading to an error.

    Thank you.

    • Moved by Tina-Shi Thursday, July 30, 2015 5:36 AM related to the ado.net
    Wednesday, July 29, 2015 1:25 PM

Answers

  • I’m assuming you are using a typed dataset. Typed data sets don't support nullable types. They support nullable columns. The typed data set generator creates non-nullable properties and related methods for handling null values. If you create a DateSent column of type DateTime and AllowDbNull set to true, the DataRow subclass will implement a non-nullable DateTime property named DateSent, a SetDateSent Null() method, and an IsDateSent  Null() method. You would have to loop through the rows of the data table and do something like this:

    DateTime? dateSent = row.IsDateSentNull() ? null : (DateTime?) row.DateSent;


    william xifaras

    Friday, July 31, 2015 8:08 PM

All replies

  • Hi David,

    Thank you for posting in MSDN forum.

    Since this issue is related to the ADO.NET , so we will move this case to the ADO.NET forum, you will get better support.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 30, 2015 5:35 AM
  • I’m assuming you are using a typed dataset. Typed data sets don't support nullable types. They support nullable columns. The typed data set generator creates non-nullable properties and related methods for handling null values. If you create a DateSent column of type DateTime and AllowDbNull set to true, the DataRow subclass will implement a non-nullable DateTime property named DateSent, a SetDateSent Null() method, and an IsDateSent  Null() method. You would have to loop through the rows of the data table and do something like this:

    DateTime? dateSent = row.IsDateSentNull() ? null : (DateTime?) row.DateSent;


    william xifaras

    Friday, July 31, 2015 8:08 PM
  • I did this to insert value NULL in a ***DateTime column,

    assuming that i have a  nullable DateTime column in Database, i retrieved some data from the database in an object called response and i want to insert nullable DateTime value in the DataSet column that called RenewDate
       
    // create anew row of the same type of your table row
     var rw=ds.StudentActionPrintDT.NewStudentActionPrintDTRow();
    
    // check for null value    
    if(!response.RenewDate.HasValue)    
    {      
       // if null, then the let DataSet to set it null by it's own way
       rw.SetRenewDateNull();
     }    
    else    
    {      
      // if not null set value to the datetime value
      rw.RenewDate = response.RenewDate.Value;    
    }   
     
    // add the created row to the dateset [DataSetName].[ColumnName].Add[ColumnName]Row([The Created Row]); 
    
    ds.StudentActionPrintDT.AddStudentActionPrintDTRow(rw);

    Wednesday, February 28, 2018 1:53 AM