Questions from some Forums


Q.

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

  

conn = New System.Data.OleDb.OleDbConnection

  conn.ConnectionString = connectionString

  conn.Open()

  myTrans = conn.BeginTransaction()

  myCommand = New System.Data.OleDb.OleDbCommand(query, conn, myTrans)

  myReader = myCommand.ExecuteReader()

  -- code not shown creates a datatable from the myReader return

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: 

   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

connectionString = "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:

'connectionString =

"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.


A.


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

dim conn as New System.Data.SqlClient.SqlConnection

conn.ConnectionString = connectionString

conn.Open()

myTrans = conn.BeginTransaction()

myCommand = New System.Data.SqlClient.sqlcommand(query, conn, myTrans)myReader = myCommand.ExecuteReader()

' code not shown creates a datatable from the myReader return

Comments