RE: SqlDataReader Function From a VB6 refugee

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



.

Relevant Pages

  • Re: ADO connections question
    ... Function CreateADOObjects(ConnectionString as string) ... you'll see this error if the connection has not been ... I have a specific login form that calls the dbLogin function as shown ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ADO connections question
    ... I have a module which creates the ADO connection object to validates the ... Function dbLogin(txtUser As String, txtPword As String, txtServer As String, ... I have a specific login form that calls the dbLogin function as shown below: ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • DTS Transformation Data Task Errors
    ... Dim oIniFile As New IniFile ... Dim sServerName As String = oIniFile.GetString("Source Connection", ... ;ServerName* - String value representing the name or ip address of the ...
    (microsoft.public.sqlserver.dts)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
Loading