[RESOLVED] Database Connection Issue

[CADman69]

Can sombody please tell me where I am going wrong, please. I am trying to teach myself how to connect to a database that is not attached to my project.

I am using VB2010 Express with SQL express.

Here's the code I have;

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        
        dbProvider = "PROVIDER=Microsoft.SQLEXPRESS;"
        ' I've tried other versions of provider names such as, Provider=SQLEXPRESS.  
        dbSource = "Data Source=C:\MarketPlaceDatabase.mdf"

        con.ConnectionString = dbProvider & dbSource

        con.Open()
        sql = "Select * From tblManagersTable"
        da = New OleDb.OleDbDataAdapter(sql, con)

        MsgBox("Database is now open")
        con.Close()
        MsgBox("Database is now closed")

    End Sub

The form is simple, 1 single button on it. Press it and it is suppose to connect and display a message box, but I keep getting an error.

Every project I have ever created has a SQL database attached to it so I a baffled to what is wrong. Also am wondering what the difference is between a MDF and MDB database extension. Mine always safe as a MDF, but all the articles I have read reference a MDB file.

[Dark Light]

Do you have Microsoft SQL Server installed and have you created the database?

Use this website for the connection string.

[CADman69]

I have SQL Server 2008 installed, and the daabase I am tryingto connect to is one from a previous project, so I know that it works and has info in it.

[techgnome]

Well first... MDB is an Access database.... MDF is a SQL Server Data file...
Secondly, if you are connecting to a SQL Server database, you should be using the SQLClient namespace not OLEDBClient....

next, you'll want to make sure you have the proper connection. Normally, you do not connect to the MDF directly... there's an exception to this where you can create user instance databases and connect to them on the fly, but either way, you need to make sure you have the right connection string...
http://www.connectionstrings.com is a great place to start...

Another great place to start will be in the Database FAQ & Tutorials thread in the Database forum here.... there's a great deal of infio in there to get started with....

-tg

[jmcilhinney]

There are a huge number of databases out there that all do pretty much the same thing but all in a different way. There are a huge number of other non-database data sources. So that application developers didn't have to know the ins and outs of connecting to each different data source, Open Database Connectivity (ODBC) was created. ODBC is a standard interface for data sources. Vendors or third parties can create an ODBC driver for pretty much any data source. An application developer then only needs to know how to connect to an ODBC driver and they can then connect to any data source for which an ODBC driver exists. The .NET Framework provides the System.Data.Odbc namespace for connecting to ODBC data sources.

Microsoft created Object Linking & Embedding, Database (OLE DB) to do a similar job to ODBC but specifically for the Windows platform. While I'm no expert, I believe that OLE DB is generally more efficient on Windows than ODBC. An OLE DB provider is the equivalent to an ODBC driver. An application developer only has to know how to connect to an OLE DB provider and they can connect to any data source for which an OLE DB provider exists. The .NET Framework provides the System.Data.OleDb namespace for connecting to OLE DB data sources.

The most well-known OLE DB provider is Jet 4.0, which is what you would have seen used in examples that connect to MDB files. MDB files are commonly called Access databases but, technically, they are not. An MDB file is actually a Jet database and Access is an application that was built to create and manipulate Jet databases.

When you use an OleDbConnection in .NET, the connection string must include the provider. In your examples, that provider is Jet 4.0. With Access 2007, Microsoft released a new file format and all that goes with it. The ACCDB file format has no specific relationship to Jet, so it can truly be called an Access database. There is a new database engine called the Access Connectivity Engine (ACE) and also an ACE 12.0 (Office 2007) and ACE 14.0 (Office 2010) OLE DB provider. ACE can also connect to MDB files and, like Jet, it can also connect to Excel files and various other formats, e.g. CSV.

In your code, you are trying to use OLE DB to connect to SQL Server. That is OK, but not advised. If you do want to use OLE DB then you have to at least refer to an OLE DB provider that exists. Microsoft.SQLEXPRESS is just something that you made up. It's not an actual OLE DB provider. If you follow the link that tg provided, you'll find various connection string formats for various data sources, including OLE DB for SQL Server. There you will find the proper provider to use.

It's generally preferable to use OleDb over Odbc but it's also preferable to use a data source-specific provider over either of them. In the case of SQL Server, The .NET Framework includes the System.Data.SqlClient namespace for connecting to SQL Server. Because SqlClient connects directly to the SQL Server instance without going through a generic interface like an ODBC driver or an OLE DB provider, it is able to provide better performance as well as more features.

