other ways to use/open a Database Connection?

[DraguVaso]

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

 

[Guest]

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

 

[Miha Markic [MVP C#]]

Hi,

You are not closing the connection. You realy should.

 

[scorpion53061]

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?

 

[DraguVaso]

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