locked
Export crystal report data to SQL server or Access using ODBC

    Question

  • I have written the code to load my crystal reports and export them into Excel. Due to MS Excel's 64000 record limit, I cannot get all the information exported. So I am thinking of exporting the contents into a SQL server database or an MS Access database using ODBC. I see the ODBC option when exporting from Crystal designer. But don't have any info / code samples in doing this in .NET. Could someone help me in this please? I am using Crystal reports 2008 with VS2005.
    Any help is greatly appreciated
    donsls
    Thursday, May 05, 2011 5:28 PM

Answers

  • Hi Don,

    As Adam suggested on SAP forums you can read the data using RAS's rowsetcontroller. Also, CR will create a new sheet for each block of 64 k rows, this is a limitation of older versions of Excel. Excel 2007 and above as the new XLXS format that doesn't have the limit. As well CR for VS 2010 now has this feature also so you are no longer limited to 64k rows.

    I don't have any code for what you want exactly but here's sample to get the SQL statement. You cna then use the Rows to read the data and build a Dataset and then save as XML or use the DS to export to ODBC......

    Exporting to ODBC with the CR API's doesn't exist, only works in the designer. You can likely do it through Windows API's though. One option is to get the SQL from the above code and create a Data Set and write that to an XML file, CR Designer likely does the same thing:

          System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString);
          System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand();
    
          GroupPath gp = new GroupPath();
          string tmp = String.Empty;
          rptClientDoc.RowsetController.GetSQLStatement(gp, out tmp);
    
          string myCommandText = tmp.ToString();
    
          cmd.CommandText = myCommandText;
          string query = cmd.CommandText;
          System.Data.OleDb.OleDbDataAdapter dAdapter = new System.Data.OleDb.OleDbDataAdapter(query, conn);
          System.Data.DataSet ds = new System.Data.DataSet();
          dAdapter.Fill(ds, "stockCode");
    
          ds.Tables.Add(dt1);
          ds.WriteXml("c:\\sc.xml", XmlWriteMode.WriteSchema);
    

    Once you have it saved as XML then you should be able to use Windows System.Data to connect to ODBC to any database, set up the structure and import the data.

    I'm sure there are samples in this forum on how to.

    The RowSetController will read the data from the reports field used and you can build a Dataset from reading from the report then do the above to get it into Access or MS SQL Server.

    Thank you

    Don


    Don Williams
    Friday, May 06, 2011 2:52 PM