C# .NET - cannot find the procedure or routine in database

Asked By abinav shankar on 19-Jan-12 12:31 AM
Hi
 
I have created a routine in mysql and it gets executed there and I am calling the routine from my code behind and I am the exception cannot find the procedure or routine in database
 
this is the code where i am calling the procedure
code where i am calling the routine:
Code for calling the routine:
public int getLeaveReqId(string strAssId,string strLeaveStatus,string strLeaveType)
  {
    int leaveReqId = 0;
    Con = new MySqlConnection(conn);
    Con.Open();
    MySqlCommand cmdLeaveReqId = new MySqlCommand("sp_getLeaveId", Con);
    cmdLeaveReqId.Parameters.Add("@associateId", MySqlDbType.Int32).Value = Convert.ToInt32(strAssId.Trim());
    cmdLeaveReqId.Parameters.Add("@leavetype", MySqlDbType.String).Value = strLeaveType.Trim();
    cmdLeaveReqId.Parameters.Add("@leaveStatus", MySqlDbType.String).Value = strLeaveStatus.Trim();
    cmdLeaveReqId.CommandType = CommandType.StoredProcedure;
    MySqlDataReader drLeaveReqId = cmdLeaveReqId.ExecuteReader();
    drLeaveReqId.Read();
    leaveReqId = Convert.ToInt32(drLeaveReqId[0].ToString().Trim());
    Con.Close();
    return leaveReqId;
  }

Routine:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getLeaveId`(
in associateId integer,
in leavetype varchar(45),
in leaveStatus varchar(45)
)
BEGIN
select leave_req_id from leave_request where leave_type_id = (select leave_type_id 
from leave_type where leave_type=leavetype) 
and leave_status_id=(select leave_status_id from leave_status where leave_status=leaveStatus) 
and associate_id=associateId;
END
Stack Trace:

[MySqlException (0x80004005): Procedure or function '`sp_getLeaveId`' cannot be found in database '`kcube_intra`'.]
   lms_ApprovalOrRejection.lnkApprove_Click(Object sender, EventArgs e) in c:\Users\10033\Desktop\lms_17_01_2012\LeaveManagementSystem\lms_ApprovalOrRejection.aspx.cs:50
   System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +79
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
  
  
pl help its very urgent

Jitendra Faye replied to abinav shankar on 19-Jan-12 12:49 AM
Check these-


1. have you executed that stored procedure in sql server first, then only you can access it.

2. Check your Connection string also.
Sri K replied to abinav shankar on 19-Jan-12 01:06 AM
could be:
1. connection pointing to wrong database
2. typo error of sp name
3. connection is pointing to a correct database name but different server
abinav shankar replied to Sri K on 19-Jan-12 02:24 AM
I have used localhost for server name

pl check it this is my connection string

<add name="Constr" connectionString="dataSource=localhost;Initial Catalog=kcube_intra;uid=root;password=kcubeserver"  providerName="MySql.Data.MySqlClient"/>
kalpana aparnathi replied to abinav shankar on 19-Jan-12 03:14 AM
hi,
solution for this problem.

    In your problem is sp name problem that  may be connection detail is wrong frm database and if connection is not wrong so may be its server problem so please check it

hope will works,

Thanks,