-1

Hello everyone. I have a question here, that to me seems there must be a way to do what I want in the way I want :)

I have a thing that updates a Zen Cart DB and adds products to it.

Ok everything works the way it is now, but It close the connection to the DB after each item, is there a way to prevent this?

Little background and code

I originally tried to just null out my command but afte the first one I would get "There is already an open DataReader associated with this Connection which must be closed first."

so I searched around the internet and found something where they set the dataReader to close the connection, At that point it works, but as I said before, it disconnects and has to reconnect every item.

here is the code with before and after. any ideas would be great.

//** = points of interest

the SQL statement is 3 INSERT statements hence why i didn't have a DataReader Object originally

private static void AddZenCartItem(Product item)
        {
            Log.WriteLine("Im adding "+item.Description+" to the zencart.");

            String sql;

            //Now lets put some SQL in that String
            sql = //BIG LONG SQL STATEMENT 
            
            //Reopen the connection just incase it closed
            if (MyConn.State != ConnectionState.Open)
                MyConnect();

            MySqlCommand command = MyConn.CreateCommand();

            //**Added from the internet fix
            MySqlDataReader dataReader = null;

            try
            {
                command.CommandText = sql;
                
                //Fill in them infos
                command.Parameters.Add(new MySqlParameter("?product_id", item.SKU));
                command.Parameters.Add(new MySqlParameter("?products_model", item.ProductNo));
                command.Parameters.Add(new MySqlParameter("?products_image", item.Attachments[0]));
                command.Parameters.Add(new MySqlParameter("?price", item.Price));
                command.Parameters.Add(new MySqlParameter("?master_categories_id", item.CategoryNo));
                command.Parameters.Add(new MySqlParameter("?manufacturers_id", item.SupplierNo));
                command.Parameters.Add(new MySqlParameter("?products_name", item.Description));
                command.Parameters.Add(new MySqlParameter("?products_description", item.LongDesc));

                //**Added from the internet fix
               dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

               //**Original code
               command.ExecuteReader();
            }
            catch (Exception e)
            {

                Log.WriteLine("Error Adding item: " + item.Description + "(" + item.ProductNo + ")");
                Log.WriteLine("Error: " + e.Message);
            }
            finally
            {
                //**Added from internet fix
                if (dataReader != null)
                    dataReader.Close();
 
               //**Original code
               command.Dispose();
            }
        }
2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by __avd
0

Use ExecuteNonQuery() method.

private static void AddZenCartItem(Product item){
            Log.WriteLine("Im adding "+item.Description+" to the zencart.");
            String sql;
            //Now lets put some SQL in that String
            sql = //BIG LONG SQL STATEMENT 
            //Reopen the connection just incase it closed
            if (MyConn.State != ConnectionState.Open)
                MyConnect();

            MySqlCommand command = MyConn.CreateCommand();
            command.CommandText = sql;
                
                //Fill in them infos
                command.Parameters.Add(new MySqlParameter("?product_id", item.SKU));
                command.Parameters.Add(new MySqlParameter("?products_model", item.ProductNo));
                command.Parameters.Add(new MySqlParameter("?products_image", item.Attachments[0]));
                command.Parameters.Add(new MySqlParameter("?price", item.Price));
                command.Parameters.Add(new MySqlParameter("?master_categories_id", item.CategoryNo));
                command.Parameters.Add(new MySqlParameter("?manufacturers_id", item.SupplierNo));
                command.Parameters.Add(new MySqlParameter("?products_name", item.Description));
                command.Parameters.Add(new MySqlParameter("?products_description", item.LongDesc));

               command.ExecuteNonQuery();
   }
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.