locked
Internal connection fatal error RRS feed

  • Question

  • User-1401557505 posted

    I have a web app with a separate DLL for Data access.
     

    This is the data access code:
     

    Public Shared Sub InitiateDAL()
     

            Try
                _sqlConn = New SqlConnection(ConfigurationManager.ConnectionStrings("cnConn").ToString)
     

            Catch ex As Exception
                _ErrorMessage = ex.Message
                LogError(_Class & ":" & _Function & ": " & _ErrorMessage)
            Finally
                '_sqlConn.Close()
            End Try
     

        End Sub
     

    I also have separate DLL’s for each area of functionality.
     

    These typically look like this:
     

     

        Public Function LocationTreeFiltered(ByVal _Location_ID As Int32, ByVal _Specialty As String, ByVal _User_GUID As Guid) As DataTable
     

            _Class = "CoreData"
            _Function = "LocationTreeFiltered()"
     

            InitiateDAL()
            Dim sqlCmd As New SqlCommand()
            Dim sqlDa As New SqlDataAdapter(sqlCmd)
            Dim dt As New DataTable
     

            Try
                With sqlCmd
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "LocationTreeFiltered"
                    .Connection = _sqlConn
                    .Parameters.Add("@Location_ID", SqlDbType.Int).Value = _Location_ID
                    .Parameters.Add("@Specialty", SqlDbType.VarChar, 40).Value = _Specialty
                    .Parameters.Add("@User_GUID", SqlDbType.UniqueIdentifier).Value = _User_GUID
                    .Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                End With
     

                sqlDa.Fill(dt)
                Return dt
     

            Catch ex As Exception
                _ErrorMessage = ex.Message
                LogError(_Class & ":" & _Function & ": " & _ErrorMessage)
                Return Nothing
            End Try
     

        End Function
     

    I use the above in my aspx.vb pages like this:
     

               Dim l As New CoreData
               Dim l_dt As DataTable = l.LocationTreeFiltered(Location_ID, Specialty, User_GUID)
     

     

     

    On test everything works OK, but when It goes into production I get data errors.
     

    These errors vary from fields not being in the dataset, to missing table(0) I was stumped until I finally found an error that said - Internal connection fatal error
     

    I’ve googled this and nearly all description are what I have going on.
     

    If you look at my data class I’m not explicitly closing the data connection, so I changed the try catch to include a finally to look like this:
     

            Try
    …..
     

            Catch ex As Exception

            Finally
                _sqlConn.Close()
                'sqlCmd = Nothing
    End Try
     

    But this made thing worse, I also tried with the Nothing commented out but it was still worse than my original.
     

    I use Telerik controls to display data and initially went to them for help, but they can find anything wrong in the way I’ve coded the controls.
     

    Has anyone seen anything like this before?
     

    Andy
     
     

    Tuesday, February 8, 2011 10:28 AM

Answers

  • User1105131773 posted

    Try to use the using statement where possible when using data readers, connections and commands

    Private Function GetConnection() as SqlConnection

    'Code to return a SQL connection here

    End Function

    Once you have that your code should then look similar to...

    Using connection as SqlConnection = GetConnection()

    Using command as new SqlCommand

    command.CommandType = CommandType.StoredProcedure

    ' Confgure the command properties here and add parameters

    Using adapter as new SqlDataAdapter(command)

    adapter.Fill(dt)

    End Using

    End using

    End Using


    All of the data access objects being used support IDisposable which means you can wrap them nicely up in the using statements and the connection and all dependents will be closed and cleaned up automatically.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 8, 2011 10:40 AM

All replies

  • User1105131773 posted

    Try to use the using statement where possible when using data readers, connections and commands

    Private Function GetConnection() as SqlConnection

    'Code to return a SQL connection here

    End Function

    Once you have that your code should then look similar to...

    Using connection as SqlConnection = GetConnection()

    Using command as new SqlCommand

    command.CommandType = CommandType.StoredProcedure

    ' Confgure the command properties here and add parameters

    Using adapter as new SqlDataAdapter(command)

    adapter.Fill(dt)

    End Using

    End using

    End Using


    All of the data access objects being used support IDisposable which means you can wrap them nicely up in the using statements and the connection and all dependents will be closed and cleaned up automatically.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 8, 2011 10:40 AM
  • User1105131773 posted

    Also, if you dont like the nested using statements these can be easily configured into a single line, for example

    Using connection as SqlConnection = GetConnection(), command As New SqlClient.SqlCommand, adapter As New SqlClient.SqlDataAdapter(command)

                command.Parameters.Add("@blah")

    'Other bits here

                adapter.Fill(ds)

            End Using


    Tuesday, February 8, 2011 10:43 AM
  • User-1401557505 posted

    Sorry, Forgot to say thanks.

    Andy

    Thursday, February 17, 2011 2:38 AM