Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
When i try execute the storeprocedure throw Ado.net i am facing the following error,

Error "Could not find stored procedure 'Procedure_name'"

that SP contain output Parameter if i remove output parameter i will work fine .

Herewith i have attached the Storeprocedure and Ado.net connectivty coding

Store Procedure

SQL
create PROCEDURE [dbo].[USP_CreateCatagory]
    -- Add the parameters for the stored procedure here
    @CatagoryName Varchar(50),
    @CatagoryId int out

AS
BEGIN
   
    Insert into tblCategory(categoryName) Values (@CatagoryName)
    select  @CatagoryId = SCOPE_IDENTITY()
END


Ado.Net

C#
sqlCon = new SqlConnection(SqlConnectionString);
            sqlcmd = new SqlCommand("USP_CreateCatagory", sqlCon);
            try
            {
                sqlcmd.CommandType = CommandType.StoredProcedure;

                sqlCon.Open();
            SqlParameter CatagoryName11 = new SqlParameter();
            CatagoryName11.ParameterName = "CatagoryName";
            CatagoryName11.Value = CategoryName;
            CatagoryName11.SqlDbType = SqlDbType.VarChar;
            CatagoryName11.Direction = ParameterDirection.Input;

            SqlParameter oUtputParam = new SqlParameter();
            oUtputParam.ParameterName = "@CatagoryId";
            oUtputParam.SqlDbType = SqlDbType.Int;
            oUtputParam.Direction = ParameterDirection.Output;               

             sqlcmd.Parameters.Add(CatagoryName11);

             sqlcmd.Parameters.Add(oUtputParam);

                sqlcmd.ExecuteNonQuery();
                sqlcmd.Dispose();
                sqlCon.Close();


-----------------------------------

I am facing error in "sqlcmd.ExecuteNonQuery();"
Posted
Updated 27-Aug-14 10:08am
v2
Comments
PhilLenoir 27-Aug-14 14:48pm    
Have you tried executing the SP on SQL Server itself? What account is being used by your application and what privileges have you assigned?
Richard Deeming 27-Aug-14 14:53pm    
Try using ParameterDirection.InputOutput instead of ParameterDirection.Output for the output parameter.
Valery Possoz 27-Aug-14 15:00pm    
This code is OK and should work.
Have you checked your connection string? are you targeting the same database, same server? my guess is that you have a server/database mix up.
Herman<T>.Instance 27-Aug-14 16:09pm    
You Dispose before you Close. That is another problem in your code
vikinghunter 27-Aug-14 20:54pm    
Perhaps executing with the wrong user who is not the stored procedure's owner.

Does it matter that you instantiate sqlcmd before you open sqlcon?

I suggest you execute the open of the database before you create the sqlcmd object.

Also, there is only one "a" in category.
 
Share this answer
 
v2
Your code could work. You need to check whether those items Valery Possoz mentioned are correct. And if you want to "Dispose()", not only "Close()", perhaps, to construct like this is better:
C#
using (SqlConnection sqlCon = new SqlConnection(SqlConnectionString))
{
    SqlCommand sqlcmd = sqlCon.CreateCommand();

    sqlcmd.CommandType = CommandType.StoredProcedure;
    sqlcmd.CommandText = "USP_CreateCatagory";

    SqlParameter CatagoryName11 = new SqlParameter
    {
        ParameterName = "CatagoryName",
        Value = CategoryName,
        SqlDbType = SqlDbType.VarChar,
        Direction = ParameterDirection.Input
    };

    SqlParameter oUtputParam = new SqlParameter
    {
        ParameterName = "@CatagoryId",
        SqlDbType = SqlDbType.Int,
        Direction = ParameterDirection.Output
    };

    sqlcmd.Parameters.Add(CatagoryName11);
    sqlcmd.Parameters.Add(oUtputParam);

    try
    {
        sqlCon.Open();
        sqlcmd.ExecuteNonQuery();
    }
    catch { }
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900