Sys.WebForms.PageRequestManagerServerErrorException: 关键字 'view' 附近有语法错误。

[gongshengnan1993]

我在sql server中新建查询没有出错，但是在asp.net中总提示关键字 'view' 附近有语法错误，不知道是为什么？
string str = "create view basic(class_id,student_id,student_number,student_name,arra_id,score) as select class_id,tb_student.student_id,student_number,student_name,tb_score.arra_id,score from tb_score,tb_courseArrage,tb_student where tb_score.arra_id=tb_courseArrage.arra_id and tb_student.student_id=tb_score.student_id and gradeid=@gradeid and termid=@termid and class_id=@classid;";
sqlHelper.ExecuteNonQuery(str, new SqlParameter("gradeid", this.DropDownList1.SelectedItem.Value.ToString()), new SqlParameter("classid", this.DropDownList2.SelectedItem.Value.ToString()), new SqlParameter("termid", this.DropDownList3.SelectedItem.Value.ToString()));
string str1 = "create view total(student_id,sum_score,avg_score) as select student_id,sum(score),avg(score) from basic group by student_id;";
sqlHelper.ExecuteNonQuery(str1);
string str2 = "create view score_table(student_number,student_name,basic_total,basic_average,quality_score,score) as select student_number,student_name,sum_score,avg_score,quality_score,avg_score*0.7+quality_score*0.3 from basic,total,tb_qualityScore where basic.student_id=tb_qualityScore.student_id and basic.student_id=total.student_id and term_id=@termid;";
sqlHelper.ExecuteNonQuery(str2, new SqlParameter("termid", this.DropDownList3.SelectedItem.Value.ToString()));
string str3 = "select distinct * from score_table order by score desc;";
DataTable table3 = sqlHelper.ExecuteDataTable(str3);
this.GridView1.DataSource = table3;
this.GridView1.DataBind();
string str4 = "drop view basic";
sqlHelper.ExecuteNonQuery(str4);
string str5 = "drop view total";
sqlHelper.ExecuteNonQuery(str5);
string str6 = "drop view score_table";
sqlHelper.ExecuteNonQuery(str6);

[BrightFireOfCy]

basic是sql关键字，用中括号括起来

[gongshengnan1993]

我加了中括号还是没用 这是我数据库连接使用的类： public static string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } return cmd.ExecuteNonQuery(); } } } public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; foreach (SqlParameter parameter in parameters) { cmd.Parameters.Add(parameter); } SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet da = new DataSet(); adapter.Fill(da); DataTable table = da.Tables[0]; return table; } } }

[gongshengnan1993]

我把名字换掉也不行，我在sql中新建查询如下 create view score_basic(class_id,student_id,student_number,student_name,arra_id,score) as select class_id,tb_student.student_id,student_number,student_name,tb_score.arra_id,score from tb_score,tb_courseArrage,tb_student where tb_score.arra_id=tb_courseArrage.arra_id and tb_student.student_id=tb_score.student_id and gradeid=1 and termid=1 and class_id=9; create view score_total(student_id,sum_score,avg_score) as select student_id,sum(score),avg(score) from score_basic group by student_id; create view score_table(student_number,student_name,basic_total,basic_average,quality_score,score) as select student_number,student_name,sum_score,avg_score,quality_score,avg_score*0.7+quality_score*0.3 from score_basic,score_total,tb_qualityScore where score_basic.student_id=tb_qualityScore.student_id and score_basic.student_id=score_total.student_id and term_id=1; select distinct * from score_table order by score desc; 结果是可以出来的 drop view score_table drop view score_basic drop view score_total

[BrightFireOfCy]

把exception的详细内容贴上来看看？

[BrightFireOfCy]

仔细看了下你的代码，

sqlHelper.ExecuteNonQuery(str, new SqlParameter("gradeid", this.DropDownList1.SelectedItem.Value.ToString()), new SqlParameter("classid", this.DropDownList2.SelectedItem.Value.ToString()), new SqlParameter("termid", this.DropDownList3.SelectedItem.Value.ToString()));

改成下面这样

