none
Problem in Export to Excel from SQL Bulk data through .NET Code RRS feed

  • Question

  • Hi,

    My clients want to see the entire records from my table.

    we have given option to our esteemed client as "Export to Excel" (Button Click Event)

    This option is working when less than 2 Lakhs records.

    If more than 2 Lakhs records then he is getting the error says "Out of memory file stream exception"

    What is error and how do we solve this problem..

    Steps to perform now as following.

    --------------------

    1. Reading the data from Sql table.

    2. store the data in data set.

    3. using file stream )response.header.etc), save into workbook.

    4.download the excel.

    The above steps will work only lsess amount of data, if more than 2 lakhs then this will not working.

    We are working many applications export to excel option. but, those are very limited data.

    thiis is first time we are facing more than 5 lakhs records issues..

    Monday, May 7, 2018 1:19 AM

Answers

  • Hello,

    I have a MSDN code sample for this with source code for both VB.NET and C#.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Gani tpt Friday, May 18, 2018 4:18 AM
    Monday, May 7, 2018 10:38 AM
    Moderator
  • Hi Gani,

    You need to use SqlDataAdapter to fill the table. But it there are 5 lakhs records, then there is performance hit.

    So you can use sqlbulkcopy() under System.Data.SqlClient namespace to populate your sql table from your data source (like excel ,acess etc ).

    https://social.msdn.microsoft.com/Forums/en-US/9e6cce14-0281-4a1d-9209-5d140f75e5c0/bulkcopy-data-from-vbnet-to-excel?forum=adodotnetdataproviders

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, May 18, 2018 4:18 AM
    Tuesday, May 8, 2018 7:00 AM
    Moderator

All replies

  • In one instance you say the error occurs if ther are more than 2 lakhs records. Then you say this is the first time you are facing more than 5 lakhs records issue. Which is it and what is Lakhs?

    Oh, a Lakh is 100,000. Never heard of it before even though it is in English dictionary.

    Anyhow is the issue with more than 200,000 records or 500,000 records?


    La vida loca


    Monday, May 7, 2018 2:59 AM
  • Hello,

    I have a MSDN code sample for this with source code for both VB.NET and C#.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Gani tpt Friday, May 18, 2018 4:18 AM
    Monday, May 7, 2018 10:38 AM
    Moderator
  • more than 500,000 records..

    Note : Indian numbering system equal to one hundred thousand (100,000; scientific notation: 10<sup style="color:#222222;font-family:arial, sans-serif;">5</sup>)..

    Thanks for understanding.

    Monday, May 7, 2018 1:36 PM
  • Hi Gani,

    You need to use SqlDataAdapter to fill the table. But it there are 5 lakhs records, then there is performance hit.

    So you can use sqlbulkcopy() under System.Data.SqlClient namespace to populate your sql table from your data source (like excel ,acess etc ).

    https://social.msdn.microsoft.com/Forums/en-US/9e6cce14-0281-4a1d-9209-5d140f75e5c0/bulkcopy-data-from-vbnet-to-excel?forum=adodotnetdataproviders

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, May 18, 2018 4:18 AM
    Tuesday, May 8, 2018 7:00 AM
    Moderator