c# - C# 无法从我的导航中获取当前选择记录值

Question

嘿，我收到一条错误消息，告诉我“列 'RowNumber' 不属于表 Table1”。当我注释掉 currentRecord 行时导航工作正常，但我不再获得当前选定记录的值。BandMember 是管理乐队和成员的多对多关系的表。

这是我的方法：

私人无效LoadBandMembers（）{

        string sqlMemberID = $"SELECT * FROM BandMember WHERE BandID = {currentBandID} AND MemberID = {currentMemberID}";
        string sqlNav = 
            $@"
                SELECT 
                (SELECT COUNT(*) FROM BandMember) AS NumberOfBandMember,
                (SELECT TOP(1) BandID as FirstBandID FROM BandMember
                ) as FirstBandID,
                (
                    SELECT TOP(1) MemberID as FirstMemberID FROM BandMember
                ) as FirstMemberID,
                q.PreviousBandID,
                q.PreviousMemberID,
                q.NextBandID,
                q.NextMemberID,
                (
                    SELECT TOP(1) BandID as LastBandID FROM BandMember ORDER BY BandID Desc
                ) as LastBandID,
                (
                    SELECT TOP(1) MemberID as LastMemberID FROM BandMember ORDER BY BandID Desc
                ) as LastMemberID
                FROM
                (
                    SELECT BandID, MemberID,
                    LEAD(BandID) OVER(ORDER BY BandID) AS NextBandID,
                    LEAD(MemberID) OVER(ORDER BY BandID) AS NextMemberID,  
                    LAG(BandID) OVER(ORDER BY BandID) AS PreviousBandID,
                    LAG(MemberID) OVER(ORDER BY BandID) AS PreviousMemberID,
                    ROW_NUMBER() OVER(ORDER BY BandID) AS 'RowNumber'
                    FROM BandMember
                ) AS q
                WHERE q.BandID = {currentBandID} AND q.MemberID = {currentMemberID}
                ORDER BY q.BandID, q.MemberID
                ";


        sqlNav = DataAccess.SQLCleaner(sqlNav);

        string[] sqlStatements = new string[] { sqlMemberID, sqlNav };

        DataSet ds = new DataSet();
        ds = DataAccess.GetData(sqlStatements);

        if (ds.Tables[0].Rows.Count == 1)
        {
            DataRow selectedBandMember = ds.Tables[0].Rows[0];
            int bandID = Convert.ToInt32(selectedBandMember["BandID"]);
            int memberID = Convert.ToInt32(selectedBandMember["memberID"]);

            cmbBand.SelectedValue = selectedBandMember["BandID"];
            cmbMember.SelectedValue = selectedBandMember["MemberID"];

            numberOfBandMember = Convert.ToInt32(ds.Tables[1].Rows[0]["NumberOfBandMember"]);
            firstBandID = Convert.ToInt32(ds.Tables[1].Rows[0]["FirstBandID"]);
            firstMemberID = Convert.ToInt32(ds.Tables[1].Rows[0]["FirstMemberID"]);
            previousBandID = ds.Tables[1].Rows[0]["PreviousBandID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["PreviousBandID"]) : (int?)null;
            previousMemberID = ds.Tables[1].Rows[0]["PreviousMemberID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["PreviousMemberID"]) : (int?)null; ;
            nextBandID = ds.Tables[1].Rows[0]["NextBandID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["NextBandID"]) : (int?)null;
            nextMemberID = ds.Tables[1].Rows[0]["NextMemberID"] != DBNull.Value ? Convert.ToInt32(ds.Tables["Table1"].Rows[0]["NextMemberID"]) : (int?)null; ;
            lastBandID = Convert.ToInt32(ds.Tables[1].Rows[0]["LastBandID"]);
            lastMemberID = Convert.ToInt32(ds.Tables[1].Rows[0]["LastMemberID"]);
            currentRecord = Convert.ToInt32(ds.Tables[1].Rows[0]["RowNumber"]);


            DisplayToolStrip();
        }
        else
        {

            MessageBox.Show("The band member no longer exists");
            LoadFirstBandMembers();
        }


    }

标签： c#sql