Question oledb connection string with sql server 2005 express

[ashahmy]

Hi All,
I was create a project with vb.net and sql server 2005. I was used oledb connection string for database connection. With my sql server database it work properly But i don't know how to connect mdf file using oledb connection string. But I was tried following way but it got error.

accessConnection.ConnectionString = "provider=SQLOLEDB;Data Source=.\SQLEXPRESS;AttachDbFilename=C:\data\cosmetic.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

if any one know please help me.
Thank You.

 

[jmcilhinney]

Why are you using OleDb for SQL Server? Is there a valid reason for not using SqlClient? If not then switch your code over to using SqlClient instead of OleDb. The change will not be especially dramatic because there's a 1:1 correspondence between classes in the two namespaces.

 

[ashahmy]

Thanks for your replay. In my project oledb connection is essential. because in my project i am using following code,
Dim schemaTable As DataTable = accessConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
i was try to convert above code to sql supported one. but i got error.
that's way i am using oledb connection string.
Thank You.

 

[jmcilhinney]

Can you not just use the SqlConnection.GetSchema method? It can give you information about tables. It may not be exactly the same information as GetOleDbSchemaTable returns but it is probably all you need. You should try it out to see.

I've never seen a SQL Server Express database attached on demand using an OleDbConnection. If it can be done, I very much doubt that AttachDbFileName is a property you can set directly in the connection string. Maybe in the Extended Properties but that's just a guess.

 

[jmcilhinney]

It seems that my doubts were unfounded. I just checked ConnectionStrings.com and it appears that you're just using the wrong provider name.

SQL Server 2005 Connection String Samples - ConnectionStrings.com

I'd still recommend using SqlClient if you can, so test GetSchema first. If it can't give you all the information you need, then use OleDb.