|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SqlDataReader Function From a VB6 refugeeI'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() 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)... -- Patrice "Paul Ilacqua" <pilac***@twcny.rr.com> a écrit dans le message de news: eKCdmWMVGHA.5***@TK2MSFTNGP10.phx.gbl...Show quote > 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() > > 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 Show quote "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() > > > 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 Show quote "Kerry Moorman" wrote: > 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() > > > > > > 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 Show quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message news:9D2DBF41-E596-4A06-8551-6F6E17DBDB11@microsoft.com... > 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" wrote: > >> 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() >> > >> > >> > You could still create a private connection in your reader function (if this
is what you meant by defeating the purpose)... -- Patrice "Paul Ilacqua" <pilac***@twcny.rr.com> a écrit dans le message de news: %23hK6OWNVGHA.4***@tk2msftngp13.phx.gbl...Show quote >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" <KerryMoor***@discussions.microsoft.com> wrote in message > news:9D2DBF41-E596-4A06-8551-6F6E17DBDB11@microsoft.com... >> 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" wrote: >> >>> 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() >>> > >>> > >>> > > > 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..... Show quote "Patrice" <scr***@chez.com> wrote in message news:uWARnmNVGHA.5364@tk2msftngp13.phx.gbl... > You could still create a private connection in your reader function (if > this is what you meant by defeating the purpose)... > > -- > Patrice > > "Paul Ilacqua" <pilac***@twcny.rr.com> a écrit dans le message de news: > %23hK6OWNVGHA.4***@tk2msftngp13.phx.gbl... >>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" <KerryMoor***@discussions.microsoft.com> wrote in message >> news:9D2DBF41-E596-4A06-8551-6F6E17DBDB11@microsoft.com... >>> 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" wrote: >>> >>>> 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() >>>> > >>>> > >>>> > >> >> > > 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 Show quote "Paul Ilacqua" <pilac***@twcny.rr.com> wrote in message news:udJNyyNVGHA.5332@tk2msftngp13.phx.gbl... > 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" <scr***@chez.com> wrote in message > news:uWARnmNVGHA.5364@tk2msftngp13.phx.gbl... >> You could still create a private connection in your reader function (if >> this is what you meant by defeating the purpose)... >> >> -- >> Patrice >> >> "Paul Ilacqua" <pilac***@twcny.rr.com> a écrit dans le message de news: >> %23hK6OWNVGHA.4***@tk2msftngp13.phx.gbl... >>>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" <KerryMoor***@discussions.microsoft.com> wrote in >>> message news:9D2DBF41-E596-4A06-8551-6F6E17DBDB11@microsoft.com... >>>> 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" wrote: >>>> >>>>> 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() >>>>> > >>>>> > >>>>> > >>> >>> >> >> > > 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 Show quote "Marina Levit [MVP]" <someone@nospam.com> wrote in message news:eX%23oT3NVGHA.5760@TK2MSFTNGP10.phx.gbl... > 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" <pilac***@twcny.rr.com> wrote in message > news:udJNyyNVGHA.5332@tk2msftngp13.phx.gbl... >> 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" <scr***@chez.com> wrote in message >> news:uWARnmNVGHA.5364@tk2msftngp13.phx.gbl... >>> You could still create a private connection in your reader function (if >>> this is what you meant by defeating the purpose)... >>> >>> -- >>> Patrice >>> >>> "Paul Ilacqua" <pilac***@twcny.rr.com> a écrit dans le message de news: >>> %23hK6OWNVGHA.4***@tk2msftngp13.phx.gbl... >>>>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" <KerryMoor***@discussions.microsoft.com> wrote in >>>> message news:9D2DBF41-E596-4A06-8551-6F6E17DBDB11@microsoft.com... >>>>> 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" wrote: >>>>> >>>>>> 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() >>>>>> > >>>>>> > >>>>>> > >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics