Paul
Fri Mar 31 10:45:47 CST 2006
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 [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" <pilacqu2@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" <scribe@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" <pilacqu2@twcny.rr.com> a écrit dans le message de news:
>>> %23hK6OWNVGHA.4660@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" <KerryMoorman@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()
>>>>>> >
>>>>>> >
>>>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
>