SqlDataReader Function From a VB6 refugee

P

Paul Ilacqua

All,
I'm trying to create a DataReader Function to call from within my class
to open a simple datareader.
I have thoroughly read the Data Access Patterns and practices and do not
believe in using canned code right off the bat. I want to rebuild the canned
code so I understand it, then I may use it or use my own understood version.

The error message is in Error Text: Message="Invalid attempt to Read when
reader is closed."


'===================== This is call to function ===================
Dim dr As SqlDataReader = Create_SQL_DataReader("20060213")
While dr.Read() !!! Errs here
End While
'===================== End call to function ===================

Shared Function Create_SQL_DataReader(ByVal sDate As String) As
SqlDataReader

Dim SQLConn As New SqlConnection(sConn)
Using SQLConn

' This Builds SQL String into "s" and uses passed in param
"sDate"
Dim s As String = BuildShiftSQL(sDate)

Dim Cmd = New SqlCommand(s, SQLConn)
SQLConn.Open()
Dim iDr As SqlDataReader = Cmd.ExecuteReader()
Return iDr
s = String.Empty
SQLConn.Close()
SQLConn.Dispose()
End Using
End Function

Error Text:
System.InvalidOperationException was unhandled
Message="Invalid attempt to Read when reader is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at WindowsApplication1.dataReaderDataSetForm.Main() in
C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
 
P

Patrice

The Return statement returns immediately (code beyond this point will not be
reached)

Also the Reader is for connected operations i.e. the underlying connection
should be open (it's likely closed not because of you explicit statmenet
that is not reached but likely because of the using statement that dispose
the connection)...
 
G

Guest

Paul,

After you return the data reader you are closing the connection:

Return iDr
s = String.Empty
SQLConn.Close()

But the data reader needs the connection to stay open until you have
finished reading the data.

An alternative is to create the data reader like this:

Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)

This will close the connection when the reader is eventually closed.

Kerry Moorman
 
G

Guest

Paul,

Ignore my last post. The Return statement ends the function so the
connection is not being closed with the code after Return.

Sorry,

Kerry Moorman


Kerry Moorman said:
Paul,

After you return the data reader you are closing the connection:

Return iDr
s = String.Empty
SQLConn.Close()

But the data reader needs the connection to stay open until you have
finished reading the data.

An alternative is to create the data reader like this:

Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)

This will close the connection when the reader is eventually closed.

Kerry Moorman



Paul Ilacqua said:
All,
I'm trying to create a DataReader Function to call from within my class
to open a simple datareader.
I have thoroughly read the Data Access Patterns and practices and do not
believe in using canned code right off the bat. I want to rebuild the canned
code so I understand it, then I may use it or use my own understood version.

The error message is in Error Text: Message="Invalid attempt to Read when
reader is closed."


'===================== This is call to function ===================
Dim dr As SqlDataReader = Create_SQL_DataReader("20060213")
While dr.Read() !!! Errs here
End While
'===================== End call to function ===================

Shared Function Create_SQL_DataReader(ByVal sDate As String) As
SqlDataReader

Dim SQLConn As New SqlConnection(sConn)
Using SQLConn

' This Builds SQL String into "s" and uses passed in param
"sDate"
Dim s As String = BuildShiftSQL(sDate)

Dim Cmd = New SqlCommand(s, SQLConn)
SQLConn.Open()
Dim iDr As SqlDataReader = Cmd.ExecuteReader()
Return iDr
s = String.Empty
SQLConn.Close()
SQLConn.Dispose()
End Using
End Function

Error Text:
System.InvalidOperationException was unhandled
Message="Invalid attempt to Read when reader is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at WindowsApplication1.dataReaderDataSetForm.Main() in
C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Click to expand...
Click to expand...
 
P

Paul Ilacqua

I moved the Connection portion to the calling sub's activities and passed in
the SQL and a connection and it works. The current "fix" works but defeats
the purpose of having a funcion to build the reader.

Calling Sub

