• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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.
0
jeremyzit
Asked:
jeremyzit
  • 9
  • 8
  • 3
  • +1
1 Solution
 
jeremyzitAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mlmccCommented:
What code are you using?

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

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

mlmcc
0
 
vastoCommented:
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
 
jeremyzitAuthor Commented:
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
 
jeremyzitAuthor Commented:
mlmcc,

I do have the sql native client installed on the web server.
0
 
vastoCommented:
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
 
jeremyzitAuthor Commented:
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
 
vastoCommented:
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
 
jeremyzitAuthor Commented:
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
 
vastoCommented:
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
 
jeremyzitAuthor Commented:
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
 
vastoCommented:
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
 
mlmccCommented:
Are you using integrated security on the database?

mlmcc
0
 
jeremyzitAuthor Commented:
Yes, I don't have to. What is best?
0
 
vastoCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now