从27张成绩表查询指定学生成绩并绑定DataGrid报错求助

首先，你的报错根源是SQL语句的JOIN语法完全错误，同时代码里还有逻辑冗余问题，咱们一步步拆解解决：

1. 错误原因分析 #

你写的SQL里，INNER JOIN后面直接跟了一堆逗号分隔的表，这完全不符合SQL的JOIN语法规范——JOIN需要为每一对关联表指定明确的关联条件。更关键的是：你的场景是要把1-9年级各学期的成绩合并成一个统一结果集展示，而不是把不同表的行做关联拼接，所以根本不应该用JOIN，而是要用UNION ALL来合并同结构的表数据。

另外，代码里调用cmd.ExecuteNonQuery()是完全多余的，这个方法是用来执行增、删、改这类不返回结果集的SQL操作的，查询场景下不需要调用它，这一步反而可能触发不必要的执行错误。

2. 修正后的代码方案 #

正确的SQL语句逻辑 #

因为每个年级的三个学期表结构应该是一致的（都包含StudentId和成绩相关字段），所以用UNION ALL把所有表中符合StudentId条件的行合并起来：

SELECT * FROM grade1FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade1ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade1TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade2FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade2ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade2TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade3FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade3ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade3TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade4FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade4ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade4TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade5FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade5ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade5TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade6FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade6ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade6TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade7FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade7ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade7TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade8FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade8ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade8TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade9FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade9ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade9TT_results WHERE StudentId=@Id

注意：如果各个表的字段顺序/数据类型不完全一致，UNION ALL会报错，这时候你需要明确指定字段名，确保每个SELECT的字段列表完全匹配，比如SELECT StudentId, Subject, Score, Term FROM ...

修正后的C#代码 #

去掉多余的ExecuteNonQuery()调用，同时用using语句自动管理数据库连接和命令的生命周期，避免资源泄漏：

private void btnsearch_Click(object sender, EventArgs e)
{
    // 用using自动释放连接资源，避免连接泄漏
    using (SqlConnection cn = new SqlConnection("你的数据库连接字符串"))
    {
        string sql = @"SELECT * FROM grade1FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade1ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade1TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade2FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade2ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade2TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade3FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade3ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade3TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade4FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade4ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade4TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade5FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade5ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade5TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade6FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade6ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade6TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade7FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade7ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade7TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade8FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade8ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade8TT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade9FT_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade9ST_results WHERE StudentId=@Id
UNION ALL
SELECT * FROM grade9TT_results WHERE StudentId=@Id";

        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
            cmd.Parameters.AddWithValue("@Id", txtid.Text);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtable = new DataTable();
            da.Fill(dtable);
            
            BindingSource dsource = new BindingSource();
            dsource.DataSource = dtable;
            datagrid.DataSource = dsource;
            
            // 这里不需要da.Update(dtable)，因为你只是查询数据，没有做修改操作
        }
    }
}

3. 额外优化建议 #

• 数据库表结构优化：27张表的设计维护成本很高，建议合并成一张StudentScores表，增加Grade（年级）和Term（学期）字段来区分不同年级学期的数据，这样查询会简单很多，后续扩展也更方便。
• 参数类型校验：如果StudentId是数值类型（比如int），建议把txtid.Text转换成对应类型再传入参数，避免类型转换错误，比如cmd.Parameters.AddWithValue("@Id", int.Parse(txtid.Text));，记得提前做输入合法性验证。

内容的提问来源于stack exchange，提问作者Nelly