CODEWITHSUNDEEP
asp.netsql-serverstored-procedures
mjyazdani
mjyazdani

Reputation: 2035

stored procedure don't excute from C# code

I have created a stored precedure in sql server with below code :

CREATE  PROCEDURE [dbo].[sp_insert_to_SCT_AccessPermission] 
(
    @AccessPesrmission_VahedSazmani_ID char(5)=NULL,
    @AccessPesrmission_GorohKarmandi_ID char(10)=NULL,
    @AccessPesrmission_Semat_ID char(13)=NULL,
    @AccessPesrmission_Personel_ID  char(8)=NULL,
    @AccessPermission_Read bit =NULL,
    @AccessPermission_Edit bit=NULL ,
    @AccessPermission_Delete bit=NULL ,
    @AccessPermission_Add bit=NULL ,
    @AccessPesrmission_SatheSazmani_ID char(2)=NULL ,
    @AccessPesrmission_Source_ID int=NULL

)

AS
BEGIN
    INSERT INTO [dbo].[SCT_AccessPermission]
           ([AccessPesrmission_VahedSazmani_ID]
           ,[AccessPesrmission_GorohKarmandi_ID]
           ,[AccessPesrmission_Semat_ID]
           ,[AccessPesrmission_Personel_ID]
           ,[AccessPermission_Read]
           ,[AccessPermission_Edit]
           ,[AccessPermission_Delete]
           ,[AccessPermission_Add]
           ,[AccessPesrmission_SatheSazmani_ID]
           ,[AccessPesrmission_Source_ID])
     VALUES
           (@AccessPesrmission_VahedSazmani_ID
           ,@AccessPesrmission_GorohKarmandi_ID 
           ,@AccessPesrmission_Semat_ID
           ,@AccessPesrmission_Personel_ID
           ,@AccessPermission_Read
           ,@AccessPermission_Edit
           ,@AccessPermission_Delete
           ,@AccessPermission_Add
           ,@AccessPesrmission_SatheSazmani_ID 
           ,@AccessPesrmission_Source_ID)
END

and I use it in my C# code with below function:

 public string Execute_Sp(string spName, SqlParameter[] parameters)
        {
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            string cmdtext = "EXEC " + spName + " ";
            for (int i = 0; i < parameters.Length; i++)
            {
                if (parameters[i].Value == DBNull.Value || string.IsNullOrEmpty(parameters[i].Value.ToString())) { }
                else
                {
                    if (i != 0) cmdtext = cmdtext + " , ";
                    cmdtext = cmdtext + "@" + parameters[i].ParameterName + " = ";
                    cmdtext = cmdtext + "\'" + parameters[i].Value + "\'";
                }
            }    
            cmd.CommandText = cmdtext;
            cmd.ExecuteNonQuery();
            return "1";    
        }

but every time I call it, it makes this error: Could not find stored procedure ''. when it makes error, I copy the command text from debug mode and copy it in sql server and it runs perfect! I mean that my function make a right code! what is the problem with it?

my command text in one of the cases is:

EXEC sp_insert_to_SCT_AccessPermission @AccessPesrmission_VahedSazmani_ID = '04011',
@AccessPermission_Read = 'True', 
@AccessPermission_Edit = 'False', 
@AccessPermission_Delete = 'False', 
@AccessPermission_Add = 'False', 
@AccessPesrmission_Source_ID = '13',
@AccessPesrmission_SatheSazmani_ID = '1'

Upvotes: 0

Views: 46

Answers (2)

net - programmer
net - programmer

Reputation: 1

You should understand the mechanism of executing a stored procedure in .NET application . 1. If you are using CommandType .StoredProcedure, The value of the Command Text is only the name of the Stored Procedure , so do not put "exec... " or whatever other than the name of the stored procedure . 2. Put the values of the sql parameters in SQLParameter object , and add it to your sqlCommand object , example : objSqlCommand1.Parameters.Add(objSqlParameter1) .

Upvotes: 0

lc.
lc.

Reputation: 116538

The command text should be just the stored procedure name:

cmd.CommandText = "[dbo].[sp_insert_to_SCT_AccessPermission]";

Then add parameters to the SqlCommand object's Parameters collection with:

cmd.Parameters.AddWithValue(parameters[i].ParameterName, parameters[i].Value);

Upvotes: 3

Related Questions