locked
Insert records into Access (not inserting null values) RRS feed

  • Question

  • User827758090 posted

    Hi Guys

    I have a SQL database. I am getting a datatable from SQL. In SQL Query the table is having null values in the output. But when it is coming to the front end, the null values are replaced by empty values.

    So in the front end in the datatable i am having empty values in some cells.

    I am trying to insert this datatable into access. For this i am using following code...

     

    OleAdpData.InsertCommand.Connection = OleConn;  // OleConn is the OleDbConnection

    OleAdpData.InsertCommand.Connection.Open();

    OleAdpData.Update(dtData); // dtData is the datatable

    OleAdpData.InsertCommand.Connection.Close();

     

    It is inserting the datatable to access database.

    But it is inserting the empty spaces as present in the datatable. I want to insert NULL into the cells in access datatable where the cells are empty.... 

    Where should i add my logic for this... Guys pls help me...

     

     

     

     

    Friday, November 12, 2010 12:02 PM

Answers

  • User827758090 posted

    This is fixed. The access does not show null values explicitly but it contains the null values.

    We can query the access tables with null, it works.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2011 11:05 AM

All replies

  • User-1675817941 posted

    hi,

    try this.

    MySQLStatement = "INSERT INTO MyTable " & _
          "(Column1, Column2) Values('A Value', null)"


    more information here

    http://imar.spaanjaars.com/285/how-do-i-insert-a-null-value-in-an-access-database

    Monday, November 15, 2010 3:06 AM
  • User827758090 posted

    Hi

    The query which you have suggested does not suit my scenario. In my scenario i am directly copying datatable to access.

    Can u suggest me a solution which suits my scenario...

     

    Monday, November 15, 2010 9:54 AM
  • User1716267170 posted

    How did you get data? Can you convert the NULL value to empty string when retrieving? Also, you may consider to convert it back to NULL values. Please show us more details so that we can know the exact problem. Thanks. 

    Wednesday, November 17, 2010 3:40 AM
  • User1867929564 posted

     As far as i know to you cannot enter null it will be always empty string in mdb.
    Though you can try while populating dtData when it is empty convert it to null.

    Friday, November 26, 2010 2:34 AM
  • User827758090 posted

    This is fixed. The access does not show null values explicitly but it contains the null values.

    We can query the access tables with null, it works.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2011 11:05 AM