Using SQLOLEDB provider with visual studio express

Question

I migrate a program from Access to SQL Server 2005.

To keep  the OLEDB interface, I needed to use the SQLOLEDB provider rather than the .Net SQL Client provider (which is the only possibility if you use the create new connection tool of visual studio express)

To achieve this I manually changed the connexion string to :

Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial Catalog=aeem;Integrated Security=SSPI

My application works ( at least the sql select )

The problem is : The dataset designer doesnt work anymore . If I try to configure a dataset adapter I get the following message :

Unable to find connection 'AEEM_2003_Listes_des_Professeurs___ConnectionString' for object 'MySettings'. 
The connection string could not be found in application settings, or the data provider associated with 
the connection string could not be loaded

The connexion string does exist, but visual studio doesn't accept the keyword "provider"

Is it a bypass?

Migrating from Access to SQL is recommanded but if you cannot use visual tools of visual studio express to migrate, it will be impossible. Pls send this requirements to your planners

---

F. Palangié

Answer 1

Dear fpalang,

Perhaps you can provide us with a code snippet that is causing you trouble.

Could it be that you are trying to create a sqlclient.sqlconnection rather than an oledb.oledbconnection? The connectionstrings are different. I wrote you two examples.

Example 1.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cnstring As String
        cnstring = "Password=pwd;Persist Security Info=True;User ID=pwd;Data Source=192.168.1.1"
        Dim cn As New SqlClient.SqlConnection(cnstring) 'sqlclient connection
        Try
            cn.Open()
            If cn.State = ConnectionState.Open Then
                MsgBox("Succes!")
            End If
            cn.Close()
        Catch ex As Exception
            MsgBox("No succes :-(")
        Finally
            cn.Close()
        End Try
    End Sub

Example 2.

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cnstring As String
        cnstring = "Provider=SQLNCLI.1;Password=pwd;Persist Security Info=True;User ID=pwd;Data Source=192.168.1.1"
        Dim cn As New OleDb.OleDbConnection(cnstring) 'oledb connection
        Try
            cn.Open()
            If cn.State = ConnectionState.Open Then
                MsgBox("Succes!")
            End If
            cn.Close()
        Catch ex As Exception
            MsgBox("No succes :-(")
        Finally
            cn.Close()
        End Try
    End Sub

For more code examples / connection string examples you can go to:

http://www.connectionstrings.com/


Cheers,

John

Answer 2

Thank you john, but the problem is not with my program, it works.. but it is with the visual studio dataset designer which doesn't work anymore when I use SQLOLEDB provider.

I have an existing VB program ( a big one) developped with visual studio express to access an ACCESS database with OLEDB.  I want to reuse this program with SQL EXPRESS 2005.

With the visual studio express tools SQL EXPRESS 2005 can be accessed only by the native .NET framework SQL client access SQLNCLI. If I use the native SQL connection string generated by visual studio I get the following error message :

An OLE DB provider was not specified in the ConnectionString. For example, 'Provider=SQLOLEDB;'.

If I use a Keyword "Provider", either with SQLNCLI or with SQLOLDB in the connection string, my program works correctly but the visual studio express dataset designer doesn't work anymore because it doesn't accept any connection string with the "Provider" keyword.
and I get the message given in my first question. Note that this  message is displayed at execution time but generated during the build by visual studio.

Now: is it a bug or a feature (to limit visual studio express capabilities.

---

F. Palangié

Answer 3

See if the following helps:

http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/1b2bc23c-d60f-4b1b-b9cf-2d7ff28cdb94/

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

May be you need a MDAC(Microsoft Data Access Componet) or

SYMPTOMS

Microsoft OLE DB Provider for SQL Server does not support nested transactions, and the following error message is returned:

Could not start a transaction for OLE DB provider '%ls'.

Microsoft SQL Server also returns the following error message if the provider is a SQL Server-specific provider:

Server: Msg 7392, Level 16, State 2,
Could not start a transaction for OLE DB provider 'SQLOLEDB'. [OLE/DB provider returned message: Only one transaction can be active on this session.]

CAUSE

This error indicates that a data modification statement is being attempted against an OLE DB provider when the connection is in an explicit or implicit transaction, and the OLE DB Provider does not support nested transactions. SQL Server requires this support so that, on certain error conditions, it can terminate the effects of the data modification statement while continuing with the transaction.

RESOLUTION

To work around this problem, set XACT_ABORT to ON before the transaction. This causes SQL Server to terminate the surrounding transaction when an error occurs while processing the data modification statement. If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB Provider.

STATUS

---

Don't judge me, just Upgrade me. Thanks!

Answer 5

I migrate a program from Access to SQL Server 2005.

To keep  the OLEDB interface, I needed to use the SQLOLEDB provider rather than the .Net SQL Client provider (which is the only possibility if you use the create new connection tool of visual studio express)

To achieve this I manually changed the connexion string to :

Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial Catalog=aeem;Integrated Security=SSPI

My application works ( at least the sql select )

The problem is : The dataset designer doesnt work anymore . If I try to configure a dataset adapter I get the following message :

Unable to find connection 'AEEM_2003_Listes_des_Professeurs___ConnectionString' for object 'MySettings'. 

The connection string could not be found in application settings, or the data provider associated with 

the connection string could not be loaded

The connexion string does exist, but visual studio doesn't accept the keyword "provider"

Is it a bypass?

Migrating from Access to SQL is recommanded but if you cannot use visual tools of visual studio express to migrate, it will be impossible. Pls send this requirements to your planners

---

F. Palangié

Hi F. Palangié

I think I am having exactly the same issue. I have spent many hours on that and the only conclusion I have com to so far is that: it is the Visual Studio Express version limitations – it only allows to use Jet and Sql Server Database Files in designer mode. Please see this post I think it explains it: http://sqlite.phxsoftware.com/forums/t/1038.aspx
I tried both VSC# Express 2005 and 2008. 
I also tried VS 2008 Professional and it allows any connection strings in Dataset designer.

Answer 6

Thank you tadkaliszewski, at least an answer to my question, even if it is not positive, it is better than nothing!


I will now try to push Microsoft to remove this limitation because the migration from Access to a real SQL DB is a real requirement for hobbyist programers working for small not for profit organizations.

---

F. Palangié