Oledb VS Sql. Oledb works with Sql Server; Sql doesn't...why

[mrmagoo]

I'm using the System.Data.OleDb for SQL Server access, and it works
perfectly. However, I see a lot of code examples that use
System.Data.SqlClient. So I try it and it and can't get it to work.

Here are my samples. Why does Oledb work and SqlClient not?

' At the top of the module:
Imports System.Data.SqlClient

Dim cn As SqlConnection = New SqlConnection("<ConnString>") '< -- error
here
cn.Open()
Dim cmd As SqlCommand = New SqlCommand("MyStoredProc", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter = cmd.Parameters.Add("@Param", "<val>")
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()

When I run it, I get an error on the Dim cn As SqlConnection line:
--------------------------------------
An unhandled exception of type 'System.ArgumentException' occurred in
system.data.dll
Additional information: Keyword not supported: 'provider'.
--------------------------------------


But this works fine.

' At the top of the module:
Imports System.Data.OleDb

Dim cn As OleDbConnection = New OleDbConnection("<ConnString>")
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("MyStoredProc", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As OleDbParameter = cmd.Parameters.Add("@Param", "<val>")
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader()

Should I continue to use SystemData.OleDb for Sql Server access? Why am I
getting an error with SqlClient?

Thanks.

 

[Ken Tucker [MVP]]

Hi,

The sqlclient class only works with sql server. You do not
need the provider in the connection string.

Ken

 

[mrmagoo]

Thanks...that worked.

Is one better than the other? Should I use SqlClient for Sql Server?

 

[Ken Tucker [MVP]]

Hi,

Use sqlclient it is optimized for sql server.

Ken

 

[mrmagoo]

thanks!

Hi,

Use sqlclient it is optimized for sql server.

Ken

 

[Jim Hughes]

See www.ConnectionStrings.com for a properly formed SqlConnection
ConnString.