Connection Unexpected Terminatedly ??

[Ranthalion]

Hi, I was wondering if anyone could help me with a problem I'm having. I'm trying to connect to a remote database and just submit some select queries. It seems to work fine after it gets going, but I always get an exception stating that the Connection was unexpectedly terminated when I submit the first query. If I just submit the query again, it all works fine after that. Any ideas on what's going on?
Here is my source code:

//some of the needed variables
private MySqlConnection conn;
private DataTable data;
private MySqlDataAdapter da;

//skip some unimportant stuff
private void connectBtn_Click(object sender, System.EventArgs e)
{
if (conn != null)
conn.Close();

string connStr = String.Format("server={0};user id={1}; password=2}; database=ranthali_osc1; pooling=false",server.Text, userid.Text, password.Text );

try
{
this.Cursor = Cursors.WaitCursor;
this.databaseExplorerStatusStripPanel.Text= "Connecting to Database.";
conn = new MySqlConnection( connStr );
conn.Open();
this.databaseExplorerStatusStripPanel.Text = "Connected to " + server.Text;
this.Cursor = Cursors.Arrow;
}
catch (MySqlException ex)
{
this.databaseExplorerStatusStripPanel.Text = "Unable to connect.";
MessageBox.Show( "Error connecting to the server: " + ex.Message );
}
}

private void btnSubmit_Click(object sender, EventArgs e)
{
try
{
data = new DataTable();

da = new MySqlDataAdapter(Query.Text.ToString(), conn);

//MessageBox.Show(da.ToString());
da.Fill(data);

dataGrid.DataSource = data;
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

}

private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (conn!=null)
{
conn.Close();
}
}


By the way, I'm coding in C# on XP. I'm connecting to a mySQL database hosted by my webhosting provider at Acehost.net. Anyway, any ideas you all have would be great. I've been trying to figure this problem out for a couple of weeks now, and I'm stuck...

-Ranthalion

[Krzemo]

Try to play with timeouts...

Best regards,
Krzemo.

[Ranthalion]

Thanks for the idea. I'll give that a try when I get home next week. I hadn't even thought that the connection could be timing out before I even make the first call. I'll also try modifying the program to open the connection, make the query, and close the connection to see if that fixes the problem. Now I've got some ideas again, and hopefully, one of them will work. I'll post back when I've either figured it out, or run out of ideas again.

Once again, thanks for the idea. I gues that was all I needed to get going on it again.

-Ranthalion

[Michaelw]

Just a guess, but...

In your connectBtn_Click() you instantiate object conn and open it, but never close it in that method you should and dispose of the object and re-instantiate it on other methods.

Then when you click connectSubmit_Click() you instaniate a DataAdapter and invoke Fill(). then exit that method. The DataAdapter will open any closed connection and but will not close an open one ( which if process timing is off you may have from teh connBtn_Click() ).

When you run the query a second time do you click the connect button and call the connectBTn_Click() again? I am guessing not.

In that case I am thinking in your first iteration you call connectBtn_Click and open a connection, then you call connectSubmit_Click whccih starts your command query. Sometime during that proces the conn object from the now outscoped connectBtn_Click is disposed and the connection closed.

Try testing the state of the connection object ( see below ) inside the connectBtn_Click if your are doing it to check if the server is up and open it if it is "Closed" . Then it when it is "Open" ( when you are done in method ).

Then in your form close you can use the same state connection checking code to close the connection even though you really don't have to since the form closing will do it for you.


In connectBtn_Click():
try {

if ( conn.State.ToString() == "Closed" )
conn.Open() ;
...
...

if ( conn.State.ToString() == "Open" )
conn.Close() ;

}

catch( SqlException e )
{}


Maybe, maybe not , I maybe off base.

[Ranthalion]

Thanks, that all works perfectly. I open the connection to verify login information, then I close the connection. After that, I just submit the query without having a previously open connection.