Solved

How to build external report from data table filled via sql select statement

Posted on 2013-01-02
21
326 Views
Last Modified: 2013-01-17
I'm using visual studio 2008 (VB) with crystal reports add in. I'm writing a web site that needs to be able to export data grid or data table to crystal report or any other report. The data table is filled via sql.
0
Comment
Question by:jeremyzit
  • 9
  • 8
  • 3
  • +1
21 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 38737600
0
 
LVL 18

Expert Comment

by:vasto
ID: 38737635
0
 

Author Comment

by:jeremyzit
ID: 38756943
Getting login failed when trying to open the report. It's passing the aspnet user. I've got the user and password hard coded, but will not take it.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38757427
What code are you using?

With the emoreau method, the report doesn't have to log into the database.

mlmcc
0
 

Author Comment

by:jeremyzit
ID: 38759709
I tried both of them and couldn't get either to work so I modify my code I used on a winform and it works fine when I run the code from my dev pc, but when I publish it to live server, it gets below error. Do I need to post another question?

Logon failed.
Details: 28000:[Microsoft][SQL Native Client][SQL Server]Login failed for user 'domainname\servername$'.Error in File C:\Windows\TEMP\RateDatabase {D9C1040B-F28B-4B85-88BD-90C4552EFE38}.rpt:
Unable to connect: incorrect log on parameters.

Source Error:


Line 39:             crtableLogoninfo.ConnectionInfo = crConnectionInfo
Line 40:             CrTable.ApplyLogOnInfo(crtableLogoninfo)
Line 41:             CrTable.Location = crConnectionInfo.DatabaseName & ".dbo." & CrTable.Location.Substring(CrTable.Location.LastIndexOf(".") + 1)
Line 42:         Next

Line 41 is hightlighted red.
0
 
LVL 18

Expert Comment

by:vasto
ID: 38760144
You are using SQL Native Client to connect to the database. Switch to OLE DB.
0
 

Author Comment

by:jeremyzit
ID: 38760581
Can I have an example to swith that to OLE DB?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38761406
Do you have the SQL native client installed on the server?  Generally it is only installed on workstations.

mlmcc
0
 
LVL 18

Expert Comment

by:vasto
ID: 38761470
The easiest way ids to download and run this tool: http://www.r-tag.com/Pages/CRDataSource.aspx

You don't need to buy it.
0
 

Author Comment

by:jeremyzit
ID: 38762959
The type initializer for 'CrystalDecisions.CrystalReports.Engine.ReportDocument' threw an exception. This is the error I get when I hit start on the CRDataSource.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:jeremyzit
ID: 38762972
mlmcc,

I do have the sql native client installed on the web server.
0
 
LVL 18

Expert Comment

by:vasto
ID: 38763089
Looks like you downloaded and installed the 32 bits version on a 64 bits machine. If your computer is 64 bits download and install the 64 bit version from this page : http://www.r-tag.com/Pages/Downloads.aspx
Before to do that uninstall currently installed version.
0
 

Author Comment

by:jeremyzit
ID: 38763229
vasto,

Do I need to make any changes in the vb.net application because I'm still getting the above login failed error after converting the report?
0
 
LVL 18

Expert Comment

by:vasto
ID: 38763341
jeremyzit, I am a little confused : My guess is  that you have a dataset and you are setting it as a datasource for your report using the code from the link above. If this is correct then the report should not even try to connect to the database because the datasource will be already set in code. The reason why the report is trying to connect is that it needs some additional information from the database. It might be a command used to get dynamic parameters values or a subreport.  If you want to use the report just with the data prepared in your WEB application you need to find what else your report is looking for.

If you converted the report to use OLEDB and it is still asking for login then the login information is not correct. What did you use - Integrated security ? If yes then keep in mind that the user which is running your WEB application is a system user , while on your machine this is probably you. You need to allow both users to connect to the database.
0
 

Author Comment

by:jeremyzit
ID: 38763404
I don't load the data via a sql string. It just runs the report which needs to connect to refresh the data in the table it pulls from. I am using integrated security, Should I not be? How do you allow a system user to connect to the database?
0
 
LVL 18

Accepted Solution

by:
vasto earned 500 total points
ID: 38763680
There are 2 ways set the data in Crystal :
PULL - you need to set the connection info for the report and it will connect and get the data
PUSH - you will retrieve the data in  your program and will pass it to the the report

The link above is using PUSH method and I thought this is what you whant to do because the table is already retrieved and available.

If you want the report to retrieve the data you will need to provide the login info. Your code is already doing this , but I guess you are setting just the location

Line 39:             crtableLogoninfo.ConnectionInfo = crConnectionInfo

crConnectionInfo contains properties for Server, User and Password - use them to provide a valid user name and password for the connection.

If you use Integrated security the report will try to connect with the current user credentials . On you WEB Server this will be the account, which is used to run IIS. You need to create a login for this account in your database server.

I usually prefer to set the connection to use a specific user (not the integrated security)

So start with these question :
1. Which method do you want to use PUSH or PULL ?
  - if it is PUSH - use the code from this link:http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

  -if it is PULL and you want to use integrated security - add (or ask the DBAto add) a login to the database server, if a specific use is OK then set crConnectionInfo.User and crConnectionInfo.Password (you might need also to run the CRDataSource tool and to set the report to use the same user)
0
 

Author Comment

by:jeremyzit
ID: 38765036
I'm trying to "pull" the data. What would the user be? domainname\servername

I'm passing the login info but it seems that it doesn't use that, and uses the web user. Is there a way to override that and use the login info I pass?
0
 
LVL 18

Expert Comment

by:vasto
ID: 38765053
The user name will the same as the login in your database. I guess it will be without Domain name.
crConnectionInfo contains all the iformation used for login. There should be a flag IntegratedSecurity (or similar) , which should be set to false
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38765499
Are you using integrated security on the database?

mlmcc
0
 

Author Comment

by:jeremyzit
ID: 38765843
Yes, I don't have to. What is best?
0
 
LVL 18

Expert Comment

by:vasto
ID: 38768775
There is no difference for the report. Using integrated security requires DBA to be involved and if you don't have all the logins created it might be easier to manage the connection by using a specific user. Another advantage for specific user is the use of connection pool. SQLServer will use the same connection for all reports , which will be a little bit faster because the chance is that the previous connection from the connection pool will be used instead of creating a new one each time.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now