I get this SQL A transport-level error, but not all the time

Question

I try to query data from the database and sometimes I get this error in my log:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The handle is invalid.) The code is inside a try-catch block. What is weird is that i do not get this error all the time. The catch block does get executed.

However, whether i get the error or not, the Drop Down Lists i am trying to populate do not get populated. It seems like there is no data being returned from the query.

I am making a connection to the database, the data is in there, i can open a connection and run queries to the database. The database is on the same machine as where im running the code from.

Also, the code works when i connect to the database on the server, but i am trying to make a local database to work when i dont have connection to the server. I just have to make a change to the connection string when i want to run from the local database.

Here is the function that queries the database

public static void QueryDB(string query)
    {
        cmd = new SqlCommand();
        cmd.CommandText = query;
        cmd.Connection = MyConn;
        if (dr != null) dr.Dispose();
        dr = cmd.ExecuteReader();
    }

Here is the connection string to connect to the database:

MyConn = new SqlConnection("Data Source=tcp:localhost; Database=EscalationManagementSystem; Integrated Security=true;");

Is there a setting that is wrong or could this be a bug in the code.

Thanks in advance.

Solution 1

Such an error usually appears when your connection to the instance of the Sql server is reset.

The cause maybe due to reset of the network connection.

Other cause might be:

You request the SQL Server and receive the data from the Connection pool. Now something bad happens to the SQL Server (service restart) and you make a postback to get data.

Check to make sure that your connection does not break between subsequent requests.

Hope this helps.

Solution 2

I think this is a bug in your code. I don't think you should have a single SqlDataReader and SqlCommand that is shared by all calls to the database.

Instead, you should instantiate a new SqlDataReader and SqlCommand for each call to the database, and dispose (and close) them when the application is finished with that specific SQL call.

My guess as to what is happening in your situation would be that the SqlDataReader or SqlCommand is being pulled out from under you by another call to the database, and that the issue is intermittent because it's a race condition.

Solution 3

This problem is due to the fact that you have not released your database connection. Just add a Finally statement in your code and add conn.Close() and conn.Dispose(). I am sure it will work fine.

finally 
{
    cmd.Connection.Close();
    cmd.Connection.Dispose();
}