Problems with Update and MySqlDataAdapter

[Kenneth P]

Hi,

I've been using MSSQL2k as the DB for several years now and I have a few
apps where I do some changings in the dataset and need to update the
underlying db and that I do with the sqlcommandbuilder object and the
sqldataadapter.

I have a sqlconnection object, a sqldatadapter object and a dataset filled
with data from the dataadapter. Then I iterate trhough the dataset updating
some values and at last I update the db. For this to success you must have a
primary key in the datadapter (table)

Now I'm trying to do the same with MySql 4.1.10b db and Connector.net and am
getting error message saying

*******************************************
Connection must be valid and open
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Connection must be
valid and open

Source Error:
Line 634: 'Update dataset & grid w new values
Line 635: daBuilder = New MySqlCommandBuilder(daRes)
Line 636: daRes.Update(dsRes, "Result")
Line 637: grid.DataSource = dsRes
Line 638: grid.DataBind()
********************************************

It stops on line 636 with this error message, works very good with exactly
the same code but sql objects with sqlerv2k

Any ideas?

TIA

Kenneth P

[Jason Bentley]

Kenneth, I do not see the code where you are opening your connections
but this code can check to see if it is open and if it is not, it will
open it for you:
if(yourConnection.State != ConnectionState.Open)
{
yourConnection.Open();
}

Jason Bentley
http://geekswithblogs.net/jbentley

[Kenneth P]

Hi Jason,

I'm 100% sure all connections are closed when I try to update the db, but it
has been no problem with sqlserv2k for three years now, why is it a problem
with mysql?

I've added some code to open the connection with three lines, then the old
code that worked quite well and then closing the connection again.

conStrDiskoweb = ConfigurationSettings.AppSettings("conStrDiskoweb" )
connDiskoweb = New MySqlConnection(conStrDiskoweb)
connDiskoweb.Open()

daBuilder = New MySqlCommandBuilder(daRes)
daRes.Update(dsRes, "Result")
grid.DataSource = dsRes
grid.DataBind()

connDiskoweb.Close()

I still get the exactly same eroor message.

Kenneth

"Jason Bentley" wrote:

Kenneth, I do not see the code where you are opening your connections
but this code can check to see if it is open and if it is not, it will
open it for you:
if(yourConnection.State != ConnectionState.Open)
{
yourConnection.Open();
}

Jason Bentley
http://geekswithblogs.net/jbentley

[Jason Bentley]

Hmmm, I dunno. In theory, OleDb and SqlClient should function the same.
Jason Bentley
http://geekswithblogs.net/jbentley