问题描述
我在sqlserver中新建查询没有出错，但是在asp.net中总提示关键字'view'附近有语法错误，不知道是为什么？stringstr="createviewbasic(class_id,student_id,student_number,student_name,arra_id,score)asselectclass_id,tb_student.student_id,student_number,student_name,tb_score.arra_id,scorefromtb_score,tb_courseArrage,tb_studentwheretb_score.arra_id=tb_courseArrage.arra_idandtb_student.student_id=tb_score.student_idandgradeid=@gradeidandtermid=@termidandclass_id=@classid;";sqlHelper.ExecuteNonQuery(str,newSqlParameter("gradeid",this.DropDownList1.SelectedItem.Value.ToString()),newSqlParameter("classid",this.DropDownList2.SelectedItem.Value.ToString()),newSqlParameter("termid",this.DropDownList3.SelectedItem.Value.ToString()));stringstr1="createviewtotal(student_id,sum_score,avg_score)asselectstudent_id,sum(score),avg(score)frombasicgroupbystudent_id;";sqlHelper.ExecuteNonQuery(str1);stringstr2="createviewscore_table(student_number,student_name,basic_total,basic_average,quality_score,score)asselectstudent_number,student_name,sum_score,avg_score,quality_score,avg_score*0.7+quality_score*0.3frombasic,total,tb_qualityScorewherebasic.student_id=tb_qualityScore.student_idandbasic.student_id=total.student_idandterm_id=@termid;";sqlHelper.ExecuteNonQuery(str2,newSqlParameter("termid",this.DropDownList3.SelectedItem.Value.ToString()));stringstr3="selectdistinct*fromscore_tableorderbyscoredesc;";DataTabletable3=sqlHelper.ExecuteDataTable(str3);this.GridView1.DataSource=table3;this.GridView1.DataBind();stringstr4="dropviewbasic";sqlHelper.ExecuteNonQuery(str4);stringstr5="dropviewtotal";sqlHelper.ExecuteNonQuery(str5);stringstr6="dropviewscore_table";sqlHelper.ExecuteNonQuery(str6);
解决方案
解决方案二：
basic是sql关键字，用中括号括起来
解决方案三：
我加了中括号还是没用这是我数据库连接使用的类：publicstaticstringconnStr=ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;publicstaticintExecuteNonQuery(stringsql,paramsSqlParameter[]parameters){using(SqlConnectionconn=newSqlConnection(connStr)){conn.Open();using(SqlCommandcmd=conn.CreateCommand()){cmd.CommandText=sql;foreach(SqlParameterparameterinparameters){cmd.Parameters.Add(parameter);}returncmd.ExecuteNonQuery();}}}publicstaticDataTableExecuteDataTable(stringsql,paramsSqlParameter[]parameters){using(SqlConnectionconn=newSqlConnection(connStr)){conn.Open();using(SqlCommandcmd=conn.CreateCommand()){cmd.CommandText=sql;foreach(SqlParameterparameterinparameters){cmd.Parameters.Add(parameter);}SqlDataAdapteradapter=newSqlDataAdapter(cmd);DataSetda=newDataSet();adapter.Fill(da);DataTabletable=da.Tables[0];returntable;}}}
解决方案四：
我把名字换掉也不行，我在sql中新建查询如下createviewscore_basic(class_id,student_id,student_number,student_name,arra_id,score)asselectclass_id,tb_student.student_id,student_number,student_name,tb_score.arra_id,scorefromtb_score,tb_courseArrage,tb_studentwheretb_score.arra_id=tb_courseArrage.arra_idandtb_student.student_id=tb_score.student_idandgradeid=1andtermid=1andclass_id=9;createviewscore_total(student_id,sum_score,avg_score)asselectstudent_id,sum(score),avg(score)fromscore_basicgroupbystudent_id;createviewscore_table(student_number,student_name,basic_total,basic_average,quality_score,score)asselectstudent_number,student_name,sum_score,avg_score,quality_score,avg_score*0.7+quality_score*0.3fromscore_basic,score_total,tb_qualityScorewherescore_basic.student_id=tb_qualityScore.student_idandscore_basic.student_id=score_total.student_idandterm_id=1;selectdistinct*fromscore_tableorderbyscoredesc;结果是可以出来的dropviewscore_tabledropviewscore_basicdropviewscore_total
解决方案五：
把exception的详细内容贴上来看看？
解决方案六：
仔细看了下你的代码，sqlHelper.ExecuteNonQuery(str,newSqlParameter("gradeid",this.DropDownList1.SelectedItem.Value.ToString()),newSqlParameter("classid",this.DropDownList2.SelectedItem.Value.ToString()),newSqlParameter("termid",this.DropDownList3.SelectedItem.Value.ToString()));
改成下面这样sqlHelper.ExecuteNonQuery(str,newSqlParameter("gradeid",this.DropDownList1.SelectedItem.Value.ToString()),newSqlParameter("termid",this.DropDownList3.SelectedItem.Value.ToString()),newSqlParameter("classid",this.DropDownList2.SelectedItem.Value.ToString()));
sqlcommand的paramters是认顺序的。
解决方案七：
stringstr="createviewbasic(class_id,student_id,student_number,student_name,arra_id,score)asselectclass_id,tb_student.student_id,student_number,student_name,tb_score.arra_id,scorefromtb_scoreinnerjointb_courseArrageontb_score.arra_id=tb_courseArrage.arra_idinnerjointb_studentontb_student.student_id=tb_score.student_idwheregradeid=@gradeidandtermid=@termidandclass_id=@classid";sqlHelper.ExecuteNonQueryAutoCommit(str,newSqlParameter("gradeid",this.DropDownList1.SelectedItem.Value.ToString()),newSqlParameter("termid",this.DropDownList3.SelectedItem.Value.ToString()),newSqlParameter("classid",this.DropDownList2.SelectedItem.Value.ToString()));stringstr1="createviewtotal(student_id,sum_score,avg_score)asselectstudent_id,sum(score),avg(score)frombasicgroupbystudent_id";sqlHelper.ExecuteNonQueryAutoCommit(str1);stringstr2="createviewscore_table(student_number,student_name,basic_total,basic_average,quality_score,score)asselectstudent_number,student_name,sum_score,avg_score,quality_score,avg_score*0.7+quality_score*0.3frombasicinnerjointotalonbasic.student_id=total.student_idinnerjointb_qualityScoreonbasic.student_id=tb_qualityScore.student_idwhereterm_id=@termid";sqlHelper.ExecuteNonQuery(str2,newSqlParameter("termid",this.DropDownList3.SelectedItem.Value.ToString()));stringstr3="selectdistinct*fromscore_tableorderbyscoredesc;";DataTabletable3=sqlHelper.ExecuteDataTable(str3);this.GridView1.DataSource=table3;this.GridView1.DataBind();stringstr4="dropviewbasic";sqlHelper.ExecuteNonQueryAutoCommit(str4);stringstr5="dropviewtotal";sqlHelper.ExecuteNonQueryAutoCommit(str5);stringstr6="dropviewscore_table";sqlHelper.ExecuteNonQueryAutoCommit(str6);
publicstaticstringconnStr=ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;publicstaticintExecuteNonQuery(stringsql,paramsSqlParameter[]parameters){using(SqlConnectionconn=newSqlConnection(connStr)){conn.Open();using(SqlCommandcmd=conn.CreateCommand()){cmd.CommandText=sql;foreach(SqlParameterparameterinparameters){cmd.Parameters.Add(parameter);}returncmd.ExecuteNonQuery();}}}publicstaticDataTableExecuteDataTable(stringsql,paramsSqlParameter[]parameters){using(SqlConnectionconn=newSqlConnection(connStr)){conn.Open();using(SqlCommandcmd=conn.CreateCommand()){cmd.CommandText=sql;foreach(SqlParameterparameterinparameters){cmd.Parameters.Add(parameter);}SqlDataAdapteradapter=newSqlDataAdapter(cmd);DataSetda=newDataSet();adapter.Fill(da);DataTabletable=da.Tables[0];returntable;}}}publicstaticintExecuteNonQueryAutoCommit(stringsql,paramsSqlParameter[]parameters){intiRet=-1;using(SqlConnectionconn=newSqlConnection(connStr)){conn.Open();using(SqlCommandcmd=conn.CreateCommand()){cmd.CommandText=sql;foreach(SqlParameterparameterinparameters){cmd.Parameters.Add(parameter);}iRet=cmd.ExecuteNonQuery();}conn.commit();}returniRet;}