locked
Load data from a DB Query to a pre-fixed excel sheet. RRS feed

  • Question

  • User69488508 posted

    Hello I have been searching for forums for almost 3 hours and I have found a few threads that are similar but none that address my particular issue.

     

    I am NOT using a Datagrid

    I am NOT using a new/blank worksheet.

     

    I am writing a query that pulls only 1 row of data from a Database.  I need to bind the data to a worksheet that has pre-defined columns.  The file is in a folder on the server.  Each time i need to call the file and append new data to it to and replace the current data.

     

    After this point, I need to attach the worksheet to an email and send.  It's not much but this is what I have so far.

    protected void Generate_excelfile(object sender, EventArgs e)
        {
            try
            {
                arcWebSitePage pageAgent = new arcWebSitePage(Convert.ToInt32(Session["WebSitePageID"]));
                arcWebSites WebSiteAgent = new arcWebSites(pageAgent.web_site_id);
                string usersession = Session.SessionID;
    
                int PageID = Convert.ToInt32(Session["WebSitePageID"]);
    
                string connstring = ConfigurationManager.ConnectionStrings["ARCcon"].ToString();
                SqlConnection sqlcon = new SqlConnection(connstring);
    
                Response.Clear();
                Response.Charset = "";
                Response.ContentType = "application/vnd.ms-excel";
    
                MemoryStream mStream = new MemoryStream();
                StreamWriter sw = new StreamWriter(mStream);
                sw.AutoFlush = true;
    
    
    
                String ReturnValue1 = "";
                string Command1 = "SELECT  user_session, first_Name1, last_Name1, Phone1, Email1, Company1 from tbl_lead_data where user_session = '" + usersession + "' ";
                SqlCommand sqlcom1 = new SqlCommand(Command1, sqlcon);
                sqlcom1.CommandType = CommandType.Text;
                sqlcon.Open();
                ReturnValue1 = sqlcom1.ExecuteScalar().ToString();
                string Subject = ReturnValue1;
            }
    
            catch
            {
            }


     

    Thursday, January 27, 2011 3:58 PM

All replies

  • User1654853431 posted

    Write to your excel sheet as the guy doing in this post:


    http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx


    and then use this guy's code to send the email with attachment


    http://www.aspnettutorials.com/tutorials/email/email-attach-aspnet2-csharp.aspx


    Sunday, January 30, 2011 2:08 PM
  • User69488508 posted

    Hello:

    Thanks for the suggestion.  I am following the steps suggested in that page.  I set up the excel doc as a datasource and created an ODBC connection for it.

    The issue now I am getting is when I go to run an insert statement i get an error saying "Keyword not supported: 'dsn'."  I included the connection string in the webconfig file and tested the connection works fine.

    Here is the code

    string excelconnstring = ConfigurationManager.ConnectionStrings["ExtranetExcelsheet"].ToString();
    SqlConnection excelsqlcon = new SqlConnection(excelconnstring);
    
                String Command11 = "Insert into BulkImport ([First name + Last Name], [Address Line 1],[Address Line 2],[Postal Code],[City],[Country],[Phone],[Email],[Billing Reference (cost center/ Department)])"
                + "VALUES ('" + firstlast +"', '"+ Address1 +"', '" + Address2+"', '" + Zip + "', '"+ City+"','"+Country+"','"+Phone+"','"+Email+"', '"+Department+"')";
                excelsqlcon.Open();
                SqlCommand sqlcom11 = new SqlCommand(Command11, excelsqlcon);
                sqlcom11.CommandType = CommandType.Text;
                sqlcom11.ExecuteNonQuery();


     

    Wednesday, February 2, 2011 9:27 AM
  • User1654853431 posted

    This means that you're having a logic error in your system , this is crystal report for VIEWING data not inserting !


    Wednesday, February 2, 2011 9:32 AM
  • User1654853431 posted

    I hope you didnt understand me wrong but the odbc is for the crystal report not for the excel , use regular connectionstring for the excel , search on google for connection string on excel sheet , the odbc is for crystal .

    Wednesday, February 2, 2011 9:36 AM
  • User69488508 posted

    i went back and re-read the first link and it looks like I need to add the following classes

    • System.Data.Odbc.OdbcFactory
    • System.Data.OleDb.OleDbFactory
    • System.Data.OracleClient.OracleClientFactory
    • System.Data.SqlClient.SqlClientFactory

    But they are not being recognized by intellisync for some reason.

    It is recognizing only System.Data.Odbc ect. the Factory part is not being recognized by intellisync, andy ideas? 

    Wednesday, February 2, 2011 10:53 AM
  • User69488508 posted

    Hi:

    I managed to get the factories to be recognized by intellisync.  The issue I am having now is a rights issue.

    The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data. 

    I have given IUSER,IWAM, ASPNET and Everyone full rights to the file and I am still getting this error.  Here is my code.

     using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = excelconnstring;
    
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "Insert into BulkImport ([First name + Last Name], [Address Line 1],[Address Line 2],[Postal Code],[City],[Country],[Phone],[Email],[Billing Reference (cost center/ Department)])"
                + "VALUES ('" + firstlast + "', '" + Address1 + "', '" + Address2 + "', '" + Zip + "', '" + City + "','" + Country + "','" + Phone + "','" + Email + "', '" + Department + "')";
                
                        connection.Open();
    
                        command.ExecuteNonQuery();
                    }
                }


     

     

    Friday, February 4, 2011 4:56 PM
  • User1654853431 posted

    are you sure your excel isnt opened during that test?


    Saturday, February 5, 2011 5:57 AM
  • User69488508 posted

    It's definately closed.  I found that this may be a bug on microsoft website.  So know I am thinking the best approach is to generate a new excel file each time, i just need to make sure it is generated with the exact header rows for the data.  Looking for an article for this.

    Monday, February 7, 2011 11:33 AM
  • User1654853431 posted

    try using this connection string

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";


    Monday, February 7, 2011 11:42 AM
  • User69488508 posted

    Hi I am getting the following error

    An OLE DB Provider was not specified in the ConnectionString.  An example would be, 'Provider=SQLOLEDB;'.

     

    Here is my connection string

      <add name="Bulk_ImportConnectionString" connectionString="Data Source=C:\Inetpub\wwwroot\testsite\emailAttachments\Bulk_Import.xls;Persist Security Info=True;Mode=ReadWrite;Extended Properties="Excel 12.0 Xml;HDR=YES""
       providerName="Microsoft.ACE.OLEDB.12.0" />


     

     

     

    Monday, February 7, 2011 12:14 PM
  • User69488508 posted

    oops wait just noticed there was an extra quote.  Let me try again. 

     

    same error even after removing extra quote.

    Monday, February 7, 2011 12:16 PM