Sql Server Error: No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).

Connection string to SQL server Express

I am having trouble with connection strings from VS 2005 to an SQL Express database.

I have an application now running using an Access Database and OLEDB.    I wanted to convert my data from Access to SQL Server.   All Queries are handled by a module that I wrote that accepts an SQL statement and returns a DataTable.   I would like to use this same technique with an SQL Server database
This is the code from the module 
 

1conn = New System.Data.OleDb.OleDbConnection
2  conn.ConnectionString = connectionString
3  conn.Open()
4  myTrans = conn.BeginTransaction()
5  myCommand = New System.Data.OleDb.OleDbCommand(query, conn, myTrans)
6  myReader = myCommand.ExecuteReader()
7  -- code not shown creates a datatable from the myReader return 
8

To see if this would work I used the northwind database.   I added it to my Project and I was able to retrieve data from the table using a table adapter.   I built the table adapter by following the tutorials and was able to populate tables and fill a GridView Control.

The connect string shown in the table adapter was:

 

1  Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True

 

I tried to connect using this and got the following error:

'Error message -- "An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'." String

I then tried the connection string with the provider information 

1connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True;Provider=SQLOLEDB"

And got the following error message

'"No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21)." String

I also tried to give a full path to the database file and failed with the same error message:

 

1'connectionString = 
2"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Cassini\NorthWind\App_Data\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True;Provider=SQLOLEDB" 

 

'"No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21)." String

I have looked through this forum and tried many other combinations and can't get it to work.   Any Ideas of what the string should be.

Thanks.



The Soulation Is :

If you are connecting to sql you should be using the sqlclient class instead of OleDb.  Try it like this:

1dim conn as New System.Data.SqlClient.SqlConnection
2conn.ConnectionString = connectionString
3conn.Open()
4myTrans = conn.BeginTransaction()
5myCommand = New System.Data.SqlClient.sqlcommand(query, conn, myTrans)myReader = myCommand.ExecuteReader()