C1FlexReport with SQLite

C1FlexReport with SQLite

Posted by: connellyj on 31 January 2018, 3:33 pm EST

• 

  connellyj

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 31 January 2018, 3:33 pm EST

  I ahve installed the System.Data.SQLite. I have created reports and they are viewable on my machine. In deploying to another machine the database is accessible for my vb.net project. But when we try to view a report we get
  
  Am I missing something that the FlexReport needs?
  
  These are the files I am distributing:
  
  

  Do I need to install SQLite on the target machine? and update the machine.config.file? I do not need to do this for any of the other database interactions I have in my application.

• 

  sonu.singh

  

  • Post Options:
  • Link
    Copy

  Posted 1 February 2018, 1:46 am EST

  Hi,
  
  Please try to use below suggestions:
  
  https://www.grapecity.com/en/forums/wpf-edition/flexreportdesigner-with-sq
  
  https://www.grapecity.com/en/blogs/getting-started-with-flexreport-for-uwp

  Hope, it will fix your issue.

  Thanks,
  
  Singh

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 1 February 2018, 9:45 am EST

  That did NOT help.
  
  I have SQLite and FlexReport working on my machine.
  
  I am not using UWP. I am using WinForms.

  I need to know what to distribute. SQLite works fine as a database in my app. It is the FlexReport that cannot open the database on a different machine.

