C# .NET - getting error when fetching data from routine

Asked By abinav shankar on 27-Jan-12 08:03 AM
I am using the following code to call the stored procedure and I am getting the errror the The function or routine not found in the databasebut it is in the database and it works well when i restart my solution pl check my Stored procedures and tell me where i went wrong
public DataTable getPermanentAddress(string strAssID)
    DataTable dtAddress = new DataTable();
    strCon = new MySqlConnection(strConnection);
    MySqlCommand cmdPermanentAddress = strCon.CreateCommand();//new MySqlCommand("sp_getPermanentAddress", strCon);
      cmdPermanentAddress.CommandText = "sp_getPermanentAddress";
      MySqlDataAdapter daPermanentAddress = new MySqlDataAdapter(cmdPermanentAddress);
      daPermanentAddress.SelectCommand.Parameters.Add("@AssociateId",MySqlDbType.Int32).Value = strAssID.ToString().Trim();
      daPermanentAddress.SelectCommand.CommandType = CommandType.StoredProcedure;
      return dtAddress;
    catch (Exception ex)
      throw (ex);
      if (strCon.State == ConnectionState.Open)

My routine
- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getPermanentAddress`(In AssociateId integer)
select permanent_add from associate_details where associate_id = AssociateId;
Pl tell where is the issue its very urgent
[)ia6l0 iii replied to abinav shankar on 27-Jan-12 08:20 AM
First, you have to associate the sql command with the command. Replace 
MySqlCommand cmdPermanentAddress = strCon.CreateCommand();
MySqlCommand cmdPermanentAddress = new MySqlCommand("sp_getPermanentAddress", strCon); 

Secondly, You should use AddWithValue and change the following line to
daPermanentAddress.SelectCommand.Parameters.Add("AssociateId",MySqlDbType.Int32).Value = strAssID.ToString().Trim(); 
daPermanentAddress.SelectCommand.Parameters.AddWithValue("?AssociateId", MySqlDbType.Int32, (string)strAssID.Trim());

Let me know if this doesn't help.
Chintan Vaghela replied to abinav shankar on 27-Jan-12 08:24 AM

you cant assign string value to Int Parameter.

daPermanentAddress.SelectCommand.Parameters.Add("@AssociateId",MySqlDbType.Int32).Value = Convert.ToInt32(strAssID);

Hope this helpful
dipa ahuja replied to abinav shankar on 27-Jan-12 08:29 AM
If you are fetching data using sp then there is no need of such long code:

ALTER PROCEDURE dbo.StoredProcedure2
    /* SET NOCOUNT ON */
    select * from emp;
Using Stored Procedure
  protected void Page_Load(object sender, EventArgs e)
    string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    MySqlConnection con = new MySqlConnection(conn);
    MySqlCommand comm = new MySqlCommand("StoredProcedure2", con);
    comm.CommandType = CommandType.StoredProcedure;
    MySqlDataAdapter da = new MySqlDataAdapter(comm);
    DataTable dt = new DataTable();
    GridView1.DataSource = dt;