Published 4/30/2007 1:45:08 AM  in  MySQL

Considering the table layout like this:

CREATE TABLE `city` (                                                                                            
          `city_id` smallint(5) unsigned NOT NULL auto_increment,                                                        
          `city` varchar(50) NOT NULL,                                                                                   
          `country_id` smallint(5) unsigned NOT NULL,                                                                    
          `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,                        
          PRIMARY KEY  (`city_id`),                                                                                      
          KEY `idx_fk_country_id` (`country_id`),                                                                        
          CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE  
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

the C# code for inserting looks like this:

        public void InsertMySQL(string city, int country_id)
        {
            MySql.Data.MySqlClient.MySqlConnection oConn =
                new MySql.Data.MySqlClient.MySqlConnection("Database=sakila;Data Source=192.168.10.4;User id=stefan;Password=pekka");
            oConn.Open();


            MySql.Data.MySqlClient.MySqlCommand command = 
                new MySql.Data.MySqlClient.MySqlCommand("insert into city(city, country_id ) select ?city, ?country_id", oConn);

            command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?city", MySql.Data.MySqlClient.MySqlDbType.VarChar, 50));
            command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?country_id", MySql.Data.MySqlClient.MySqlDbType.Int16));
            command.Parameters[0].Value = city;
            command.Parameters[1].Value = country_id;
            command.ExecuteNonQuery();

            oConn.Close();

        }