• 

  sonu.singh

  

  • Post Options:
  • Link
    Copy

  Posted 2 February 2018, 4:30 am EST

  Hi,
  
  Please try to use the steps below to resolve the issue:

  1. Uninstall the current version of System.Data.SQLite.
  2. Reboot the system.
  3. Download SQLite from the below link:
     
     https://system.data.sqlite.org/downloads/1.0.107.0/sqlite-netFx40-setup-x64-2010-1.0.107.0.exe
  4. Install it, please note, during installation ensure that “install assemblies into gac” option is checked.
  5. On the 64bit system you have two machine config files:
     
     C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config and C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
     
     find the below-mentioned tag of both the files.

  <system.data>
          <DbProviderFactories>
                   <!--add tag here-->
          </DbProviderFactories>
  </system.data>
  

  and add this option:

  <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite"                type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.107.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
  

  It will enable you to use SQLite in FlexReport.

  Thanks,
  
  Singh

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 27 February 2018, 8:35 am EST

  Do I really need to do this for users of my application? My app is 32-bit and I want to distribute it. The SQLite works fine as a database with my app. It is only FlexReport that is having a problem.

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 28 February 2018, 6:35 am EST

  Hi,
  
  FlexReport uses standard ADO.NET mechanism and requires ADO.NET driver to be installed in the system, driver can be downloaded from:
  
  https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

  Each ADO.NET driver should be registered, it can be registered:
  
  -) in the machine.config file, in this case it will be available for all applications by default
  
  -) in the application config file, like MyApp.exe.config, for example, in this case it will be available only for this application.

  IMO, in your case it will be better to add this key to your app.config file:

  
    <system.data>
      <DbProviderFactories>
        <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>
      </DbProviderFactories>
    </system.data>
  
  

  ADO.NET driver itself is in System.Data.SQLite.dll, so it should be distributed with application.
  
  I have attached sample application it works on my system even if machine.config does not contain entry about SQLite.
  
  WindowsFormsApp1.zip

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 26 March 2018, 7:04 am EST

  I still cannot get Flex Report to work on anyone else’s machine. I now have a new machine (Windows 10, VS2017, SQLite Data Provider x64 1.0.108.0, C1.Win.FlexReport 4.0.20181.296, my app is x64). I put the generic SQLite statement you provided above into the app.config for the application. It is not currently in the machine.config for this machine. I can view the Flex Reports from within Visual Studio as well as running the app from the Bin/Release folder. I copy that Bin/Release folder to another machine and the Flex Reports throw the error that SQLite is not registered. My other uses of SQLite in this app work fine. It is only the Flex Report that is a problem.

  Do I need to reference the specific version of SQLite?
  
  My app is x64. Is this ok?

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 27 March 2018, 4:56 am EST

  Sorry, this is very strange problem, could you please create simple test application with some test SQLite database, zip it, including bin folder and send me? I will try to run it on clear machine.

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 27 March 2018, 11:26 am EST

  The attached project is cut from my overall solution. At this point it opens the viewer for 3 reports but does not present the data and the viewer windows are frozen. I could not include the bin folder it was too big. I am using Windows 10, VS2017, SQLite Data Provider x64 1.0.108.0, C1.Win.FlexReport 4.0.20181.296, my app is x64.

  (TestReports.zip

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 27 March 2018, 12:20 pm EST

  ok, thank you, will look into this

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 27 March 2018, 12:48 pm EST

  As I can see you already installed:
  
  https://system.data.sqlite.org/downloads/1.0.108.0/sqlite-netFx46-setup-bundle-x64-2015-1.0.108.0.exe.
  
  Also as I can see you already added reference to the System.Data.SQLite.dll, BUT please set its “Copy Local” property to True, so when project is build dll will be copied to output folder.
  
  I have attached corrected project.UserSample.zip

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 27 March 2018, 1:54 pm EST - Updated 4 October 2022, 2:53 am EST

  What I sent did have “copy local” set. When I opened your version, I needed to repoint to the SQLite files as well as C1.Win.C1DX.4. Now I get :
  
  

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 27 March 2018, 3:07 pm EST - Updated 4 October 2022, 2:53 am EST

  Please see attached image

  

  961×698 45.2 KB

  CopyLocal not set, this is your original project and when solution is build System.Data.SQLite.dll is not copied to output directory.

  I attached another version of your project it differs from original (your) version only by one string in TestReports.vbproj:
  
  
  
  False
  
  …............\Program Files\System.Data.SQLite\2015\bin\System.Data.SQLite.dll
  
  True
  
  ^^^^ This string is added
  
  
  
  UserSampleOrig.zip

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 28 March 2018, 7:54 am EST - Updated 4 October 2022, 2:53 am EST

  I did/do have them set to copy local. They reset on your machine because you are using a different version of sqlite. Please note I have not trouble accessing the sqlite database for input screens and other processing. It is only the FlexReport that is not accessing the data.
  
  

  

  695×596 83.9 KB

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 29 March 2018, 2:47 am EST - Updated 4 October 2022, 2:53 am EST

  I tried following:

  -) Create clean Win10 VMWare machine
  
  -) Copy TestReports prject including bin folder to created machine
  
  -) Run TestReports.exe from bin folder. Application starts but when I press
  
  “Click to Generate Reports” button error occurs:
  
  Can not load “SQLite.Interop.dll”
  
  -) Download and install
  
  https://system.data.sqlite.org/downloads/1.0.108.0/sqlite-netFx46-setup-bundle-x64-2015-1.0.108.0.exe
  
  -) Now when I run application and press button 3 empty preview windows
  
  are displayed but status bar contains error message:
  
  [ERROR]: unable to open database file
  
  -) Create directory
  
  C:\Users\connellyj\AppData\Local\Temp\PHVAC
  
  and copy hvacOut.phvac to it
  
  -) Now application runs without errors see attached image

  

  1651×821 70.2 KB

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 29 March 2018, 12:45 pm EST

  Thanks you for your patience. I had code that I thought was updating the datasource in the report flxr file. It was not and threw no error.

  I have tried to update the datasource when the report is generated but I must not have that quite right.

  How do you recommend changing the path of a datasource at runtime?

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 3 April 2018, 1:36 am EST

  Sorry for delay with answer.

  How do you recommend changing the path of a datasource at runtime?
  
  All depends on your task, IMO most simple method change DataSource.ConnectionString property after loading a report.

  
  Dim rep As C1FlexReport = New C1FlexReport()
  rep.Load("..\..\PHVACReports.flxr", "Calculation Summary Report")
  rep.DataSource.ConnectionString = "Some New Value"
  
  

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 3 April 2018, 6:11 am EST

  In my app the user can select 20 different reports. First I run it through a counting process to get the total page count. Then I run through the whiole process again to actually print the reports.
  
  I tried the process above which works in the counting phase but then throws and error when rendering the second time.
  
  In my previous version, using C1Reports, I edited the report file when the app was opened so each report did not need to be updated twice for every report request. But the code below did not actually update the .flxr file. Any advice is appreciated.

     xRepoDef = XDocument.Load(My.Application.Info.DirectoryPath + "\PHVACReports.flxr")
      For Each xRepoElm As XElement In xRepoDef...<Report>
          xRepoElm.<DataSource>.<ConnectionString>.Value = "Data Source=" + OutFile
      Next
      xRepoDef.Save(WorkDir + "\PHVACReports.flxr")
  

  This code was in the sample I sent earlier. It did not work for you and you needed to move the database to the location in the flxr file.

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 4 April 2018, 7:27 am EST

  I have fixed my xml update of the flxr file. I needed to add to the element.
  
  I am all set

• 

  connellyj

  • Post Options:
  • Link
    Copy

  Posted 6 April 2018, 11:57 am EST

  Turns out I was not all done. But I have managed to figure the issue with alot of installing/uninstalling…

  I had followed ALL the advise in this thread which put the Sqlite section in the machine.config files AND the project app.config. THAT was my problem.

  So, I reserved the SQLite files before uninstalling both the 32bit and 64bit. I removed the SQLite reference from both machine.config files. I kept the reference in my app.config. I can generate reports and install on another machine with no issue.

  To use the C1FlexReportGenerator I edited C1FlexReportDesigner.4.exe.config and added
  
  <system.data>
  
  
  
  
  
  
  
  </system.data>
  
  Then I copied the 64bit System.Data.SQLite.dll to the apps folder with the C1FlexReportGenerator. Now I can edit my report that uses sqlite.