When connecting to SQL Server, you should always use SqlClient unless you have a very good reason not to. If you check out the Database FAQ thread that tag mentioned (there's a link in my signature) then you'll find various .NET resources, including my own Retrieving & Saving Data thread from the VB.NET CodeBank. All those examples use SqlClient, although they can be easily adapted to use other providers, e.g. use OleDbConnection instead of SqlConnection.

When working with SQL Server databases, as tg said, there are two ways to create and connect to the database. The conventional way is to open SQL Server Management Studio (Express) and create a new database. In that case, an MDF file is created behind the scenes and permanently attached to your instance. With SQL Server Express, your instance will generally be named '<MachineName>\SQLEXPRESS', which can also be referred to using '(local)\SQLEXPRESS' or '.\SQLEXPRESS'. In a SqlClient connection string, that instance name is the Data Source and the name of the database is the Initial Catalog.

The second option is something new with SQL Server Express and was introduced to make life easier for developers to create and deploy applications with local database, i.e. databases that will not have multiple clients connecting. Within Visual Studio or VB Express, you can now choose to add a Service-based Database to your project. In this case, the database is the MDF file, which is added to your project along with all the other source files, and the service that it's based on is SQL Server. When using SqlClient to connect to such a database, the Data Source is still the SQL Server instance but there is no Data Source. Instead, you use the AttachDbFileName and specify the path of your MDF file. When the code is run, the database is attached to the server instance on demand and then detached when you're done. This means that only your application can access the database.

[CADman69]

Thanks for the replies. I deleted my code and started over using the SQLClient. By the sounds of it, and what I read, its the best way to go.

I also read that it is not smart to hard code the connection string, I wasn't exactly sure what was meant by it so I put i a builder.

Here's what I come up with, it seems to work so far. Still need to play with it to see if it was a fluke.

        Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
        builder("Data Source") = "Toshiba\SQLExpress"
        builder("Integrated Security") = True
        builder("Initial Catalog") = "C:\Users\Ken\Documents\Database.mdf"
        builder("User Instance") = "True"

        TextBox1.Text = builder.ConnectionString

        Dim SQLconnect As New SqlClient.SqlConnection
        SQLconnect.ConnectionString = builder.ConnectionString

        SQLconnect.Open()
        MsgBox("Database is Open")
        SQLconnect.Close()
        MsgBox("Database is Close")

I put the textbox in there just so I had a visual to see if the builder was working properly. Even though it seems to work, I would like some opinions to if this is a smart way of doing it. Any and all opinions are welcome, and thanks for taking the time to go so in depth with the explanations.

[jmcilhinney]

Hard-coding means writing something into your actual source code, which you are still doing. The issue is that, if you want to change the value, you need to recompile the application. Data like a connection string should be stored in external to the application and then read in at run time. .NET applications have a config file specifically for that purpose. Many of the tools in VS will actually store a database connection string in the config file automatically, or you can do it manually.

In your case, open the Settings page of the project properties and add a setting of type (Connection string). Once you've added it, go to the Solution Explorer and open the app.config file to see how the connection string is stored. Now, in code, you can use My.Settings.MyConnectionString to access your connection string by name.

Apart from that, you're still building the connection string incorrectly. I addressed this specifically in my previous post:

In a SqlClient connection string, that instance name is the Data Source and the name of the database is the Initial Catalog.

When using SqlClient to connect to such a database, the Data Source is still the SQL Server instance but there is no Data Source. Instead, you use the AttachDbFileName and specify the path of your MDF file.

You're using Initial Catalog to specify a file, which is not either of those above. If you use the browse (...) button when creating your connection string in your settings, you can use a dialogue to build the connection string so you can't make a mistake.

One more thing: if you do want to use a loose MDF file then it's a bad idea to store it external to the project. You should add the file to your project as a source file. The IDE will then create a copy each time you build and place it with your EXE. You connect to the copy and so you won't make a mess of your original database, meaning it won't need to be cleaned each time you want to start over, e.g. when you deploy. In that case you use "|DataDirectory|\MyDatabase.mdf" as the path.

[CADman69]

Thanks jmcilhinney, think I see the direction your heading. I looked at my app.confiq page then realize there was no connection string stored because I do not have a database attached.

Maybe if I explain what I need my program to do. The program will be installed on a couple of laptops, use by the managers. When they link their laptop to the server, then and only then will they have access to the database. This way if the program runs without being connected, basically it'll be a dead program. But once connected and the program loads, it recognizes the database and opens it for editing.

I'm not too worried about the hard coding in this case, only because the program wll be used by family members who are the managers.

I was afraid when I was asked to do this project it was going to be above my VB IQ, but took it on anyway. If anything I am learning.

After my small explaination, I am hoping my code will work, even if it is temporary until I can grasp the whole connection issue. Do you know of a site that will explain database connection strings in detail. I looked on the "Connection String" web site, but couldn't find a detailed explaination to what each line of code is doing and the proper way of using them.