C#SQL Server 2008 R2插入存储过程将不起作用

最后发布: 2011-02-17 14:24:00


问题

我有此存储的程序将无法正常工作。 如果我在Management Studio中尝试使用此参数进行填充的查询,那么我看不到我的代码做错了什么,但我希望这里有人注意到我做错了什么

CREATE PROCEDURE new_project 
-- Add the parameters for the stored procedure here
@proj_naam nvarchar = null,
@plaats nvarchar = null,
@opd_geef int = 0,
@status int = 0,
@proj_id int = 0  AS  BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO project (naam_project, plaats, opdrachtgeverZEEBREGTS_nr, status, project_NR)
VALUES (@proj_naam, @plaats, @opd_geef, @status, @proj_id)  END  GO

和C#代码:

System.Data.SqlClient.SqlConnection con;
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = Global.ConnectionString_fileserver;
            con.Open();
            string stopro = "";
            switch (type)
            {
                case 1:
                    stopro = "new_project";
                    break;
                case 2:
                    stopro = "new_bedrijf";
                    break;
                case 3:
                    stopro = "new_persoon";
                    break;
            }
            SqlCommand command = new SqlCommand(stopro, con);
            command.CommandType = CommandType.StoredProcedure;
            switch (type)
            {
                case 1:
                    SqlParameter proj_naam = command.Parameters.Add("@proj_naam", SqlDbType.NVarChar);
                    SqlParameter plaats = command.Parameters.Add("@plaats", SqlDbType.NVarChar);
                    SqlParameter opdrachtgever = command.Parameters.Add("@opd_geef", SqlDbType.Int);
                    SqlParameter status = command.Parameters.Add("@status", SqlDbType.Int);
                    SqlParameter proj_id = command.Parameters.Add("@proj_id", SqlDbType.Int);
                    proj_naam.Value = tb_proj_projectnaam.Text; proj_naam.Direction = ParameterDirection.Input;
                    plaats.Value = tb_proj_plaats.Text; plaats.Direction = ParameterDirection.Input;
                    opdrachtgever.Value = cb_proj_opdrachtgever.SelectedValue; opdrachtgever.Direction = ParameterDirection.Input;
                    status.Value = cb_proj_status.SelectedValue; status.Direction = ParameterDirection.Input;
                    proj_id.Value = id; proj_id.Direction = ParameterDirection.Input;
                    break;  
            }  
            int nwok = command.ExecuteNonQuery();
            con.Close();

因此,我希望有人可以提前帮助thnx!

c# stored-procedures sql-server-2008-r2
回答

您需要先break一下int nwok = command.ExecuteNonQuery();

编辑

与您的SPROC不执行的原因无关,但是您已将SET NOCOUNT ON ,这将导致ExecuteNonQuery返回-1 您在插入之前是否正在运行SET NOCOUNT OFF


回答

我可以看到,如果在您的切换案例中Case不为1 ,那么该命令实例将永远无法获取这些参数,因此它将无法正常工作。


回答

在这里,您要在switch语句中测试type

switch (type)             
{                 
    case 1:                     
        stopro = "new_project";                     
        break;                 
    case 2:
        stopro = "new_bedrijf";                     
        break;
    // etc.
}

然后,您在另一个switch语句中重新测试了该type ,不过这是过分的。 将参数移到第一个switch语句中,这可能会解决您的错误。

switch (type)             
{                 
    case 1:                     
        stopro = "new_project";     
        SqlParameter proj_naam = command.Parameters.Add("@proj_naam", SqlDbType.NVarChar);                     
        SqlParameter plaats = command.Parameters.Add("@plaats", SqlDbType.NVarChar);
        SqlParameter opdrachtgever = command.Parameters.Add("@opd_geef", SqlDbType.Int); 
        SqlParameter status = command.Parameters.Add("@status", SqlDbType.Int); 
        SqlParameter proj_id = command.Parameters.Add("@proj_id", SqlDbType.Int);
        proj_naam.Value = tb_proj_projectnaam.Text; 
        proj_naam.Direction = ParameterDirection.Input;   
        plaats.Value = tb_proj_plaats.Text; 
        plaats.Direction = ParameterDirection.Input; 
        opdrachtgever.Value = cb_proj_opdrachtgever.SelectedValue; 
        opdrachtgever.Direction = ParameterDirection.Input;
        status.Value = cb_proj_status.SelectedValue; 
        status.Direction = ParameterDirection.Input;
        proj_id.Value = id; 
        proj_id.Direction = ParameterDirection.Input;                    
        break;                 
    case 2:
        stopro = "new_bedrijf";                     
    // etc.
}