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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.      

Answer 5

Do you need more than read only access to the database to connect?

Answer 6

 

What type of access does the user need?  Will Read only access connect?

Answer 7

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
[e-mail]

Answer 8

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

 

Answer 9

Thanks a million.......... really thanks a lot for this information...

Answer 10

thank u .
if its working i wil told u
bye

Answer 11

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

Answer 12

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