locked
How do I connect Crystal Reports to an SQL Express MDF file.

    Question

  • I want to connect to an SQL Express MDF database file. How do I set up the connection? I don't see a way to set up the connection in the Database Expert.

    I tried to create an ODBC (RDO) data source by using the following connect string:

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\dtorg.LUMINET\My Documents\Dev\OMx\Version 1.0\Source\Src\Services\DataManager\OmxDataDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

    I then selected the server .\SQLEXPRESS with a user id and password that I set with sp_addlogin

    I get the error:

    ---------------------------
    Crystal Reports ActiveX Designer
    ---------------------------
    Logon failed.
    Details: Cannot find DSN in connection string
    ---------------------------
    OK  
    ---------------------------


    Dave
    Thursday, November 17, 2005 2:22 PM

Answers

  • I was able to connect to my MDF file by performing the following steps:

    1) Attach the MDF file to SQLExpress

    SQLCMD -S.\SQLEXPRESS -e
    1> sp_attach_db @dbname = 'MyDB', @filename1 = N'C:\Documents and Settings\dtorg\My Documents\MyDB.mdf'
    2>go

    2) Create a FileDSN and select the 'MyDB' database
       Administrative Tools->Data Sources (ODBC)
       Select File DSN
       Create a new file 'MyDB.dsn
       Server: .\SQLExpress
       Default database: 'MyDB'
       Save the file :MyDB.dsn

    3) Create a new report. 
          Select ODBC (RDO)
          Select FindFileDNS: browse to MyDB.dsn 
          Select finish and you can then select your tables.

          Alternate:
          Select OLE DB (ADO)
          Select SQL Native Client
          Select server .\SQLEXPRESS
          Select Integrated security
          Select database 'MyDB'
          Select finish and you can then select your tables.

    I would rather use the following connect string to connect to the database. It eliminates the need to perform the sp_attach_db above. I don't understand it generates an error "Logon failed. Details: Cannot find DSN in string". Am I missing something in the string?

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\dtorg\My Documents\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True     


    Thanks.      




    Friday, November 18, 2005 3:44 PM

