locked
Can't set a datatable cell to DBNull.Value RRS feed

  • Question

  • User113730038 posted

    if (dtUBRecon.Rows[0]["prepStageID"].ToString() == "")
    {
        dtUBRecon.Rows[0]["prepStageID"] = DBNull.Value;  << -- not working - looking at the datatable after, it is blank, when i try to do a db insert, it is missing the nulls.
    }

    any help is greatly appreciated.

    Tuesday, December 18, 2018 10:34 PM

Answers

  • User113730038 posted

    BulkCopy much better and less code.  Works like a charm.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 19, 2018 8:22 PM

All replies

  • User475983607 posted

    If the table field allows null then simply leave field out of the INSERT SQL script.

    Tuesday, December 18, 2018 11:49 PM
  • User113730038 posted

    easier said than done, i have 5 tables with over 600 columns.  i would have to really do some major overhaul since i have the insert into (all columns)

    Wednesday, December 19, 2018 1:30 PM
  • User113730038 posted

    i am populating the datatable from 1 database and need to feed it to another.  So i need to check for empty and populate the cell with DBNull.Value

    Wednesday, December 19, 2018 1:57 PM
  • User475983607 posted

    i am populating the datatable from 1 database and need to feed it to another.  So i need to check for empty and populate the cell with DBNull.Value

    Keep in mind that your code snippet is missing the INSERT code.  

    The SQL is simply.

    IF OBJECT_ID('tempdb..#Test') IS NOT NULL
    	DROP TABLE #Test
    
    CREATE TABLE #Test ([Name] VARCHAR(10) NULL)
    
    INSERT INTO #Test
    VALUES (''), ('Hello'), (''), ('World')
    
    SELECT CASE WHEN [Name] = '' THEN NULL ELSE [Name] END
    FROM #Test

    Results

    NULL
    Hello
    NULL
    World

    Basically, cast the table field to NULL when fetching the records rather than in ,NET code.

    Wednesday, December 19, 2018 2:35 PM
  • User113730038 posted

    Oh i have an insert, i have to set the cols to dbnull, THEN the insert happens.  Right now i am trying to not have the insert blow up with data like this (, , , ,) and i want (,null,null,null)

    Wednesday, December 19, 2018 4:34 PM
  • User113730038 posted

    Also this is inline sql as this is only to copy records for the developers, also my first post says it fails when trying to do an insert.

    this is how i get the data:

    cmd.CommandText = "SELECT * FROM UBRecon WHERE ubid = " + intSysID;
    dtUBRecon = new DataTable("UBRecon");
    dtUBRecon.Load(cmd.ExecuteReader());

    Wednesday, December 19, 2018 4:35 PM
  • User475983607 posted

    Oh i have an insert, i have to set the cols to dbnull, THEN the insert happens.  Right now i am trying to not have the insert blow up with data like this (, , , ,) and i want (,null,null,null)

    And the source code that does the INSERT?

    Also this is inline sql as this is only to copy records for the developers, also my first post says it fails when trying to do an insert.

    this is how i get the data:

    cmd.CommandText = "SELECT * FROM UBRecon WHERE ubid = " + intSysID;
    dtUBRecon = new DataTable("UBRecon");
    dtUBRecon.Load(cmd.ExecuteReader());

    Replace the "*" with the column names and implement the CASE statement for the affected fields.   Use SSMS to populate the SQL then just copy the text to the editor.  You really should not use "*"

    Another approach is loading the data as is into a temp table in the destination DB.  Then executing a SQL script to move the data to the destination table formatted as you like.

    Wednesday, December 19, 2018 5:14 PM
  • User113730038 posted

    i changed my select to remove the * and add the col names.  Same thing.  I am writing a winforms app so SSMS is not an option.  This is only to copy data for an id then put into another (same table different db).  Again this is inline code in a winforms app.  This setting to DBNull.Value SHOULD work as i have seen it on many posts.

    So if we have a production issue, we can copy that to the dev db and try to figure out what the problem is.  That is what this will be used for.

    trying to accomplish this: https://stackoverflow.com/questions/6357899/assign-null-value-to-the-integer-column-in-the-datatable

    Wednesday, December 19, 2018 5:23 PM
  • User475983607 posted

    skister77

    i changed my select to remove the * and add the col names.  Same thing.

    Did you add CASE to convert the empty string to NULL as openly illustrated in my previous post?

    skister77

    I am writing a winforms app so SSMS is not an option.

    This is an ASP.NET forum for creating web application not Windows Forms.

    SSMS can create the query text by right clicking the table -> Script table as -> select.  Then copy the text rather than writing by hand.  

    IMHO, it seems odd to write an application to perform a task SSMS is designed to do.

    skister77

    This setting to DBNull.Value SHOULD work as i have seen it on many posts.

    Set a break point and verifying the data and logic flow is expected?

    skister77

    So if we have a production issue, we can copy that to the dev db and try to figure out what the problem is.  That is what this will be used for.

    I recommend a backup and restore otherwise you run the risk of missing keys.

    Wednesday, December 19, 2018 5:56 PM
  • User113730038 posted

    this was not an asp.net specific question only a datatable trying to insert a dbnull.  I think i could use case in my select.  A good thought but i think i will be going the route of either BulkCopy or using a datareader.  Thanks for the help.

    Wednesday, December 19, 2018 7:17 PM
  • User113730038 posted

    BulkCopy much better and less code.  Works like a charm.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 19, 2018 8:22 PM