CODEWITHSUNDEEP
c#mysqlasp.netstored-procedures
Ramachandran Arunachalam
Ramachandran Arunachalam

Reputation: 11

MySql storedprocedure not executing frm asp.net

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?

Upvotes: 1

Views: 97

Answers (2)

nilima
nilima

Reputation: 1

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";

Upvotes: 0

Soner Gönül
Soner Gönül

Reputation: 98740

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;
}

Upvotes: 2

Related Questions