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

jinying8869

　　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
  

1 conn = 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()