for some reason, my query's connection is crashing some of the time and then the connection is not freed.  I'm using the odbc driver (mysql.data.dll).

 I open the connection with using so I was thinking it should always automatically close.

 any thoughts?

<add name="OTRSConnection" connectionString="server=localhost ; username=peter; password=xxxx; database=OTRS; pooling=false"

providerName="System.Data.Odbc" />

@"

SELECT ...

cmd.Parameters.Add("?startDate", MySqlDbType.Datetime).Value = startDate;

try

DateTime ticketDate = Convert.ToDateTime(ticketDateString);

int totalTickets = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);

 Hi

It doesnt close the connection automatically you have to close the connection

conn.Close();

I have also used mysql with odbc its fine

are you implying that the using clause for mysqlconnection is not implemented properly and does not automatically close the connection? that would be hard to believe.

Hello,

Have you tried closing the reader after your while clause is closed?

while (reader.Read()) 
{

string ticketDateString = reader.IsDBNull(0) ? string.Empty : reader.GetString(0); 
DateTime ticketDate = Convert.ToDateTime(ticketDateString);

int userId = reader.IsDBNull(1) ? -1 : reader.GetInt32(1); 
int totalTickets = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);

Ticket ticket = new Ticket(totalTickets, ticketDate, userId,string.Empty); 
listTicket.Add(ticket);

}

reader.close()

 or you may want to try to pur reader.close in a 'finaly' clause of your 'try catch' .

There is a difference between disposing the reader and closing it. And I'm also guessing you have to close the connection after you close the reader. Otherwise you would get the error indicating that it is in use by a reader.

May be that is what causing the trouble.

Regards,

So, I left out a couple lines of code that showed I am opening the reader with the using syntax so just like mysqlconnection, dispose is being called.

What is the difference between closing the reader and calling dispose?

A disposed reader cannot be used after disposal unless you rewrite the line to create a new object from it the line: reader= new datareader();

But if you close the reader, you can open it again anytime without requiring to create the reader object again.

for ex.

reader.close()

reader= yoursqlcommand.executereader();