other ways to use/open a Database Connection?

TheMSsForum.com: The Microsoft Software Forum

Hi,

In a VB.NET-application I use this to open a Database Connection:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim strConn As String
strConn = "Server=BESQL1;DataBase=VocalcomCetelem;User
ID=vocalcomcetelem;Password=vocalcomcetelem;Trusted_Connection=False"

Dim strSql As String
strSql = "SELECT TOP 1 * " & _
"FROM tblCalls " & _
"ORDER BY CallID DESC"

Dim dtrSql As SqlDataReader
Dim conSql As SqlConnection
conSql = New SqlConnection(strConn)
Dim cmdSql As SqlCommand
Try
conSql.Open()
cmdSql = New SqlCommand(strSql, conSql)
dtrSql = cmdSql.ExecuteReader(CommandBehavior.SingleRow)
dtrSql.Read()
If dtrSql.HasRows Then
MessageBox.Show(dtrSql.Item("tdcaNCartphy").ToString)
End If
Catch ex As Exception
MessageBox.Show(ex.Message & ex.StackTrace)
End Try
End Sub

Sometimes it gives me an error when opening the Connection (on the
conSql.Open() ).:
Like this with MDAC 2.7 installed:
The .Net Data SQL Provider (System.Data.SqlClient) requires Microsoft Data
Access Components(MDAC) version 2.6 or later.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
Or this with MDAC 2.8:
Object reference not set to an instance of an object.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()

So it seems I get troubles to get a (pooled) connection. Is there a 'safer'
way to get a connection? Using OleDB or Odbc? Or what can I do to get rid of
the error and get always my connection?

Thanks a lot in advance,

Pieter
Top

RE: other ways to use/open a Database Connection? by anonymous

anonymous
Wed Jun 02 06:16:03 CDT 2004

Hi
try to asign the connection to the Connection property of the Command object

cmdSql.Connection = conSq

also try to close the DataReader before left the method

----- DraguVaso wrote: ----

Hi

In a VB.NET-application I use this to open a Database Connection

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e A
System.EventArgs) Handles Button1.Clic
Dim strConn As Strin
strConn = "Server=BESQL1;DataBase=VocalcomCetelem;Use
ID=vocalcomcetelem;Password=vocalcomcetelem;Trusted_Connection=False

Dim strSql As Strin
strSql = "SELECT TOP 1 * " &
"FROM tblCalls " &
"ORDER BY CallID DESC

