Link to home
Start Free TrialLog in
Avatar of jeremyzit

asked on

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

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.
Avatar of plusone3055
Flag of United States of America image

Avatar of jeremyzit


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.
What code are you using?

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

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.
You are using SQL Native Client to connect to the database. Switch to OLE DB.
Can I have an example to swith that to OLE DB?
Do you have the SQL native client installed on the server?  Generally it is only installed on workstations.

The easiest way ids to download and run this tool:

You don't need to buy it.
The type initializer for 'CrystalDecisions.CrystalReports.Engine.ReportDocument' threw an exception. This is the error I get when I hit start on the CRDataSource.

I do have the sql native client installed on the web server.
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 :
Before to do that uninstall currently installed version.

Do I need to make any changes in the application because I'm still getting the above login failed error after converting the report?
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.
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?
Avatar of vasto
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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
Are you using integrated security on the database?

Yes, I don't have to. What is best?
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.