Home All Groups Group Topic Archive Search About

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

microsoft.public.dotnet.languages.vb
Author
28 Mar 2006 10:10 PM
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.

Author
28 Mar 2006 11:06 PM
Ken Tucker [MVP]
Hi,

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

Ken
---------
Show quoteHide quote
"mrmagoo" <-> wrote in message news:uTBBvRrUGHA.4900@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.
>
>
>
>
Author
28 Mar 2006 11:16 PM
mrmagoo
Thanks...that worked.

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


Show quoteHide quote
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:OKGQWxrUGHA.1572@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:uTBBvRrUGHA.4900@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.
> >
> >
> >
> >
>
>
Author
28 Mar 2006 11:31 PM
Ken Tucker [MVP]
Hi,

        Use sqlclient it is optimized for sql server.

Ken
------------
Show quoteHide quote
"mrmagoo" <-> wrote in message news:OJt5c2rUGHA.5588@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:OKGQWxrUGHA.1572@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:uTBBvRrUGHA.4900@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.
>> >
>> >
>> >
>> >
>>
>>
>
>
Author
28 Mar 2006 11:59 PM
mrmagoo
thanks!

Show quoteHide quote
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:uTlqS$rUGHA.5364@tk2msftngp13.phx.gbl...
> Hi,
>
>         Use sqlclient it is optimized for sql server.
>
> Ken
> ------------
> "mrmagoo" <-> wrote in message
news:OJt5c2rUGHA.5588@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:OKGQWxrUGHA.1572@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:uTBBvRrUGHA.4900@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.
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>
Author
29 Mar 2006 12:32 AM
Jim Hughes
See www.ConnectionStrings.com for a properly formed SqlConnection
ConnString.

Show quoteHide quote
"mrmagoo" <-> wrote in message news:uTBBvRrUGHA.4900@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.
>
>
>
>



Post Other interesting topics
Class question
Form Exit
Doing some extra task while saving databind Controls in VB.Net 2005 ?
Dynamically Loading Programs
select all text when textbox1 is clicked
Auto-stop DUN via VB.Net?
Adding HTML code to project.
SQL Server and ADO.Net best method
Re: ContextSwitchDeadlock was detected
set default printer