MySql storedprocedure not executing frm asp.net

Question

I am able to execute MySQL sp. In server it works fine, but when called from asp.net it is not working properly. Below is the stored procedure:

CREATE PROCEDURE `GetCategoryForBackLinkID`(IN BLID int, OUT CatID int)
BEGIN
        SELECT CategoryID INTO CatID FROM backlink where BackLinkID = BLID;
END

Below is the asp.net code

MySqlCommand cmd1 = new MySqlCommand("GetCategoryForBackLinkID");
MySqlConnection con1 = new MySqlConnection();

//ConnectionStringSettings mySetting = ConfigurationManager.ConnectionStrings["linkbuilding1Entities3"];
con1.ConnectionString = "server=67.227.183.117;User Id=rammu1;Pwd=eframmu1;database=linkbuilding1;Persist Security Info=True";
cmd1.Connection = con1;
using (cmd1.Connection)
{
     cmd1.Connection.Open();

     MySqlParameter returnParameter1 = cmd1.Parameters.Add("BLID", MySqlDbType.Int16);
     returnParameter1.Direction = ParameterDirection.Input;
     returnParameter1.Value = maximumbacklinid;

     MySqlParameter returnParameter2 = cmd1.Parameters.Add("CatID", MySqlDbType.Int16);
     returnParameter2.Direction = ParameterDirection.Output;

     cmd1.ExecuteNonQuery();                   
     CategID = (Int16)returnParameter2.Value;

The error I get is

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 'GetCategoryForBackLinkID' at line 1.

What is possibly wrong here?

Soner Gönül · Accepted Answer · 2014-08-04 10:51:21Z

2

Well, I'm not 100% sure but looks like you need to assing your CommandType property like;

cmd1.CommandType = CommandType.StoredProcedure;

Since you using store procedure, this property is Text by default. That's why your program thinks your "GetCategoryForBackLinkID" string is a valid SQL query, not a store procedure.

When you set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.

using(MySqlConnection con1 = new MySqlConnection(connString))
using(MySqlCommand cmd1 = con.CreateCommand())
{
   cmd1.CommandType = CommandType.StoredProcedure;
   // Add your parameter values.
   cmd1.ExecuteNonQuery();
   CategID = (int)returnParameter2.Value;
}

edited Aug 4, 2014 at 10:51

answered Aug 4, 2014 at 8:38

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jignesh.Raj · Accepted Answer · 2014-08-04 11:05:09Z

0

IN Your connection string one keyword is wrong write Data source not database there.

"server=67.227.183.117;User Id=rammu1;Pwd=eframmu1;Data Source=linkbuilding1;Persist Security Info=True";

edited Aug 4, 2014 at 11:05

Jignesh.Raj

6,0074 gold badges30 silver badges57 bronze badges

answered Aug 4, 2014 at 8:51

nilima

12 bronze badges

Collectives™ on Stack Overflow

MySql storedprocedure not executing frm asp.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related