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

Asked By abinav shankar on 27-Jan-12 08:03 AM
HI
 
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)
  {
    //strCon.Open();
    DataTable dtAddress = new DataTable();
    strCon = new MySqlConnection(strConnection);
    strCon.Open();
    MySqlCommand cmdPermanentAddress = strCon.CreateCommand();//new MySqlCommand("sp_getPermanentAddress", strCon);
    try
    {
      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;
      daPermanentAddress.Fill(dtAddress);
      return dtAddress;
    }
    catch (Exception ex)
    {
      throw (ex);
    }
    finally
    {
      if (strCon.State == ConnectionState.Open)
      {
        cmdPermanentAddress.Dispose();
        strCon.Close();
        strCon.Dispose();
      }
      
    }
  }

My routine
- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getPermanentAddress`(In AssociateId integer)
BEGIN
select permanent_add from associate_details where associate_id = AssociateId;
END
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();
with
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(); 
with
daPermanentAddress.SelectCommand.Parameters.AddWithValue("?AssociateId", MySqlDbType.Int32, (string)strAssID.Trim());

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

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:
Procedure
 
ALTER PROCEDURE dbo.StoredProcedure2
AS
    /* SET NOCOUNT ON */
 
    select * from emp;
    
RETURN
 
Using Stored Procedure
 
  protected void Page_Load(object sender, EventArgs e)
  {
    string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
     
    MySqlConnection con = new MySqlConnection(conn);
    con.Open();
    MySqlCommand comm = new MySqlCommand("StoredProcedure2", con);
    comm.CommandType = CommandType.StoredProcedure;
    MySqlDataAdapter da = new MySqlDataAdapter(comm);
    DataTable dt = new DataTable();
    da.Fill(dt);
    con.Close();
 
    GridView1.DataSource = dt;
    GridView1.DataBind();
   }