How to get OUT parameter from MySQL stored procedure in asp.net

In this tutorial we use the previous tutorial form. And modify the c# code to insert data in mysql using store procedure and we will try to get the next insert id from the the mysql database using out put parameter. Last we i an facing a problem to get the out put parameter value from the mysql database but there is not good tutorial on this with asp.net. So it inspire me to write an article to get the value of the output parameter value from mysql. How to create storeprocedure in mysql please read my previous article .
Insert in to MySQL with ASP.NET – Part 2

Here we just give the save button code and show this output parameter value in out message box. You can download the full code by link given at the end of this article.

     protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                // Connection string for a typical local MySQL installation
                string cnnString = "Server=localhost;Port=3306;Database=ci_series;Uid=root;Pwd=";

                // Create a connection object 
                MySqlConnection connection = new MySqlConnection(cnnString);

                // call store procedure
                MySqlCommand cmd = new MySqlCommand("insert_membership", connection);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("?first_name", MySqlDbType.VarChar).Value = txtFirstName.Text;
                cmd.Parameters.Add("?last_name", MySqlDbType.VarChar).Value = txtLastName.Text;
                cmd.Parameters.Add("?username", MySqlDbType.VarChar).Value = txtUserName.Text;
                cmd.Parameters.Add("?password", MySqlDbType.VarChar).Value = txtPassword.Text;
                cmd.Parameters.Add("?email_address", MySqlDbType.VarChar).Value = txtEmail.Text;


                cmd.Parameters.Add(new MySqlParameter("?rows", MySqlDbType.Int32));
                cmd.Parameters["?rows"].Direction = ParameterDirection.Output;


                connection.Open();

                cmd.ExecuteNonQuery();

                int rows = (int)cmd.Parameters["?rows"].Value;

                lblError.Text = "Data Saved. " + "New Id will be " + rows.ToString();
            }

            catch (MySqlException ex)
            {
                lblError.Text = "Insert failed with: " + ex.Message;
            }



        }

[smProductImageAdd src="http://learneveryday.net/codecanyon/adverticement/add_codecnayon_smart-social-share-asp.net.png" alt= "Smart Social Share" href="http://codecanyon.net/item/smart-social-share/160097" title="Smart Social Share (Asp.net control)" description="Smart Social share is a asp.net control . Which helps you to give user to share your content with social book mark site." ref="marifdu"]

Create a store procedure using MySQL Administrator. The code is bellow. In my sql we use out as a return parameter value which we can get from the font end by code. And we give this parameter direction as ParameterDirection.Output;

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_membership` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_membership`(

IN first_name varchar(25),
IN last_name varchar(25),
IN username varchar(25),
IN password varchar(10),
IN email_address varchar(50),
out rows int


)
BEGIN

INSERT INTO membership (
first_name,
last_name,
username,
password,
email_address
)

VALUES(
first_name,
last_name,
username,
password,
email_address

) ;

select LAST_INSERT_ID() into rows;

END $$

DELIMITER ;

After insert data using store procedure you can see the successful message like bellow.

To learn more about store procedure you can read Stored Procedure Parameters and
MySQL Stored Procedure – INSERT – Example

Bellow is the link to download the sample code used in this tutorial.

Related Posts