?
Solved

Creating True OLEDB Connectivity

Posted on 2007-08-10
7
Medium Priority
?
226 Views
Last Modified: 2013-11-26
Hello,

I have an application that needs to be able to connect to any OLEDB enabled database.

The application utilizes the MSDASC.dll connection screen to generate the connection strings.  That works fine.  

Currently, the various connection string fields are stored in the application's database "Provider", "Data Source", "UserID", "Initial Catalog", etc.  So, when the user needs to connect to that database again, the application puts the connection string back together using each field.  This also is working.

My problem is that I need a simple way to store all of the connection strings.  If we continue to use this method for storing the connection strings, I'm going to have to write a routine to piece the connection string back together for every kind of OLEDB database that exists.  (e.g. MS Access uses Provider, Data Source, User ID and Password while MS SQL uses Provider,  Data Source, Initial Catalog, Integrated Security, etc)

This would kind of cut down on the connectability, as users would only be able to connect to platforms for which I had specifically written code.

Can anyone think of a better way to store and retreive the connection string information?

Thanks in advance,
-Torrwin
0
Comment
Question by:Torrwin
  • 4
  • 3
7 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19679246
Persist them to an XML file.

Bob
0
 
LVL 13

Author Comment

by:Torrwin
ID: 19680378
Ok, but that doesn't solve the problem of piecing the connection string back together by platform does it?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19680733
I don't know about piecing them together, but I do know about building dynamic strings with String.Format:

Dim connectionString As String = "Data Source={0};UID={1};Pwd={2}", dataSource, userID, password)

Bob
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Author Comment

by:Torrwin
ID: 19685777
That is not the problem.  The problem is that for every different platform, you need a different connection string.  So, right now I have code similar to this:

Select Case Provider
     Case SQL
          sConnStr = "Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;"
     Case Oracle
          sConnStr = "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"
     Case Access
          sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;"
     Case MySQL
          sConnStr = "Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;"
...etc...
End Select

While MSDASC can setup a connection to any OLEDB enabled datasource, we'll only be able to re-connect later to those datasources which are specifically listed in the SELECT statement above.  Thus, we can't label our product as able to connect to any OLEDB enabled datasource.

How can we better store the connection strings so that they will automatically rebuild themselves, and thus eliminate the SELECT statement?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 19688695
I don't understand what the Select Case has to do with the MSDASC Link Builder dialog.  You can get the connection string that dialog creates, and store that.

Bob
0
 
LVL 13

Author Comment

by:Torrwin
ID: 19765366
Bob,

Your confusion on this subject led me to find that "ConnectionString" was a property of the MSDASC dialog, which answers my question.  The program (which I was given) stored the individual pieces such as "Provider", "DataSource", "Username", and "Password".  It then tried to piece these back together instead of just storing the entire connection string in the first place.

So, should I ask for this question to be deleted or would you like me to award you the points since you led me in the right direction?

Thanks for the help as always,
-Torrwin
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19770806
Torrwin,

If I was helpful, and/or answered your question, then feel free to accept the comment that best helped you.  If not, feel free to ask for a PAQ/Refund.

Bob
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

862 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