Reputation: 2035
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
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
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