locked
Inserting NULL in a TableAdapter RRS feed

  • Question

  • User1764167293 posted

    I have two columns not nullable (A and B) and one column nullable (C).  All the columns are nvarchar.

    I want to insert a record and leave C null.

    How do I do this with:

    MyTableAdapter.Insert ( "A", "B",  X) where X is the syntax that sets column C to NULL.

    Thanking you in anticipation.

    Roger

     

    Tuesday, March 31, 2009 12:01 PM

Answers

  • User1096912014 posted

    Can you post your Insert method definition?

    In C# to make a datatype nullable you add ? at the end, e.g. int? or datetime?

    I'm not sure how to create nullable datatype in VB.NET, need to double check.

    If you want to pass NULL value to database you usually use DbNull.Value as the value. Or particular type null.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2009 2:52 PM

All replies

  • User1611433984 posted

    You can do like this

    If u want the 3 rd column to be null then declare it as null in the table and while inserting specify the column names

     

    insert into tblname(a,b) values('a','b')

     

    For any help reply me

    Thanks & Regards

    Jacob

    If you find my post have helped you out please mark it as answer

    Tuesday, March 31, 2009 12:27 PM
  • User1764167293 posted

    Sure.  I understand the SQL syntax to insert into selected columns.

    But I have a query in a table adapter which has generated its own Insert (and Update and Delete) statements and has its own Insert (and Update and Delete) methods as well.

    Accordingly I can Insert into the table using the table adapter like so:

    MyTableAdapter.Insert(ColA, ColB, ColC)

    But ColC, which is nullable, needs to be set to NULL.  How do set it to NULL using this command?

    Thanking you in anticipation.

    Roger

     

    Friday, April 3, 2009 6:06 AM
  • User1096912014 posted

    Can you post your Insert method definition?

    In C# to make a datatype nullable you add ? at the end, e.g. int? or datetime?

    I'm not sure how to create nullable datatype in VB.NET, need to double check.

    If you want to pass NULL value to database you usually use DbNull.Value as the value. Or particular type null.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 3, 2009 2:52 PM
  • User1057111565 posted

     I too have this same problem, trying to use the insert commands with the TableAdapters.  I want to write a NULL value back to a date field in the SQL table, but i have found no way to do this using insert commands with table adapters.  I did read an article on another support forum, where someone posted findings that this is a known issue with the table adapters and limitations of .net 1 and 2 --  http://www.velocityreviews.com/forums/t366691-null-intfloat-columns-with-tableadapters.html

    I have tried using nullable types in VB, and it simply will not store the null back to the table.  For example, this code will work:

            Dim OrderDate As Nullable(Of SqlTypes.SqlDateTime)
            OrderDate = SqlTypes.SqlDateTime.Null

    ...then you pass OrderDate in your TableAdapter.Insert(OrderDate, ...) command as a variable.  However, when this insert is performed, a minimum value is stored back, rather than a null.  So in the table when this gets written, it shows up as 1/1/1900 rather than null.   I have only experimented with date fields so far.

    For me, minimum values aren't satisfactory, since some fields the users leave dates blank until they have valid info to put in.  If a user is looking at a date field and needs to know if any data has been entered in, seeing an empty date field means no one has entered data for that field yet.  Seeing 1/1/1900 means the user is going to be confused.  I guess i could write some patch code to handle all this, but I would rather it work right to start with.

    i hope this helps.  I have wasted quite a bit of time trying to get this to work, and am reverting back to SQL commands, ditching the table adapters.  

    Friday, April 10, 2009 11:31 AM
  • User227052781 posted

    I have a TableAdapter on a table with a nullable field (integer), so in VB I defined the variable storing the value as Global.System.Nullable(Of Integer), and then when I want a Null value sent to the database, I set the variable to Nothing, then assigning the table column in the TableAdapter to that variable (ie in an Insert statement, or through a DataTable assignment) will set the value in the database table to Null.

    Wednesday, September 29, 2010 2:32 PM
  • User1882753323 posted

    I have solved this problem by creating a custom insert or update query (e.g., "UpdateNULLDate") in the Table Adapter where I have replaced the New Value parameter, e.g. @NewDate, with NULL.  Then in code I find the cases where I need to insert or update to a NULL value into the Date field and use the "UpdateNULLDate" query

    Here is a very simple example:

    Default Update Query generated by TableAdapter:
    UPDATE    MyTable
    SET              NewDate = @NewDate
    WHERE     (ID = @Original_ID)

    Modified Update Query added to Table Adapter ("UpdateNULLDate"):
    UPDATE    MyTable
    SET              NewDate = NULL
    WHERE     (ID = @Original_ID)

    In VB code I use this:
    taMyTable.UpdateNULLDate(OriginalID)

    If you're using a GridView to make the updates, then place the above code in the GridView RowUpdating event handler followed by e.cancel=True to change the default GridView update behavior to what you need to insert or update the NULL date field values.

    Friday, June 10, 2011 10:16 PM
  • User-463963973 posted

    use YourTableAdapter.Insert("A", "B", Nothing)

    Sunday, September 1, 2013 12:20 PM