All replies

  • You can actually connect to a MDF file using the Data Source Configuration Wizard (Data => Add New Data Source) in VS2005.  Then select Database and then New Connection.  Click the Change button and then Microsoft SQL Database File and then browse to the SQL Server Database File.  This will create a new connection string that you can then use.
    Thursday, November 17, 2005 5:32 PM
    Moderator
  • Yes, I have a database connection, but what is the proper way to connect my report to the database using the connection string? I tried to select ODBC (RDO) and use the connection string (see previous post) but I get the error listed in the previous post. Can you provide me with the steps necessary to use the connection string in a new Crystal reports item?

    Thanks,
    Dave
    Thursday, November 17, 2005 8:45 PM
  • If you created a new data source as above then in the report designer you should now see it listed under Project Data => ADO.NET DataSets.

    For more information on connectivity check out the tutorials at:

    http://support.businessobjects.com/documentation/product_guides/cr_net/vs_2005/html/crtsktutorialsdata.htm

    Thursday, November 17, 2005 8:59 PM
    Moderator
  • I was able to connect to my MDF file by performing the following steps:

    1) Attach the MDF file to SQLExpress

    SQLCMD -S.\SQLEXPRESS -e
    1> sp_attach_db @dbname = 'MyDB', @filename1 = N'C:\Documents and Settings\dtorg\My Documents\MyDB.mdf'
    2>go

    2) Create a FileDSN and select the 'MyDB' database
       Administrative Tools->Data Sources (ODBC)
       Select File DSN
       Create a new file 'MyDB.dsn
       Server: .\SQLExpress
       Default database: 'MyDB'
       Save the file :MyDB.dsn

    3) Create a new report. 
          Select ODBC (RDO)
          Select FindFileDNS: browse to MyDB.dsn 
          Select finish and you can then select your tables.

          Alternate:
          Select OLE DB (ADO)
          Select SQL Native Client
          Select server .\SQLEXPRESS
          Select Integrated security
          Select database 'MyDB'
          Select finish and you can then select your tables.

    I would rather use the following connect string to connect to the database. It eliminates the need to perform the sp_attach_db above. I don't understand it generates an error "Logon failed. Details: Cannot find DSN in string". Am I missing something in the string?

    Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\dtorg\My Documents\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True     


    Thanks.      




    Friday, November 18, 2005 3:44 PM
  • Do you need more than read only access to the database to connect?

    Wednesday, September 12, 2007 7:42 PM
  •  

    What type of access does the user need?  Will Read only access connect?
    Thursday, September 13, 2007 8:58 PM
  • hi my name is mohammed and  iam from eygpt i have the same problem as you if  you know how  to solve  it please send  me the solution and many thanks  to you.
    my email is
    eng_shiplanga@yahoo.com

    Friday, November 30, 2007 4:59 AM
  • hello dtorg1955,

    you forgot to tell us that those commands you have mentioned should be typed on command prompt

     

    I just wanna say thank you verymuch .. You have solved my problem

     

    Tuesday, January 29, 2008 10:39 PM
  • Thanks a million.......... really thanks a lot for this information...
    Wednesday, July 16, 2008 5:54 PM
  • thank u .
    if its working i wil told u
    bye
    Tuesday, August 18, 2009 5:58 AM
  • That was so simple and i spend many hours to know and solution was there,

    if you are working in vs2008 or vs2005 better to be add you mdf file into SQLEXPRESS.

    donwload http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

    and install this, and attach your database with it.

    now this will work as you work with normal SQL Server

    for your forms here is the connection string

    <a

     

     

     

     

     

     

    dd name="DJOBS_MDFConnectionString" connectionString="Data Source=AQEEL-PC;Initial Catalog=DJOBS;User ID=sa" providerName="System.Data.SqlClient" />

    AQEEL-PC is my SQLEXPRESS you can get this name my running managment console of sqlexpress

    FOR CRYSTAL REPORT.

    design your report by using : OLE DB (ADO) and select "microsoft ole db provider for sql server" and type your sqlexpress name and user name and password normaly user name is sa with no password.

    once your report done, now when ever you upload your project on Production server under IIS you must have sqlexpress on your production server as well. Ofcouse when you designed your report have different datasource. here what you have to do in your reporting form.

    .1 drap crystal report component on your web page 

    <
    CR:CrystalReportViewer ID="CRViewer" runat="server" AutoDataBind="true" DisplayGroupTree="False" EnableViewState="true" BackColor="#CCCCCC" />

    .2 add assemblies code behind

    using 
    CrystalDecisions.Shared;
    using CrystalDecisions.CrystalReports.Engine;

    .3 on generate report button use the following code 

     

     -- as in your webconfig file

    <
    appSettings>
       <add key="Database" value="DJOBS" />
       <
    add key="Server" value="AQEEL-PC" />
       <
    add key="username" value="sa" />
       <
    add key="password" value="" />
    </
    appSettings> 

     

     

          -- end webconfig file

    string mUserName = System.Configuration.ConfigurationManager.AppSettings["username"].ToString();string mPassword = System.Configuration.ConfigurationManager.AppSettings["password"].ToString().Trim();string mDbase = System.Configuration.ConfigurationManager.AppSettings["Database"].ToString();string mServer = System.Configuration.ConfigurationManager.AppSettings["Server"].ToString();

     

    ReportDocument CR = new ReportDocument();
    CR.Load(Server.MapPath(
    "~/Reports/rpt_GetEmployeeStatus.rpt"));
    for (int iConnect = 0; iConnect < CR.DataSourceConnections.Count; iConnect++)
    {
        CR.DataSourceConnections[iConnect].SetConnection(mServer, mDbase, mUserName, mPassword);
    }
    CR.SetDatabaseLogon(mUserName, mPassword);
    CR.SetParameterValue(0,
    Utility.ConvertInt32(dpEmployee.SelectedValue.ToString()));
    CRViewer.ReportSource = CR;
    CRViewer.HasCrystalLogo =
    false;
    Session[
    "reportDocument"] = CR;

    Hope some one need this..

    Cheers
    AQEEL AHMED

    Monday, November 02, 2009 11:17 PM
  • I have been looking for the code to use to get this to work for 3 days!  Lots of answers, but none of the right until yours.  Thanks so much for your help!

    Hm Cody
    Helenmary Cody
    Friday, March 12, 2010 10:53 PM