By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,544 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,544 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.


Mar 28 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Mar 28 '06 #2

P: n/a
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.


Mar 28 '06 #3

P: n/a
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.
>
>
>
>



Mar 28 '06 #4

P: n/a
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.
>
>
>
>



Mar 29 '06 #5

P: n/a
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.

Mar 29 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.