Sql Server Error: No error message available, result code: DB_E_ERRORSOCCURRED(0
Connection string to SQL server ExpressI 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
2conn.ConnectionString = connectionString
3conn.Open()
4myTrans = conn.BeginTransaction()
5myCommand = New System.Data.OleDb.OleDbCommand(query, conn, myTrans)
6myReader = 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:
1Data 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()
页:
[1]