vb.net - Visual Studio ;New System.Data.OleDb.OleDbConnection(strConString); code fails - what am I missing?

Question

I am trying to build a Visual Studio 19, Visual Basic database application using the Windows Forms App (.NET Framework) template. The template wizard works well and leads me to make a connection with the database. The app.config file is shown below.

```

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Paint.MySettings.PaintDBConnectionString" connectionString="Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|PaintDB.mdf;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
</configuration>
```

Note that both the connectionstring and providername are defined but that the latter does not include "Provider=" which is often cited as a requirement in the literature that I have read.

My code then moves on to a Form that makes use of the database. One of the first lines of code in the Form_Load subroutine is:

`strConString = My.Settings.PaintDBConnectionString`

When the code is executed the value of strConString is

"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|PaintDB.mdf;Integrated Security=True".

Note that there is no reference to "Provider=System.Data.SqlClient".

When I try to establish the connection in the form using:

conDB = New System.Data.OleDb.OleDbConnection(strConString)

I get an Exception Unhandled message thus:

System.ArgumentException: 'An OLE DB Provider was not specified in the ConnectionString.  An example would be, 'Provider=SQLOLEDB;'.'

If, however, I use the following code:

Using conDB As New Data.SqlClient.SqlConnection With {.ConnectionString = My.Settings.PaintDBConnectionString}
            conDB.Open()

The connection is made and the code moves on to the next line which is:

   da = New System.Data.OleDb.OleDbDataAdapter(strSQL, strConString)
            da.Fill(ds, "Manufacturers")

This fails on the '.Fill' command with the same error as above.

System.ArgumentException: 'An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.'

If I try to programmatically correct strConString to what I think it should be the following:

strConString = "Provider=System.Data.SqlClient;" + strConString 

then the code fails again at the '.Fill' command but this time with an Exception Thrown message as follows:

System.InvalidOperationException: 'The 'System.Data.SqlClient' provider is not registered on the local machine.'

I have seen articles in stackoverflow on both of these messages and have tried to follow the solutions but without success.

Can anyone throw any light on how I can correct my workings. I hope the additional information I have provided may help your thinking.

question from:https://stackoverflow.com/questions/65892029/visual-studio-new-system-data-oledb-oledbconnectionstrconstring-code-fails

Answer 1

If you are to use Access as Database, use this for your reference:

       //Declaration of connection
       Public sConnection As String = ""
       Public Connection As OleDbConnection 

       sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES"""
    Connection = New OleDbConnection(sConnection)
    Connection.Open()

    Dim sSelect As String = "select * from [table$]"
    Dim cmd As New OleDbCommand(sSelect, Connection)
    Dim da As New OleDbDataAdapter(cmd)

If you are to use SQL as Database, use this for your reference:

    //Declaration of connection
    Public sqlcon As SqlConnection = New SqlConnection("Server =*servername*;User Id=*userid*;Password=*password*;Integrated Security=false;Database=*dbname*;")

    Dim str as String = "SELECT * FROM table"
    Dim cmd As New SqlCommand(str, sqlcon)
    Dim da As New SqlDataAdapter(cmd)

        Dim ds As New DataSet()
        Dim dt As New DataSet

        da.Fill(ds, "TableDetails")