Connecting Crystal Reports to VPS

1
votes

I have coded a program in VB.NET which is for making proposal and printing invoices. I can connect to my VPS MySQL (Mariadb) database via my software and everything works fine (ADD/UPDATE/DELETE strings). But I also need to gather data from my database to print invoices. For making this I am using Crystal Reports XI Release 2. I need to connect Crystal Reports to my online MySQL database which is located in my VPS to get data and make the invoice ready to print.

Connection options for Crystal Reports are :

  1. Access/Excel (DAO)
  2. ADO.NET(XML)
  3. Database Files
  4. Exchange 5.5 Message Tracking Log
  5. Exchange Message Tracking Log
  6. Legacy Exchange
  7. Mailbox Admin
  8. ODBC (RDO)
  9. Olap
  10. OLE DB (ADO)
    • Microsoft Jet 4.0 OLE DB Provider
    • Microsoft Office 12.0 Access Database Engine
    • Microsoft OLE DB Provider for Analysis Service
    • Microsoft OLE DB Provider for Indexing Service
    • Microsoft OLE DB Provider for ODBC Drivers
    • Microsoft OLE DB Provider for Oracle
    • Microsoft OLE DB Provider for Search
    • Microsoft OLE DB Provider for SQL Server
    • Microsoft OLE DB Simple Provider
    • MSDataShape
    • OLE DB Provider for Microsoft Directory Service
    • SQL Server Native Client 11.0
  11. Outlook/Exchange
  12. Public Folder ACL
  13. Public Folder Admin
  14. Public Folder Replica
  15. Universes
  16. XML

I tried to connect to the VPS database in Crystal Reports with the following steps :

In Database expert menu I am selecting OLE DB (ADO) and then selecting Microsoft OLE DB Provider for SQL Server. Then it asks me Server, UserID, Password, Database. I am filling the fields but when I try to select database it shows nothing. When I leave the database field empty and click on next then it give me the following message:

Failed to open the connection. 
Details: ADO Error Code:0x 
Source: Microsoft OLE DB Provider for SQL Server 
Description : [DBNETLIB][ConnectionOpen (Connect().]SQL Server does not exist or access denied. 
SQL State:08001 
Native Error: [Database Vendor Code:17]
1
mysqldatabasecrystal-reports
Diagnosing Connection to SQL Server Your question is tagged Mysql and SQL-Server, you are selecting Microsoft OLE DB Provider for SQL Server which would not fit for MySQL, please clarify your question. – bummi
Have you tried connecting to your VPS db using other application? Maybe there it' inaccessible? – user2941651
I have coded a program in VB.NET which is for making proposal and printing invoices. I can connect to my vps MySQL (Mariadb) database and everything works fine (ADD/UPDATE/DELETE strings). But i also need to geather datas from my database to print invoices. In this case i am using Crystal Reports. But when i try to connect to my db from Crystal Reports then i am having the error. To clarify the question : I need to connect crystal reports to my online MySQL database which is located in my VPS to get datas and make the invoice ready to print. – Ardeth
You are mixing up SQL_Server with MySQL Microsoft OLE DB Provider for SQL Server take a look at MySQL connection strings – bummi
Connecting MySQL with Crystal Reports v10 – bummi

1 Answers

1
votes

I've solved that issue with following steps (Assuming MySQL Connector/ODBC already installed in your system) :

  1. Add a new connection from ODBC Data Source Administrator
  2. Select MySQL ODBC 5.3 ANSI Driver
  3. Select TCP/IP radio button and enter your VPS server's IP address with port number 3306
  4. Fill User and Password field with you database username and database password
  5. Select your database from the drop down list
  6. Click Test button and if everything works fine than click Ok
  7. Select database expert under Database menu
  8. Select ODBC (RDO) under Create New Connection menu
  9. Select your connection and click Next
  10. Enter your database user name and password and click Finish
  11. Add required tables from your database by selecting the table and clicking arrow button.

If you already created a design then open your design in Crystal Report and follow these steps:

  1. Select Set Datasource Location under Database menu
  2. Select ODBC (RDO) under Create New Connection menu which is located in "Replace With" section
  3. Select your connection and click Next
  4. Enter your database user name and password and click Finish
  5. Now select one of your previous tables from "Current Datasource" tab and select the same table in "Replace With" tab then click update. Do it for all tables one by one.
  6. Click close.

Hope this helps.