none
数据库错误,错误原因:必须声明变量 '@CourseID' RRS feed

  • 问题

  • 今天在开发系统中,对两个DropDownList之间进行联动之后,点击查询按钮总是出现数据库错误,错误原因:必须声明变量 '@CourseID'
    我的后台代码为:protected void btnSearch_Click(object sender, EventArgs e)
        {
            string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            string SqlSelect = @"select  ST1.StudentID,ST1.StudentName,CT.CourseName,CT.TeacherID,ST2.SchoolName,DT.DepartName,ST.SpecialtyName,GT.GradeName,CT1.ClassName,ECT.Score,TT.TeacherName
                               from StudentTable as ST1,SchoolTable as ST2,DepartTable as DT,SpecialtyTable as ST,GradeTable as GT,ClassTable as CT1,ElectCourseTable as ECT,CourseTable as CT,TeacherTable as TT
                               where ST2.SchoolID=DT.SchoolID
                                 and DT.DepartID=ST.DepartID
                                 and ST.SpecialtyID=CT1.SpecialtyID
                                 and GT.GradeID=CT1.GradeID
                                 and CT.CourseTeacherID=ECT.CourseTeacherID
                                 and ECT.StudentID=ST1.StudentID
                                 and ST1.ClassID=CT1.ClassID
                                 and CT.CourseID=@CourseID
                                 and TT.TeacherID=@TeacherID";
            SqlConnection conn = new SqlConnection(connStr);                                //创建连接对象
            SqlCommand cmd = new SqlCommand(SqlSelect, conn);                               //执行查询
         
           
            try
            {                                               
                if (conn.State.ToString() == "Closed")    //如果连接关闭,打开连接
                    conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(SqlSelect, conn);
                da.SelectCommand.Parameters.AddWithValue("@CourseID", ddlCourse.SelectedValue);
                da.SelectCommand.Parameters.AddWithValue("@TeacherID", ddlTeacher.SelectedValue);
               
                cmd.ExecuteNonQuery();              //执行查询
            }
            catch (Exception ex)                          //异常处理
            {
                Response.Write("数据库错误,错误原因:" + ex.Message);
                Response.End();
            }

            finally
            {
                cmd.Dispose();
                cmd.Dispose();
                if (conn.State.ToString() == "Open")               //如果连接打开,关闭连接
                    conn.Close();
            }
            GridViewBind();
           
        }
    我的SQL是能够正确执行的,但好像是参数传递那边出现了问题,请帮我看看吧!

    2010年2月8日 3:03

答案

  • da.SelectCommand 不等于你的 cmd。


    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    string SqlSelect = @"select  ST1.StudentID,ST1.StudentName,CT.CourseName,CT.TeacherID,ST2.SchoolName,DT.DepartName,ST.SpecialtyName,GT.GradeName,CT1.ClassName,ECT.Score,TT.TeacherName
                   from StudentTable as ST1,SchoolTable as ST2,DepartTable as DT,SpecialtyTable as ST,GradeTable as GT,ClassTable as CT1,ElectCourseTable as ECT,CourseTable as CT,TeacherTable as TT
                   where ST2.SchoolID=DT.SchoolID
                     and DT.DepartID=ST.DepartID
                     and ST.SpecialtyID=CT1.SpecialtyID
                     and GT.GradeID=CT1.GradeID
                     and CT.CourseTeacherID=ECT.CourseTeacherID
                     and ECT.StudentID=ST1.StudentID
                     and ST1.ClassID=CT1.ClassID
                     and CT.CourseID=@CourseID
                     and TT.TeacherID=@TeacherID";
    //SqlConnection conn = new SqlConnection(connStr);                                //创建连接对象
    //SqlCommand cmd = new SqlCommand(SqlSelect, conn);                               //执行查询
    
    
    try
    {
        //if (conn.State.ToString() == "Closed")    //如果连接关闭,打开连接
        //    conn.Open();
        using (SqlDataAdapter da = new SqlDataAdapter(SqlSelect, connStr))
        {
            da.SelectCommand.Parameters.AddWithValue("@CourseID", ddlCourse.SelectedValue);
            da.SelectCommand.Parameters.AddWithValue("@TeacherID", ddlTeacher.SelectedValue);
    
            DataSet ds = new DataSet();
            //cmd.ExecuteNonQuery();              //执行查询
            da.Fill(ds);
            // 重新绑定
        }
    }
    catch (Exception ex)                          //异常处理
    {
        Response.Write("数据库错误,错误原因:" + ex.Message);
        Response.End();
    }


    知识改变命运,奋斗成就人生!
    • 已标记为答案 Smiling008 2010年2月8日 4:16
    2010年2月8日 3:25
    版主

全部回复

  • 你修改你的SqlDataAdapter 

      SqlDataAdapter sda = new SqlDataAdapter(cmd);
    努力+方法=成功
    2010年2月8日 3:21
  • 同时判断下 ddlCourse.SelectedValue这个是否可能为null  如果为null参数就穿不过去了 当为null 可以用DbNull.Value作为其值
    Memory all alone in the moonlight~My Blog in .NET~~~
    2010年2月8日 3:23
    版主
  • da.SelectCommand 不等于你的 cmd。


    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    string SqlSelect = @"select  ST1.StudentID,ST1.StudentName,CT.CourseName,CT.TeacherID,ST2.SchoolName,DT.DepartName,ST.SpecialtyName,GT.GradeName,CT1.ClassName,ECT.Score,TT.TeacherName
                   from StudentTable as ST1,SchoolTable as ST2,DepartTable as DT,SpecialtyTable as ST,GradeTable as GT,ClassTable as CT1,ElectCourseTable as ECT,CourseTable as CT,TeacherTable as TT
                   where ST2.SchoolID=DT.SchoolID
                     and DT.DepartID=ST.DepartID
                     and ST.SpecialtyID=CT1.SpecialtyID
                     and GT.GradeID=CT1.GradeID
                     and CT.CourseTeacherID=ECT.CourseTeacherID
                     and ECT.StudentID=ST1.StudentID
                     and ST1.ClassID=CT1.ClassID
                     and CT.CourseID=@CourseID
                     and TT.TeacherID=@TeacherID";
    //SqlConnection conn = new SqlConnection(connStr);                                //创建连接对象
    //SqlCommand cmd = new SqlCommand(SqlSelect, conn);                               //执行查询
    
    
    try
    {
        //if (conn.State.ToString() == "Closed")    //如果连接关闭,打开连接
        //    conn.Open();
        using (SqlDataAdapter da = new SqlDataAdapter(SqlSelect, connStr))
        {
            da.SelectCommand.Parameters.AddWithValue("@CourseID", ddlCourse.SelectedValue);
            da.SelectCommand.Parameters.AddWithValue("@TeacherID", ddlTeacher.SelectedValue);
    
            DataSet ds = new DataSet();
            //cmd.ExecuteNonQuery();              //执行查询
            da.Fill(ds);
            // 重新绑定
        }
    }
    catch (Exception ex)                          //异常处理
    {
        Response.Write("数据库错误,错误原因:" + ex.Message);
        Response.End();
    }


    知识改变命运,奋斗成就人生!
    • 已标记为答案 Smiling008 2010年2月8日 4:16
    2010年2月8日 3:25
    版主