locked
weird problems: nothing is happening!! RRS feed

  • Question

  • User969992919 posted

    I have the following code and SQL:

    sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
    sql_connection.Open();
    sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
    sql_command.CommandType = CommandType.StoredProcedure;
    
    sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
    sql_command.Parameters.AddWithValue("param_volow_member_id", Convert.ToInt32(Session["volow_member_id"])).MySqlDbType = MySqlDbType.Int32;
    sql_command.Parameters.Add("param_is_channel_created", MySqlDbType.Bit).Direction = ParameterDirection.Output;
                    
    // execute the query;
    int result_rows = sql_command.ExecuteNonQuery();
    
    if (Convert.ToBoolean(sql_command.Parameters["param_is_channel_created"].Value) != true)
    { Session["is_channel_created"] = false; }


    and this is MySQL stored procedure:

    BEGIN
    	SET param_is_channel_created = TRUE;
    
    	DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
    	DELETE FROM member_channels WHERE member_channel_id = param_channel_id;
    
    	IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
    	BEGIN
    		UPDATE volow_members SET is_channel_created = FALSE;
    
    		SET param_is_channel_created = FALSE;
    	END;
    	END IF;
    END


    but nothing is happending and I am sure all are ok because I change something in the storedprocedure and I got and error but with the above I don''t get any error and page is just reloading with the same data when I click the delete button?

    Kindly help...

    Friday, October 5, 2012 12:35 PM

All replies

  • User276895905 posted

    this does not work because your stored procedure does not return the variable param_is_channel_created as output param.

    check this article on how to create output param in stored procedure.

    http://www.mysqltutorial.org/stored-procedures-parameters.aspx

    Friday, October 5, 2012 9:46 PM
  • User969992919 posted

    but DELETE sql statment has nothing to do with value returning... however, records are not getting deleted...

    Saturday, October 6, 2012 12:47 PM
  • User71929859 posted

    Hello,

    Try to debug and get the value for channel_id. Then execute your delete queries directly in the DB (Not from the program) with that value. See whether records are getting deleted or not. I suspect that your WHERE clause in the queries are returning any records which matches the critiria, you've given.

    Saturday, October 6, 2012 1:38 PM
  • User969992919 posted

    getting this:

    [SQL] 
    
    	IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = @volow_member_id) THEN
    	BEGIN
    		UPDATE volow_members SET is_channel_created = FALSE;
    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_' at line 1
    



    Saturday, October 6, 2012 1:50 PM
  • User969992919 posted

    kindly help...

    Sunday, October 7, 2012 1:23 PM