Unable to update datasource location through Set Datasource Location VS2019 Community

[sbankscharles]

I am using Visual Studio 2019 Community with Crystal Reports SP27, CRforVS13SP27_0-10010309 and CR13SP27MSI64_0-10010309. The data for the report is pulled from a SQL 2019 server using a stored procedure. I have added a new field to the stored procedure so I now need to update the report. After going into Set Datasource Location I select OLE DB (ADO) and choose SQL Native Server 11.0. In connection information once I enter the server, user id and password I can select my database without any issue. From there I can get a list of my stored procedure and choose which one I used. After selecting the stored procedure I am able to select Update which brings up the enter parameter box. Once I do that an error message pops up "Data Object cannot be opened/accessed" followed by "Unknown Database Connector Error" preventing from completing the update.

If I create a new report I am able to connect to the database and bring in data to the report. Because I can access data in a new report this issue is only with the set datasource process. I cannot find anything online to help resolve this issue. My report is very complex and I'd rather not have to recreate it. Also, I'd like to resolve this for future updates.

Thanks

[former_member11696 · Accepted solution]

Hi Charles,

The Embedded designer is limited, based on very old technology and should not be used as a final test for the app, run your app to verify it works.

To manually update the report download a trial version of the full Report Designer.

Likely though the connection info is full and can't display all of the SP's.

Open the report, right click on Database, log onto your server and then right click on the Connection.

You should see the Options menu item, select it and then change the filter to show SP's only.

See if that works.

If it doesn't then download the Trail version of CR 2016, don't use CR 2020, it's 64 bit only and will likely cause problems for you.

You can get it here: https://www.sap.com/products/crystal-reports.html

Don

[former_member11696]

Hi Charles,

Are you using the embedded Report Designer in VS or are you using a standalone copy of the full CR Designer?

Did you Verify Database after re-setting it the SP? That should fix/update the rpt file with the new SP info.

See if converting it to ODBC works and then convert it back to OLE DB.

The VS IDE defaults to 32 bit, so you may need to create a DSN in both 32 and 64 ODBC Admin.

Don

[sbankscharles]

Don,

I am using the embedded report designer for VS. My initial problem was that verify did not work so I tried to use set datasource location. Just like with set datasource location, I could see a list of databases but when I tried to complete the verify process I got a message that the database could not be found. Since the indication I got from the (lack) of responses was ‘this should work’ I did some playing around and was able to (sort of) resolve my issue. Because the work-around doesn’t make sense to me I want to let you know what I figured out.

In development I had created my database and stored procedures under the default SQL Data Directory. Since I planned on retrieving data for the reports using code, all reports used stored procedures. When I put the application into production I moved the database to an application data folder under the Program Data folder. I set my connection string in the app.config to use an SQL Express Instance and attach to the datafile. Everything worked great, including making changes to the report and test those changes by running my application (not using the Preview Data of the report designer). Most of this work had been done with Visual Studio 2015 Professional and SQL 2012. Recently I switched to VS2019 and SQL 2019 and now my client wanted me to make some changes with the data. After adding a couple of fields to my table I updated my stored procedure. As far as I know, there is no way for Crystal Reports to access an SQL Datafile so I attached the file to my SQL Developer Server. When Verify Database failed I figured I needed to change the datasource from “myserver\inventory” to “myserver\c:\program data\custInv\inventory.mdf”. Because I was able get as far as selecting my stored procedure I thought this could either be a limitation or issue with VS 2019 Community. As a check to that theory, I decided to try creating a new report and see if I could pull in data. When that worked, I went online and researched Crystal Reports and VS 2019 Community. When that didn’t turn up anything I posted my question expecting “You can’t change a datasource location with either CR27 or VS 2019 Community”. Thus my comment about lack of response is not meant as a bad thing.

Since that didn’t happen I decided to try the following. I created a new Inventory database on my server (did not want to attach even under default folders). Then I copied my production database over those files (yes I had to take the database off-line). Once that was done I was able to go into my report and verify the database. Now I get the message about the database file being change and preparing to fixup the report. Once that is done my new fields have been added and everything works. I don’t understand why it would not work with a file path but would like to get that to work for future changes. Any ideas?

Thanks