Dim SQLConn As New SqlConnection(sConn)
SQLConn.Open()
Dim dr As SqlDataReader = Create_SQL_DataReader("Select Top 10 *
From SQLParts", SQLConn)
While dr.Read
Console.WriteLine(dr(0) & vbTab & dr(1))
End While

----------------------------------------------------------------------------------------------------------------
Function Create_SQL_DataReader(ByVal sSQL As String, ByVal SQLConn As
SqlConnection) As SqlDataReader
Dim Cmd = New SqlCommand(sSQL, SQLConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function



Kerry Moorman said:
Paul,

Ignore my last post. The Return statement ends the function so the
connection is not being closed with the code after Return.

Sorry,

Kerry Moorman


Kerry Moorman said:
Paul,

After you return the data reader you are closing the connection:

Return iDr
s = String.Empty
SQLConn.Close()

But the data reader needs the connection to stay open until you have
finished reading the data.

An alternative is to create the data reader like this:

Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)

This will close the connection when the reader is eventually closed.

Kerry Moorman



Paul Ilacqua said:
All,
I'm trying to create a DataReader Function to call from within my
class
to open a simple datareader.
I have thoroughly read the Data Access Patterns and practices and do
not
believe in using canned code right off the bat. I want to rebuild the
canned
code so I understand it, then I may use it or use my own understood
version.

The error message is in Error Text: Message="Invalid attempt to Read
when
reader is closed."


'===================== This is call to function ===================
Dim dr As SqlDataReader = Create_SQL_DataReader("20060213")
While dr.Read() !!! Errs here
End While
'===================== End call to function ===================

Shared Function Create_SQL_DataReader(ByVal sDate As String) As
SqlDataReader

Dim SQLConn As New SqlConnection(sConn)
Using SQLConn

' This Builds SQL String into "s" and uses passed in param
"sDate"
Dim s As String = BuildShiftSQL(sDate)

Dim Cmd = New SqlCommand(s, SQLConn)
SQLConn.Open()
Dim iDr As SqlDataReader = Cmd.ExecuteReader()
Return iDr
s = String.Empty
SQLConn.Close()
SQLConn.Dispose()
End Using
End Function

Error Text:
System.InvalidOperationException was unhandled
Message="Invalid attempt to Read when reader is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at WindowsApplication1.dataReaderDataSetForm.Main() in
C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile,
Evidence
assemblySecurity, String[] args)
at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object
state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Click to expand...
Click to expand...
Click to expand...
 
P

Patrice

You could still create a private connection in your reader function (if this
is what you meant by defeating the purpose)...

--
Patrice

Paul Ilacqua said:
I moved the Connection portion to the calling sub's activities and passed
in the SQL and a connection and it works. The current "fix" works but
defeats the purpose of having a funcion to build the reader.

Calling Sub

