RE: SqlDataReader Function From a VB6 refugee

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" wrote:

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()



.

Relevant Pages

  • Re: SqlDataReader Function From a VB6 refugee
    ... Return iDr ... Shared Function Create_SQL_DataReader(ByVal sSQL As String) As ... Dim MyConn As New SqlConnection ... Dim iDr As SqlDataReader = ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: SqlDataReader Function From a VB6 refugee
    ... After you return the data reader you are closing the connection: ... But the data reader needs the connection to stay open until you have ... Shared Function Create_SQL_DataReader(ByVal sDate As String) As ... Dim SQLConn As New SqlConnection ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Extract Single Record from Dataset filled from SP Output
    ... I cannot pass a Data Reader back as the connection is closed. ... I thought i would load the data into an array instead, ... strStoredProcedure As String, ByVal strSP_InputParam1 As String) As String ... Dim objSQLCn As New SqlConnection ...
    (microsoft.public.dotnet.languages.vb)
  • Re: sql data to string
    ... That is because you are essentially returning a record set. ... Additionally, you have a connection leak, since the data reader never gets ... > But when I fill the string with the ddsql reader it gives me a true value ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Loop through datareader after binding to repeater control
    ... data reader is read-only, forward-only. ... you need to requery database in order to loop ... use OleDbdataAdapter to fill a dataTable and loop through the ... Dim spApproverList As OleDb.OleDbDataReader ...
    (microsoft.public.dotnet.framework.aspnet)