[Mono-aspnet-list] Auto SQL generation during Update requires a valid SelectCommand

[jan van der watt]

Hi,

This error has been reported in this forum (in 2006 and 2008), but no
apparent resolution has been provided, so I am re-posting with more
information.

I must be missing something really obvious so I would really appreciate a
pointer in the right direction as updating/inserting is obviously essential
to any web site :-) and posting a definite/detailed resolution would also
help other beginners.

I get this error during any and all attempts to update/add a record in my
MySQL database on openSUSE 11 using MONO 2.2 (or 1.9) with MySql Connector
5.x (or 6.0).

Caught an exception:
Message: [Auto SQL generation during Update requires a valid SelectCommand.]
Occurred: [  at System.Data.Common.DbDataAdapter.Update
(System.Data.DataRow[] dataRows, System.Data.Common.DataTableMapping
tableMapping) [0x00000] ]
In: [Int32 Update(System.Data.DataRow[],
System.Data.Common.DataTableMapping)]

Note: The code works fine on Windows XP using the same connectors and
connection strings to the same MySQL database.

After many many variations and attempts, all failing, I took the example
from the MySQL Connector Manual (I only added the connection string/table
name and error trapping):

        String myConnection =
"Server=www.xxxyyyzzz.org;Uid=demo;Pwd=xxxyyyzzz;Database=demo;";
        String mySelectQuery = "SELECT * FROM Tenant;";
        String myTableName = "Tenant";

        MySqlConnection myConn = new MySqlConnection(myConnection);
        MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
        myDataAdapter.SelectCommand = new MySqlCommand(mySelectQuery,
myConn);
        MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);

        try {

            myConn.Open();

            DataSet ds = new DataSet();
            myDataAdapter.Fill(ds, myTableName);

            //code to modify data in DataSet here
            DataRow row = ds.Tables[myTableName].Rows[0];
            row.BeginEdit();
            row["Tenant"] += "-";      // very simple change
            row.EndEdit();

            //Without the MySqlCommandBuilder this line would fail
            int updated_records = myDataAdapter.Update(ds, myTableName);

            Response.Write(Utilities.variable("updated records",
updated_records));
        } catch (Exception ex) {
            Response.Write(Utilities.format_exception(ex));
        }

        myConn.Close();


The schema for Tenant is:
CREATE TABLE `Tenant` (
  `TenantID` int(11) NOT NULL auto_increment,
  `Tenant` varchar(50) NOT NULL,
  PRIMARY KEY  (`TenantID`),
  KEY `TenantID` (`TenantID`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

I have two openSUSE boxes, one built from the openSUSE 11 distro DVD, and
another, a Linode (virtual machine) set up from the Linode pre-installed
images. Both machines give the same problem.

I installed using YaST (so, I did not compile from source) trying both:
ftp://ftp.novell.com/pub/mono/download-stable/openSUSE_11.1/i586/ and
http://download.opensuse.org/update/11.0/rpm/

Thanks in advance.

Jan
-- 
View this message in context: http://www.nabble.com/Auto-SQL-generation-during-Update-requires-a-valid-SelectCommand-tp22550410p22550410.html
Sent from the Mono - ASP.NET mailing list archive at Nabble.com.