Dim SQLConn As New SqlConnection(sConn)
SQLConn.Open()
Dim dr As SqlDataReader = Create_SQL_DataReader("Select Top 10 *
From SQLParts", SQLConn)
While dr.Read
Console.WriteLine(dr(0) & vbTab & dr(1))
End While

----------------------------------------------------------------------------------------------------------------
Function Create_SQL_DataReader(ByVal sSQL As String, ByVal SQLConn As
SqlConnection) As SqlDataReader
Dim Cmd = New SqlCommand(sSQL, SQLConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function



Kerry Moorman said:
Paul,

Ignore my last post. The Return statement ends the function so the
connection is not being closed with the code after Return.

Sorry,

Kerry Moorman


Kerry Moorman said:
Paul,

After you return the data reader you are closing the connection:

Return iDr
s = String.Empty
SQLConn.Close()

But the data reader needs the connection to stay open until you have
finished reading the data.

An alternative is to create the data reader like this:

Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)

This will close the connection when the reader is eventually closed.

Kerry Moorman



:

All,
I'm trying to create a DataReader Function to call from within my
class
to open a simple datareader.
I have thoroughly read the Data Access Patterns and practices and do
not
believe in using canned code right off the bat. I want to rebuild the
canned
code so I understand it, then I may use it or use my own understood
version.

The error message is in Error Text: Message="Invalid attempt to Read
when
reader is closed."


'===================== This is call to function ===================
Dim dr As SqlDataReader = Create_SQL_DataReader("20060213")
While dr.Read() !!! Errs here
End While
'===================== End call to function ===================

Shared Function Create_SQL_DataReader(ByVal sDate As String) As
SqlDataReader

Dim SQLConn As New SqlConnection(sConn)
Using SQLConn

' This Builds SQL String into "s" and uses passed in param
"sDate"
Dim s As String = BuildShiftSQL(sDate)

Dim Cmd = New SqlCommand(s, SQLConn)
SQLConn.Open()
Dim iDr As SqlDataReader = Cmd.ExecuteReader()
Return iDr
s = String.Empty
SQLConn.Close()
SQLConn.Dispose()
End Using
End Function

Error Text:
System.InvalidOperationException was unhandled
Message="Invalid attempt to Read when reader is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at WindowsApplication1.dataReaderDataSetForm.Main() in
C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20
at System.AppDomain.nExecuteAssembly(Assembly assembly,
String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile,
Evidence
assemblySecurity, String[] args)
at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object
state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Ilacqua

Patrice,
After an entire morning of work it seems to be the using statement that
was in the original post was the problem

Working version
=============================================================
Shared Function Create_SQL_DataReader(ByVal sSQL As String) As SqlDataReader
Dim MyConn As New SqlConnection(sConn)
MyConn.Open()
Dim Cmd = New SqlCommand(sSQL, MyConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function
=============================================================
Flawed Version
=============================================================
Shared Function Create_SQL_DataReader(ByVal sSQL As String) As SqlDataReader
Dim MyConn As New SqlConnection(sConn)
Using MyConn
MyConn.Open()
Dim Cmd = New SqlCommand(sSQL, MyConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Using
End Function
=============================================================
Thanks so much but I would still like to know why.....

Patrice said:
You could still create a private connection in your reader function (if
this is what you meant by defeating the purpose)...

--
Patrice

Paul Ilacqua said:
I moved the Connection portion to the calling sub's activities and passed
in the SQL and a connection and it works. The current "fix" works but
defeats the purpose of having a funcion to build the reader.

Calling Sub

Dim SQLConn As New SqlConnection(sConn)
SQLConn.Open()
Dim dr As SqlDataReader = Create_SQL_DataReader("Select Top 10 *
From SQLParts", SQLConn)
While dr.Read
Console.WriteLine(dr(0) & vbTab & dr(1))
End While

----------------------------------------------------------------------------------------------------------------
Function Create_SQL_DataReader(ByVal sSQL As String, ByVal SQLConn As
SqlConnection) As SqlDataReader
Dim Cmd = New SqlCommand(sSQL, SQLConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function



Kerry Moorman said:
Paul,

Ignore my last post. The Return statement ends the function so the
connection is not being closed with the code after Return.

Sorry,

Kerry Moorman


:

Paul,

After you return the data reader you are closing the connection:

Return iDr
s = String.Empty
SQLConn.Close()

But the data reader needs the connection to stay open until you have
finished reading the data.

An alternative is to create the data reader like this:

Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)

This will close the connection when the reader is eventually closed.

Kerry Moorman



:

All,
I'm trying to create a DataReader Function to call from within
my class
to open a simple datareader.
I have thoroughly read the Data Access Patterns and practices and do
not
believe in using canned code right off the bat. I want to rebuild the
canned
code so I understand it, then I may use it or use my own understood
version.

The error message is in Error Text: Message="Invalid attempt to Read
when
reader is closed."


'===================== This is call to function ===================
Dim dr As SqlDataReader = Create_SQL_DataReader("20060213")
While dr.Read() !!! Errs here
End While
'===================== End call to function ===================

Shared Function Create_SQL_DataReader(ByVal sDate As String) As
SqlDataReader

Dim SQLConn As New SqlConnection(sConn)
Using SQLConn

' This Builds SQL String into "s" and uses passed in
param
"sDate"
Dim s As String = BuildShiftSQL(sDate)

Dim Cmd = New SqlCommand(s, SQLConn)
SQLConn.Open()
Dim iDr As SqlDataReader = Cmd.ExecuteReader()
Return iDr
s = String.Empty
SQLConn.Close()
SQLConn.Dispose()
End Using
End Function

Error Text:
System.InvalidOperationException was unhandled
Message="Invalid attempt to Read when reader is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at WindowsApplication1.dataReaderDataSetForm.Main() in
C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20
at System.AppDomain.nExecuteAssembly(Assembly assembly,
String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile,
Evidence
assemblySecurity, String[] args)
at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object
state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Click to expand...
Click to expand...
Click to expand...
 
M

Marina Levit [MVP]

Because by definition the Using statement calls Dispose on the object you
are 'using' at the end of the statement.

In the implementation of Dispose for SqlConnection, it calls Close. Which
means your connection is closed prior to the method finishing - meaning the
datareader you are returning has had its connection closed. And since
datareader require an open connection, the datareader being returned is
unsable.

Using MyConn
....
Return iDr
End Using

Is more or less the equivalent of:

Try

...
Return iDr
Finally
MyConn.Dispose()
End Try

Paul Ilacqua said:
Patrice,
After an entire morning of work it seems to be the using statement that
was in the original post was the problem

Working version
=============================================================
Shared Function Create_SQL_DataReader(ByVal sSQL As String) As
SqlDataReader
Dim MyConn As New SqlConnection(sConn)
MyConn.Open()
Dim Cmd = New SqlCommand(sSQL, MyConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function
=============================================================
Flawed Version
=============================================================
Shared Function Create_SQL_DataReader(ByVal sSQL As String) As
SqlDataReader
Dim MyConn As New SqlConnection(sConn)
Using MyConn
MyConn.Open()
Dim Cmd = New SqlCommand(sSQL, MyConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Using
End Function
=============================================================
Thanks so much but I would still like to know why.....

Patrice said:
You could still create a private connection in your reader function (if
this is what you meant by defeating the purpose)...

--
Patrice

Paul Ilacqua said:
I moved the Connection portion to the calling sub's activities and passed
in the SQL and a connection and it works. The current "fix" works but
defeats the purpose of having a funcion to build the reader.

Calling Sub

Dim SQLConn As New SqlConnection(sConn)
SQLConn.Open()
Dim dr As SqlDataReader = Create_SQL_DataReader("Select Top 10 *
From SQLParts", SQLConn)
While dr.Read
Console.WriteLine(dr(0) & vbTab & dr(1))
End While

----------------------------------------------------------------------------------------------------------------
Function Create_SQL_DataReader(ByVal sSQL As String, ByVal SQLConn As
SqlConnection) As SqlDataReader
Dim Cmd = New SqlCommand(sSQL, SQLConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function



message Paul,

Ignore my last post. The Return statement ends the function so the
connection is not being closed with the code after Return.

Sorry,

Kerry Moorman


:

Paul,

After you return the data reader you are closing the connection:

Return iDr
s = String.Empty
SQLConn.Close()

But the data reader needs the connection to stay open until you have
finished reading the data.

An alternative is to create the data reader like this:

Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)

This will close the connection when the reader is eventually closed.

Kerry Moorman



:

All,
I'm trying to create a DataReader Function to call from within
my class
to open a simple datareader.
I have thoroughly read the Data Access Patterns and practices and do
not
believe in using canned code right off the bat. I want to rebuild
the canned
code so I understand it, then I may use it or use my own understood
version.

The error message is in Error Text: Message="Invalid attempt to Read
when
reader is closed."


'===================== This is call to function ===================
Dim dr As SqlDataReader = Create_SQL_DataReader("20060213")
While dr.Read() !!! Errs here
End While
'===================== End call to function ===================

Shared Function Create_SQL_DataReader(ByVal sDate As String) As
SqlDataReader

Dim SQLConn As New SqlConnection(sConn)
Using SQLConn

' This Builds SQL String into "s" and uses passed in
param
"sDate"
Dim s As String = BuildShiftSQL(sDate)

Dim Cmd = New SqlCommand(s, SQLConn)
SQLConn.Open()
Dim iDr As SqlDataReader = Cmd.ExecuteReader()
Return iDr
s = String.Empty
SQLConn.Close()
SQLConn.Dispose()
End Using
End Function

Error Text:
System.InvalidOperationException was unhandled
Message="Invalid attempt to Read when reader is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at WindowsApplication1.dataReaderDataSetForm.Main() in
C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20
at System.AppDomain.nExecuteAssembly(Assembly assembly,
String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile,
Evidence
assemblySecurity, String[] args)
at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object
state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Ilacqua

OK....
I'm beginning to get the problem and the why.... This is a perfect example
of the learning process that cannot be found in books, and bosses do not
understand sometimes need to be done. This is a "mistake" I will never make
again, or if I do It's my fault.

Thanks to all for thier valuable input

Paul Ilacqua - VB6 Refugee

Marina Levit said:
Because by definition the Using statement calls Dispose on the object you
are 'using' at the end of the statement.

In the implementation of Dispose for SqlConnection, it calls Close. Which
means your connection is closed prior to the method finishing - meaning
the datareader you are returning has had its connection closed. And since
datareader require an open connection, the datareader being returned is
unsable.

Using MyConn
...
Return iDr
End Using

Is more or less the equivalent of:

Try

...
Return iDr
Finally
MyConn.Dispose()
End Try

Paul Ilacqua said:
Patrice,
After an entire morning of work it seems to be the using statement that
was in the original post was the problem

Working version
=============================================================
Shared Function Create_SQL_DataReader(ByVal sSQL As String) As
SqlDataReader
Dim MyConn As New SqlConnection(sConn)
MyConn.Open()
Dim Cmd = New SqlCommand(sSQL, MyConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function
=============================================================
Flawed Version
=============================================================
Shared Function Create_SQL_DataReader(ByVal sSQL As String) As
SqlDataReader
Dim MyConn As New SqlConnection(sConn)
Using MyConn
MyConn.Open()
Dim Cmd = New SqlCommand(sSQL, MyConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Using
End Function
=============================================================
Thanks so much but I would still like to know why.....

Patrice said:
You could still create a private connection in your reader function (if
this is what you meant by defeating the purpose)...

--
Patrice

"Paul Ilacqua" <[email protected]> a écrit dans le message de %[email protected]...
I moved the Connection portion to the calling sub's activities and
passed in the SQL and a connection and it works. The current "fix" works
but defeats the purpose of having a funcion to build the reader.

Calling Sub

Dim SQLConn As New SqlConnection(sConn)
SQLConn.Open()
Dim dr As SqlDataReader = Create_SQL_DataReader("Select Top 10 *
From SQLParts", SQLConn)
While dr.Read
Console.WriteLine(dr(0) & vbTab & dr(1))
End While

----------------------------------------------------------------------------------------------------------------
Function Create_SQL_DataReader(ByVal sSQL As String, ByVal SQLConn As
SqlConnection) As SqlDataReader
Dim Cmd = New SqlCommand(sSQL, SQLConn)
Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return iDr
End Function



message Paul,

Ignore my last post. The Return statement ends the function so the
connection is not being closed with the code after Return.

Sorry,

Kerry Moorman


:

Paul,

After you return the data reader you are closing the connection:

Return iDr
s = String.Empty
SQLConn.Close()

But the data reader needs the connection to stay open until you have
finished reading the data.

An alternative is to create the data reader like this:

Dim iDr As SqlDataReader =
Cmd.ExecuteReader(CommandBehavior.CloseConnection)

This will close the connection when the reader is eventually closed.

Kerry Moorman



:

All,
I'm trying to create a DataReader Function to call from within
my class
to open a simple datareader.
I have thoroughly read the Data Access Patterns and practices and
do not
believe in using canned code right off the bat. I want to rebuild
the canned
code so I understand it, then I may use it or use my own understood
version.

The error message is in Error Text: Message="Invalid attempt to
Read when
reader is closed."


'===================== This is call to function ===================
Dim dr As SqlDataReader = Create_SQL_DataReader("20060213")
While dr.Read() !!! Errs here
End While
'===================== End call to function ===================

Shared Function Create_SQL_DataReader(ByVal sDate As String) As
SqlDataReader

Dim SQLConn As New SqlConnection(sConn)
Using SQLConn

' This Builds SQL String into "s" and uses passed in
param
"sDate"
Dim s As String = BuildShiftSQL(sDate)

Dim Cmd = New SqlCommand(s, SQLConn)
SQLConn.Open()
Dim iDr As SqlDataReader = Cmd.ExecuteReader()
Return iDr
s = String.Empty
SQLConn.Close()
SQLConn.Dispose()
End Using
End Function

Error Text:
System.InvalidOperationException was unhandled
Message="Invalid attempt to Read when reader is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at WindowsApplication1.dataReaderDataSetForm.Main() in
C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20
at System.AppDomain.nExecuteAssembly(Assembly assembly,
String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile,
Evidence
assemblySecurity, String[] args)
at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object
state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I assign a null value to a SqlDataReader? 4
System.InvalidCastException when calling a COM function 5
Passing SQLDataReader through a function 6
login routine with information returned from database 3
Have function return SqlDatReader and then read results. How? 4
export datagridview to text file and import text file to sql servertable on the fly 2
How to read output parameter returned from a function as sqlDataReader 2
Using excel XIRR FUNCTION IN A VB 2005 program 1

Top