Dim dtrSql As SqlDataReade
Dim conSql As SqlConnectio
conSql = New SqlConnection(strConn
Dim cmdSql As SqlComman
Tr
conSql.Open(
cmdSql = New SqlCommand(strSql, conSql
dtrSql = cmdSql.ExecuteReader(CommandBehavior.SingleRow
dtrSql.Read(
If dtrSql.HasRows The
MessageBox.Show(dtrSql.Item("tdcaNCartphy").ToString
End I
Catch ex As Exceptio
MessageBox.Show(ex.Message & ex.StackTrace
End Tr
End Su

Sometimes it gives me an error when opening the Connection (on th
conSql.Open() ).
Like this with MDAC 2.7 installed
The .Net Data SQL Provider (System.Data.SqlClient) requires Microsoft Dat
Access Components(MDAC) version 2.6 or later
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean
isInTransaction
a
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConne
tionString options, Boolean& isInTransaction
at System.Data.SqlClient.SqlConnection.Open(
Or this with MDAC 2.8
Object reference not set to an instance of an object
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean
isInTransaction
a
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConne
tionString options, Boolean& isInTransaction
at System.Data.SqlClient.SqlConnection.Open(

So it seems I get troubles to get a (pooled) connection. Is there a 'safer
way to get a connection? Using OleDB or Odbc? Or what can I do to get rid o
the error and get always my connection

Thanks a lot in advance

Piete



Top

Re: other ways to use/open a Database Connection? by Miha

Miha
Wed Jun 02 06:21:33 CDT 2004

Hi,

You are not closing the connection. You realy should.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"DraguVaso" <pietercoucke@hotmail.com> wrote in message
news:O7kAC9ISEHA.3840@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> In a VB.NET-application I use this to open a Database Connection:
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim strConn As String
> strConn = "Server=BESQL1;DataBase=VocalcomCetelem;User
> ID=vocalcomcetelem;Password=vocalcomcetelem;Trusted_Connection=False"
>
> Dim strSql As String
> strSql = "SELECT TOP 1 * " & _
> "FROM tblCalls " & _
> "ORDER BY CallID DESC"
>
> Dim dtrSql As SqlDataReader
> Dim conSql As SqlConnection
> conSql = New SqlConnection(strConn)
> Dim cmdSql As SqlCommand
> Try
> conSql.Open()
> cmdSql = New SqlCommand(strSql, conSql)
> dtrSql = cmdSql.ExecuteReader(CommandBehavior.SingleRow)
> dtrSql.Read()
> If dtrSql.HasRows Then
> MessageBox.Show(dtrSql.Item("tdcaNCartphy").ToString)
> End If
> Catch ex As Exception
> MessageBox.Show(ex.Message & ex.StackTrace)
> End Try
> End Sub
>
> Sometimes it gives me an error when opening the Connection (on the
> conSql.Open() ).:
> Like this with MDAC 2.7 installed:
> The .Net Data SQL Provider (System.Data.SqlClient) requires Microsoft Data
> Access Components(MDAC) version 2.6 or later.
> at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> isInTransaction)
> at
>
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
> tionString options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> Or this with MDAC 2.8:
> Object reference not set to an instance of an object.
> at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> isInTransaction)
> at
>
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
> tionString options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
>
> So it seems I get troubles to get a (pooled) connection. Is there a
'safer'
> way to get a connection? Using OleDB or Odbc? Or what can I do to get rid
of
> the error and get always my connection?
>
> Thanks a lot in advance,
>
> Pieter
>
>


Top

Re: other ways to use/open a Database Connection? by scorpion53061

scorpion53061
Wed Jun 02 06:25:52 CDT 2004

> So it seems I get troubles to get a (pooled) connection. Is there a
'safer'
> way to get a connection? Using OleDB or Odbc? Or what can I do to get rid
of
> the error and get always my connection?

Try installing 2.8 on the server and client possibly?


Top

Re: other ways to use/open a Database Connection? by DraguVaso

DraguVaso
Thu Jun 03 08:22:01 CDT 2004

Ok I finally found out what caused the problem: Fasten your seatbelts:
The EXTRA.System-object conflcited with the SetWindowsHookEx-function. Both
of them worked fine, and there wasn't anything to see at it. But they caused
sometimes an error on the Database-Connection...

Disabling these functions on NT (and now finding an other solution for that
part) fixed the problem.

Thanks a lot to averything who helped me looking for a solution.

Pieter

"DraguVaso" <pietercoucke@hotmail.com> wrote in message
news:O7kAC9ISEHA.3840@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> In a VB.NET-application I use this to open a Database Connection:
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim strConn As String
> strConn = "Server=BESQL1;DataBase=VocalcomCetelem;User
> ID=vocalcomcetelem;Password=vocalcomcetelem;Trusted_Connection=False"
>
> Dim strSql As String
> strSql = "SELECT TOP 1 * " & _
> "FROM tblCalls " & _
> "ORDER BY CallID DESC"
>
> Dim dtrSql As SqlDataReader
> Dim conSql As SqlConnection
> conSql = New SqlConnection(strConn)
> Dim cmdSql As SqlCommand
> Try
> conSql.Open()
> cmdSql = New SqlCommand(strSql, conSql)
> dtrSql = cmdSql.ExecuteReader(CommandBehavior.SingleRow)
> dtrSql.Read()
> If dtrSql.HasRows Then
> MessageBox.Show(dtrSql.Item("tdcaNCartphy").ToString)
> End If
> Catch ex As Exception
> MessageBox.Show(ex.Message & ex.StackTrace)
> End Try
> End Sub
>
> Sometimes it gives me an error when opening the Connection (on the
> conSql.Open() ).:
> Like this with MDAC 2.7 installed:
> The .Net Data SQL Provider (System.Data.SqlClient) requires Microsoft Data
> Access Components(MDAC) version 2.6 or later.
> at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> isInTransaction)
> at
>
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
> tionString options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> Or this with MDAC 2.8:
> Object reference not set to an instance of an object.
> at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> isInTransaction)
> at
>
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
> tionString options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
>
> So it seems I get troubles to get a (pooled) connection. Is there a
'safer'
> way to get a connection? Using OleDB or Odbc? Or what can I do to get rid
of
> the error and get always my connection?
>
> Thanks a lot in advance,
>
> Pieter
>
>


Top