[Mohsin01]

Hi Guys,
I am trying to create a crystal report in c#, sql server. And i have noticed and checked that you can only put connection string of the current pc. what if i change the computer and try to run it on other pc having sql server installed on it. I made my library project and i kept and app.config file in it. so when the application is installed the app.config file is installed along the exe file. so i can change the app.config file according to the name of server of current pc.
I hope you guys now understand what i am trying to say.
I want crystal report to take connection string from app.config file. so,that it can run on other machines as well.
And i also wanna know how i can use sql commands in crystal reports. like i want

"Select EName,BasicPay from Employees Where EmployeeID="+EmployeeID+""

I searched much about it, But couldnt find solution. Please Help
Thanks in Advance

This post has been edited by Mohsin01: 17 January 2013 - 07:04 AM

[AnalyticLunatic]

I'm not sure what you mean by you can only put the connection string of the current PC? You should be able to just use the Server Connection string in your code from any location and it will work with any PC (given security settings).

You'll have to do some tweaking to get exactly what you want, but the base layout of the code looks like this:

// Create the connection string.
using System.Data.SqlClient;

SqlConnection conn = null;
conn = new SqlConnection("Data Source=[SERVER-NAME];Initial Catalog=[DATABASE-NAME];Trusted_Connection=True;Integrated Security=SSPI");

// Open the connection
conn.Open();

// Create the sql command
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = ([YOUR-QUERY]);

// Create SQL Reader to read the results
SqlDataReader myReader = null;
myReader = cmd.ExecuteReader();

From here you have to decide how you wish to store results. Some common ways are DataTables, Lists, or ObservableCollections.

In most cases, this loop will work:

// Read through each result in the SQL Reader
while(myReader.Read())
{
   // THIS IS WHERE YOU ADD TO YOUR DESIRED STORAGE
}

The line while(myReader.Read()) continues to execute until the last record in the reader has been read. At some point I usually like to throw everything returned from Reader into a DataTable since I can use a DataTable as the Source on my Crystal Report.

Hope that helps a bit!

This post has been edited by AnalyticLunatic: 17 January 2013 - 07:41 AM

[Mohsin01]

Yeah i am doing the same. But i have made app.config file. And i want crsytal report to get the connection string from app.config file.
someone said use dataset to give connection to crystal report. I couldnt understand it. Could you Please Explain it.
And also please Explain how to use sql commands in crystal reports.

[AnalyticLunatic]

Then set the Connection String in your app.config file and reference in the appropriate place of your code. You can use a DataSet as source for a Crystal Report, but by going that route your table adapter handles the connection string automatically.

I do not understand what you mean by using sql commands in crystal reports? You cannot directly type (SELECT...) and have it magically appear on your report.

[Mohsin01]

AnalyticLunatic, on 17 January 2013 - 09:18 AM, said:

Then set the Connection String in your app.config file and reference in the appropriate place of your code. You can use a DataSet as source for a Crystal Report, but by going that route your table adapter handles the connection string automatically.

I do not understand what you mean by using sql commands in crystal reports? You cannot directly type (SELECT...) and have it magically appear on your report.

Could you tell me how i set my connection string in dataset??

Lol,i Am not talking about magically.
Suppose i want to get report of a employee of the month and how do i put this sql command in crystal report.
Like in c# winforms we simply say that select column from table where id="TextBox.text" OR a varibale.
But how do we do this in crystal reports.

[AnalyticLunatic]

SUGGESTION: Read this entire post before doing anything

In Visual Studio (I am using VS2010) create a DataSet (Right-Click -> Add -> New Item -> DataSet) in your solution. Double click it and the 'Edit DataSet with Designer' layout will open. Now do (Right-Click -> Add -> TableAdapter).

The first prompt in the TableAdapter Configuration Wizard will prompt you for choosing a Data Connection. From here things can get a little tricky but you should be able to stumble through. Hit 'New Connection' (if your connection isn't automatically listed in the dropdown).

I'm using SQL Server, so I select 'Microsoft SQL Server' as my Data Source and the Data Provider will automatically choose its own selection. Hit continue and you will open the Add Connection Wizard. Here you select your Server Name and whether you use Windows or SQL Server Authentication (My shops prefers Windows).

Once you have everything properly set hit 'Test Connection'. If Successful, hit Ok and you should continue to the Choose a Command Type section of the wizard. Typically I click 'Use SQL Statements' and then 'Next>'. Everything else is pretty self-explanatory.

Once you have everything set up, use the DataSource expert in your Crystal Report to set the TableAdapter as your Source on the Report. From there just drag and drop fields as you wish them to appear on the report and edit the font styling to your hearts content.

That is the simple Wizard route. Another way you can try (I do not vouch for this working as it may be a bit backward) is go into your app.config file and set it up similar to the following:

<?xml version="1.0"?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="[SOLUTION-NAME].Properties.Settings.[NAME-OF-CONNECTION-STRING]" connectionString="Data Source=[SERVER-NAME];Initial Catalog=[DATABASE-NAME];Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup></configuration>

Your app.config file should have this code inserted into it if you use the wizard to create your connection string as listed above. I think (THINK) by just typing the code in your app.config file as I have listed, you will then be able to just select your connection string from the dropdown in the TableAdapter creation wizard and hit next when creating a new tableadapter.

EDIT: Confirmed adding the lines of code to your app.config as listed will allow you to just select the connection string from the dropdown in the tableadapter creation wizard. Can use the New Connection wizard to help you if unsure of some things.

This post has been edited by AnalyticLunatic: 17 January 2013 - 10:08 AM