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" <-> wrote in message news:uT**************@TK2MSFTNGP12.phx.gbl...

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.

[mrmagoo]

Thanks...that worked.

Is one better than the other? Should I use SqlClient for Sql Server?
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:OK**************@tk2msftngp13.phx.gbl...

Hi,

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

Ken
---------
"mrmagoo" <-> wrote in message

news:uT**************@TK2MSFTNGP12.phx.gbl...

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,

Use sqlclient it is optimized for sql server.

Ken
------------
"mrmagoo" <-> wrote in message news:OJ**************@TK2MSFTNGP09.phx.gbl...

Thanks...that worked.

Is one better than the other? Should I use SqlClient for Sql Server?
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:OK**************@tk2msftngp13.phx.gbl...

Hi,

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

Ken
---------
"mrmagoo" <-> wrote in message

news:uT**************@TK2MSFTNGP12.phx.gbl...

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

[mrmagoo]

thanks!

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:uT**************@tk2msftngp13.phx.gbl...

Hi,

Use sqlclient it is optimized for sql server.

Ken
------------
"mrmagoo" <-> wrote in message

news:OJ**************@TK2MSFTNGP09.phx.gbl...

Thanks...that worked.

Is one better than the other? Should I use SqlClient for Sql Server?
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:OK**************@tk2msftngp13.phx.gbl...

Hi,

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

Ken
---------
"mrmagoo" <-> wrote in message

news:uT**************@TK2MSFTNGP12.phx.gbl...

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

[Jim Hughes]

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

"mrmagoo" <-> wrote in message news:uT**************@TK2MSFTNGP12.phx.gbl...

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.