SQL Connection problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an app that uses SQL with access. I've been fine through the whole thing until I tried using the UPDATE command. When it gets to ExecuteNonQuery() it tells me that there is no open connection. A few lines before it though I can access the database. I went through the query builder and it generated an update statement. Where am I going wrong? Here's my code:

oleDbDataAdapter1.SelectCommand.CommandText = "SELECT Count FROM Choices WHERE Title = '" + title + "' AND Choice = '" + choice + "'";
oleDbDataAdapter1.Fill(dataSet1);

DataTable dataTable = dataSet1.Tables[0];

int count = Int32.Parse(dataTable.Rows[0][4].ToString());
count++;

oleDbDataAdapter1.UpdateCommand.CommandText = "UPDATE Choices SET Count = '" + count.ToString() + "' WHERE Title = '" + title + "' AND Choice = '" + choice + "'";
oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
 
If my memory serves me right, the Fill command opens a connection receives
the data and immediatly closes the connection.

You have to reopen the connection, execute, and close it manually.
 
I have an app that uses SQL with access. I've been fine through the
whole thing until I tried using the UPDATE command. When it gets to
ExecuteNonQuery() it tells me that there is no open connection. A few
lines before it though I can access the database. I went through the
query builder and it generated an update statement. Where am I going
wrong? Here's my code:
Click to expand...

Fill opens the Connection by its own.

for the UpdateCommand you can do something like this:

try
{
oleDbDataAdapter1.UpdateCommand.Connection.Open();
oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
}
catch(Exception e)
{
}
finally
{
oleDbDataAdapter1.UpdateCommand.Connection.Close();
}
 
Back
Top