使用C＃在asp.net中执行SQL Server 2008存储过程/游标时出错？

Hello this is my first cursor program that I have written.

你好，这是我写的第一个光标程序。

I am surprised to see the error after successful affecting the rows of table in my procedure that I want to do using this procedure.

在使用此过程我想要执行的过程中成功影响表行之后，我很惊讶地看到错误。

I am assigning the menus to the post and when I remove some menu from specific post then I need to update (Add/Remove) menus to all employees belonging to this post. This below procedure does all things but I am getting an error :

我正在为帖子分配菜单，当我从特定帖子中删除一些菜单时，我需要更新（添加/删除）菜单给属于这篇文章的所有员工。以下程序执行所有操作但我收到错误：

Error:There is already an object named 'EmployeeMenuMst' in the database.

错误：数据库中已存在名为“EmployeeMenuMst”的对象。

after execution.

执行后。

Stored procedure is:

存储过程是：

ALTER PROCEDURE [sUpdateEmpMenuMst]
    (@EmployeePostID INT)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EMPID INT
    DECLARE @EMPMENUID INT

    BEGIN TRAN
    BEGIN TRY
    DECLARE EmployeeTable CURSOR FOR

    SELECT EmployeeID FROM EMPLOYEEMST WHERE EmployeePostID=@EmployeePostID

    OPEN EmployeeTable

    FETCH NEXT FROM EmployeeTable INTO @EMPID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DELETE FROM EmployeeMenuMst WHERE EmployeeID=@EMPID  

    FETCH NEXT FROM EmployeeTable INTO @EMPID
    END
    CLOSE EmployeeTable
    DEALLOCATE EmployeeTable

        SELECT   EmployeeMst.EmployeeID, EmployeePostMenuMst.MenuID, EmployeePostMenuMst.AccessLevelID,1 AS ISVISIBLE
        INTO EmployeeMenuMst
        FROM     EmployeePostMenuMst 
        INNER JOIN EmployeeMst 
        ON EmployeePostMenuMst.EmployeePostID = EmployeeMst.EmployeePostID
        WHERE EmployeeMst.EMPLOYEEPOSTID=@EmployeePostID ORDER BY EmployeeMst.EMPLOYEEID
        COMMIT TRAN
    END TRY

    BEGIN CATCH
        ROLLBACK TRAN
        IF @@TRANCOUNT > 0
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT
        SELECT @ErrMsg = ERROR_MESSAGE(),
        @ErrSeverity = ERROR_SEVERITY()
        RAISERROR(@ErrMsg, @ErrSeverity, 1)

    END CATCH

   END
GO

In code behind aspx.cs file:

在aspx.cs文件后面的代码中：

private void UpdateMenus()
    {
        SqlCommand cmd = new SqlCommand();
        SqlConnection con = new SqlConnection(conStr);
        try
        {
            con.Open();
            cmd.CommandText = "sUpdateEmpMenuMst";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmployeePostID", Convert.ToInt32(Session["postID"]));
            cmd.Connection = con;
            int i = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            con.Close();
        }
        finally
        {
            con.Close();
        }
    }

The error is on ExecuteNonQuery line. Why does it throw an error - any idea?

错误发生在ExecuteNonQuery行上。为什么会抛出错误 - 任何想法？

And after getting error rows are affected successfully in the way that I need!

在获得错误后，行以我需要的方式成功受到影响！

Help appreciated!

帮助赞赏！

1 个解决方案

#1

SELECT   EmployeeMst.EmployeeID, EmployeePostMenuMst.MenuID, EmployeePostMenuMst.AccessLevelID,1 AS ISVISIBLE
        INTO EmployeeMenuMst

Will try to create a table EmployeeMenuMst ans fill ist, perhaps you are searching for

将尝试创建一个表EmployeeMenuMst ans fill ist，也许你正在寻找

Insert into EmployeeMenuMst
SELECT   EmployeeMst.EmployeeID, EmployeePostMenuMst.MenuID, EmployeePostMenuMst.AccessLevelID,1 AS ISVISIBLE