sqlHelper.ExecuteNonQuery(str, new SqlParameter("gradeid", this.DropDownList1.SelectedItem.Value.ToString()), new SqlParameter("termid", this.DropDownList3.SelectedItem.Value.ToString()), new SqlParameter("classid", this.DropDownList2.SelectedItem.Value.ToString()));

sqlcommand的paramters是认顺序的。

[BrightFireOfCy]

string str = "
                create view basic( 
                    class_id, 
                    student_id, 
                    student_number, 
                    student_name, 
                    arra_id, 
                    score 
                ) as 
                select 
                    class_id, 
                    tb_student.student_id, 
                    student_number, 
                    student_name, 
                    tb_score.arra_id, 
                    score 
                from 
                    tb_score 
                inner join 
                    tb_courseArrage 
                on 
                    tb_score.arra_id=tb_courseArrage.arra_id 
                inner join 
                    tb_student 
                on 
                    tb_student.student_id=tb_score.student_id 
                where 
                    gradeid=@gradeid 
                    and termid=@termid 
                    and class_id=@classid
            ";
        sqlHelper.ExecuteNonQueryAutoCommit(
            str, 
            new SqlParameter("gradeid", this.DropDownList1.SelectedItem.Value.ToString()), 
            new SqlParameter("termid", this.DropDownList3.SelectedItem.Value.ToString()), 
            new SqlParameter("classid", this.DropDownList2.SelectedItem.Value.ToString())
        );
            
        string str1 = "
                create view total( 
                    student_id, 
                    sum_score, 
                    avg_score 
                ) as 
                select 
                    student_id,
                    sum(score),
                    avg(score) 
                from 
                    basic 
                group by 
                    student_id";
                    sqlHelper.ExecuteNonQueryAutoCommit(str1);
                    
                string str2 = "
                create view score_table(
                    student_number,
                    student_name,
                    basic_total,
                    basic_average,
                    quality_score,
                    score
                ) as 
                select 
                    student_number,
                    student_name,
                    sum_score,
                    avg_score,
                    quality_score,
                    avg_score*0.7+quality_score*0.3 
                from 
                    basic
                inner join
                    total
                on
                    basic.student_id=total.student_id
                inner join
                    tb_qualityScore  
                on 
                    basic.student_id=tb_qualityScore.student_id
                where 
                    term_id=@termid
            ";
        sqlHelper.ExecuteNonQuery(
            str2, 
            new SqlParameter("termid", this.DropDownList3.SelectedItem.Value.ToString())
        );

        string str3 = "select distinct * from score_table order by score desc;";
        DataTable table3 = sqlHelper.ExecuteDataTable(str3);

        this.GridView1.DataSource = table3;
        this.GridView1.DataBind();
            
        string str4 = "drop view basic";
        sqlHelper.ExecuteNonQueryAutoCommit(str4);
            
        string str5 = "drop view total";
        sqlHelper.ExecuteNonQueryAutoCommit(str5);
        
        string str6 = "drop view score_table";
        sqlHelper.ExecuteNonQueryAutoCommit(str6); 

	public static string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
	public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
	{
		using (SqlConnection conn = new SqlConnection(connStr))
		{
			conn.Open();
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				foreach (SqlParameter parameter in parameters)
				{
					cmd.Parameters.Add(parameter);
				}
				return cmd.ExecuteNonQuery();
			}
		}
	}
	public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
	{
		using (SqlConnection conn = new SqlConnection(connStr))
		{
			conn.Open();
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				foreach (SqlParameter parameter in parameters)
				{
					cmd.Parameters.Add(parameter);
				}
				SqlDataAdapter adapter = new SqlDataAdapter(cmd);
				DataSet da = new DataSet();
				adapter.Fill(da);
				DataTable table = da.Tables[0];
				return table;
			}
		}
	} 

	public static int ExecuteNonQueryAutoCommit(string sql, params SqlParameter[] parameters)
	{
		int iRet = -1;
		using (SqlConnection conn = new SqlConnection(connStr))
		{
			conn.Open();
			using (SqlCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				foreach (SqlParameter parameter in parameters)
				{
					cmd.Parameters.Add(parameter);
				}
				iRet = cmd.ExecuteNonQuery();
			}
			conn.commit();
		}
		return iRet;
	}