how to know if to close sqlreader

  • Thread starter Thread starter cj2
  • Start date Start date
C

cj2

if I'm instigating a sqlreader like the line below and it throws and
exception, how do I know if the reader was instigated or not?

MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
account = '" & mact & "'", MySqlConnection).ExecuteReader

If it was I need instigated it needs to be closed. If it wasn't
instigated then trying to do anything with it throws another exception.
 
cj2 said:
if I'm instigating a sqlreader like the line below and it throws and
exception, how do I know if the reader was instigated or not?

MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
account = '" & mact & "'", MySqlConnection).ExecuteReader

If it was I need instigated it needs to be closed. If it wasn't
instigated then trying to do anything with it throws another
exception.
Click to expand...

If 'ExecuteReader' throws an exception, there is no SqlReader. Two things
I'd change: 1st, separate the New statement from the ExecuteReader call in
order to know where the exception happens. 2nd, use the SqlCommands
parameters property.




Armin
 
cj2 said:
if I'm instigating a sqlreader like the line below and it throws and
exception, how do I know if the reader was instigated or not?

MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
account = '" & mact & "'", MySqlConnection).ExecuteReader

If it was I need instigated it needs to be closed. If it wasn't
instigated then trying to do anything with it throws another
exception.
Click to expand...

If 'ExecuteReader' throws an exception, there is no SqlReader. Two things
I'd change: 1st, separate the New statement from the ExecuteReader call in
order to know where the exception happens. 2nd, use the SqlCommands
parameters property.




Armin
 
if I'm instigating a sqlreader like the line below and it throws and
exception, how do I know if the reader was instigated or not?

MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
account = '" & mact & "'", MySqlConnection).ExecuteReader

If it was I need instigated it needs to be closed.  If it wasn't
instigated then trying to do anything with it throws another exception.
Click to expand...

I have always set mine up like this and have never had any issues.

Dim oReader As SqlDataReader
Dim oConnection As SqlConnection
Dim strSQL As String

'set up the connection and the command...
oConnection = New SqlConnection("Data Source=.
\SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=true")


strSQL = "SELECT * FROM COMPUTERS"
Dim oCommand As SqlCommand = New SqlCommand(strSQL,
oConnection)

'open the connection, and use the reader to populate
the combobox
oConnection.Open()
oReader = oCommand.ExecuteReader()

Do While oReader.Read()

Loop

'Clean up
oConnection.Close()
oCommand = Nothing
oReader = Nothing
oConnection = Nothing
 
if I'm instigating a sqlreader like the line below and it throws and
exception, how do I know if the reader was instigated or not?

MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
account = '" & mact & "'", MySqlConnection).ExecuteReader

If it was I need instigated it needs to be closed.  If it wasn't
instigated then trying to do anything with it throws another exception.
Click to expand...

I have always set mine up like this and have never had any issues.

Dim oReader As SqlDataReader
Dim oConnection As SqlConnection
Dim strSQL As String

'set up the connection and the command...
oConnection = New SqlConnection("Data Source=.
\SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=true")


strSQL = "SELECT * FROM COMPUTERS"
Dim oCommand As SqlCommand = New SqlCommand(strSQL,
oConnection)

'open the connection, and use the reader to populate
the combobox
oConnection.Open()
oReader = oCommand.ExecuteReader()

Do While oReader.Read()

Loop

'Clean up
oConnection.Close()
oCommand = Nothing
oReader = Nothing
oConnection = Nothing
 
I have always set mine up like this and have never had any issues.

Dim oReader As SqlDataReader
Dim oConnection As SqlConnection
Dim strSQL As String

'set up the connection and the command...
oConnection = New SqlConnection("Data Source=.
\SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=true")

strSQL = "SELECT * FROM COMPUTERS"
Dim oCommand As SqlCommand = New SqlCommand(strSQL,
oConnection)

'open the connection, and use the reader to populate
the combobox
oConnection.Open()
oReader = oCommand.ExecuteReader()

Do While oReader.Read()

Loop

'Clean up
oConnection.Close()
oCommand = Nothing
oReader = Nothing
oConnection = Nothing
Click to expand...

Setting your objects to Nothing when they're going out of scope does
not help in cleaning them up and in fact, may hinder the garbage
collector.

In addition, your SqlReader objects need to be disposed when you are
finish with them so the Using statement is ideal for this sort of
thing:

Using oConnection As new SqlConnection(...)
Using oReader As New SqlReader(...)
End Using
End Using

This will ensure that the items that need to be disposed will be.

Chris
 
I have always set mine up like this and have never had any issues.

Dim oReader As SqlDataReader
Dim oConnection As SqlConnection
Dim strSQL As String

'set up the connection and the command...
oConnection = New SqlConnection("Data Source=.
\SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=true")

strSQL = "SELECT * FROM COMPUTERS"
Dim oCommand As SqlCommand = New SqlCommand(strSQL,
oConnection)

'open the connection, and use the reader to populate
the combobox
oConnection.Open()
oReader = oCommand.ExecuteReader()

Do While oReader.Read()

Loop

'Clean up
oConnection.Close()
oCommand = Nothing
oReader = Nothing
oConnection = Nothing
Click to expand...

Setting your objects to Nothing when they're going out of scope does
not help in cleaning them up and in fact, may hinder the garbage
collector.

In addition, your SqlReader objects need to be disposed when you are
finish with them so the Using statement is ideal for this sort of
thing:

Using oConnection As new SqlConnection(...)
Using oReader As New SqlReader(...)
End Using
End Using

This will ensure that the items that need to be disposed will be.

Chris
 
I don't think you can use sql reader like that. It gives me
sqldatareader has no constructors
 
I don't think you can use sql reader like that. It gives me
sqldatareader has no constructors
 
I got it figured out. I put in a finally statement

If Not MySqlReader Is Nothing Then MySqlReader.Close()

I found finally executes even if in the catch statement I have return.
I was a bit surprised of that. And is nothing works for determining if
the reader has been instigated yet.
 
I got it figured out. I put in a finally statement

If Not MySqlReader Is Nothing Then MySqlReader.Close()

I found finally executes even if in the catch statement I have return.
I was a bit surprised of that. And is nothing works for determining if
the reader has been instigated yet.
 
I can assure you: it does,

Chris is for sure not a newbie and like Chris wrote: it is a better way.

(Although the AdoNet group has included in the Close of the datareader the
Dispose of the unmanaged resources and vice vers)

Cor
 
I can assure you: it does,

Chris is for sure not a newbie and like Chris wrote: it is a better way.

(Although the AdoNet group has included in the Close of the datareader the
Dispose of the unmanaged resources and vice vers)

Cor
 
I don't think you can use sql reader like that. It gives me
sqldatareader has no constructors
Click to expand...

You're right, my apologies. I was emphasizing the use of the Using
statement.

It would be something like:

Using oReader As SqlReader = oCommand.ExecuteReader(...)
End Using

Sorry about that.

Chris
 
I don't think you can use sql reader like that. It gives me
sqldatareader has no constructors
Click to expand...

You're right, my apologies. I was emphasizing the use of the Using
statement.

It would be something like:

Using oReader As SqlReader = oCommand.ExecuteReader(...)
End Using

Sorry about that.

Chris
 
Back
Top