“/OnLineExam”应用程序中的服务器错误。
--------------------------------------------------------------------------------列名 'student' 无效。
说明: 执行当前 Web 请求期间,出现未经处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 列名 'student' 无效。源错误:
行 299: SqlDataAdapter adapter = new SqlDataAdapter(Cmd);
行 300: DataSet dataset = new DataSet();
行 301: adapter.Fill(dataset);
行 302: Close();
行 303: return dataset;
源文件: d:\My Documents\桌面\Projects\OnLineExam\App_Code\DataAccessLayer\DataBase.cs 行: 301
--------------------------------------------------------------------------------列名 'student' 无效。
说明: 执行当前 Web 请求期间,出现未经处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 列名 'student' 无效。源错误:
行 299: SqlDataAdapter adapter = new SqlDataAdapter(Cmd);
行 300: DataSet dataset = new DataSet();
行 301: adapter.Fill(dataset);
行 302: Close();
行 303: return dataset;
源文件: d:\My Documents\桌面\Projects\OnLineExam\App_Code\DataAccessLayer\DataBase.cs 行: 301
把调试到的sql文本,复制到数据库执行下.
然后就比较了...
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using OnLineExam.BusinessLogicLayer;
using System.Data.SqlClient;
using OnLineExam.DataAccessLayer;
public partial class Web_UserPaper : System.Web.UI.Page
{
private int paperid;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string loginName = Session["UserID"].ToString();
Users user = new Users();
user.LoadData(loginName);
labUser.Text = user.UserName;
InitData();
}
}
//初始化试卷,从数据库中将试题取出
protected void InitData()
{
string userid = Request.QueryString["UserID"].ToString();
paperid = int.Parse(Request.QueryString["PaperID"].ToString());
DataBase DB = new DataBase();
DataSet ds = DB.GetDataSetSql("select * from [UserAnswer] where UserID='" + userid + "'");
DataRow[] row = ds.Tables[0].Select();
foreach (DataRow rs in row)
{
lblExamtime.Text = rs["ExamTime"].ToString();
}
SqlParameter[] Params1 = new SqlParameter[3];
Params1[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperid);
Params1[1] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, "单选题");
Params1[2] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, userid);
DataSet ds1 = DB.GetDataSet("Proc_UserAnswer", Params1);
GridView1.DataSource = ds1;
GridView1.DataBind();
((Label)GridView1.HeaderRow.FindControl("Label27")).Text = ((Label)GridView1.Rows[0].FindControl("Label4")).Text;
SqlParameter[] Params2 = new SqlParameter[3];
Params2[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperid);
Params2[1] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, "多选题");
Params2[2] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, userid);
DataSet ds2 = DB.GetDataSet("Proc_UserAnswer", Params2);
GridView2.DataSource = ds2;
GridView2.DataBind();
((Label)GridView2.HeaderRow.FindControl("Label28")).Text = ((Label)GridView2.Rows[0].FindControl("Label8")).Text;
后面的就省略了
下面的是我写的数据库基类:
using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OnLineExam.BusinessLogicLayer;
using OnLineExam.DataAccessHelper;
using OnLineExam.CommonComponent;namespace OnLineExam.DataAccessLayer{
// 数据库接口类
public class DataBase
{
//私有变量,数据库连接
protected SqlConnection Connection;
protected string ConnectionString; //构造函数
public DataBase()
{
ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}
//保护方法,打开数据库连接
private void Open()
{
//判断数据库连接是否存在
if (Connection == null)
{
//不存在,新建并打开
Connection = new SqlConnection(ConnectionString);
Connection.Open();
}
else
{
//存在,判断是否处于关闭状态
if (Connection.State.Equals(ConnectionState.Closed))
Connection.Open(); //连接处于关闭状态,重新打开
}
} //公有方法,关闭数据库连接
public void Close()
{
if (Connection.State.Equals(ConnectionState.Open))
{
Connection.Close(); //连接处于打开状态,关闭连接
}
} /// <summary>
/// 析构函数,释放非托管资源
/// </summary>
~DataBase()
{
try
{
if (Connection != null)
Connection.Close();
}
catch{}
try
{
Dispose();
}
catch{}
} //公有方法,释放资源
public void Dispose()
{
if (Connection != null) // 确保连接被关闭
{
Connection.Dispose();
Connection = null;
}
} //公有方法,根据Sql语句,返回是否查询到记录
public bool GetRecord(string XSqlString)
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(XSqlString, Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close(); if (dataset.Tables[0].Rows.Count > 0)
{
return true;
}
else
{
return false;
}
} //公有方法,返回Sql语句获得的数据值
//SqlString的格式:select count(*) from XXX where ...
// select max(XXX) from YYY where ...
public int GetRecordCount(string XSqlString)
{
string SCount; Open();
SqlCommand Cmd = new SqlCommand(XSqlString,Connection);
SCount = Cmd.ExecuteScalar().ToString().Trim();
if (SCount=="")
SCount="0";
Close();
return Convert.ToInt32(SCount);
} //公有方法,根据XWhere更新数据表XTableName中的某些纪录
//XTableName--表名
//XHT--哈希表,键为字段名,值为字段值
public DataSet AdvancedSearch(string XTableName, Hashtable XHT)
{
int Count = 0; string Fields = "";
foreach(DictionaryEntry Item in XHT)
{
if (Count != 0)
{
Fields += " and ";
}
Fields += Item.Key.ToString();
Fields += " like '%";
Fields += Item.Value.ToString();
Fields += "%'";
Count++;
}
Fields += " "; string SqlString = "select * from " + XTableName + " where " + Fields;
Open();
SqlDataAdapter Adapter = new SqlDataAdapter(SqlString, Connection);
DataSet Ds = new DataSet();
Adapter.Fill(Ds);
Close();
return Ds;
} //私有方法,获得一个用来调用存储过程的SqlCommand
//输入:
// ProcName - 存储过程名
// Params - 用来调用存储过程的参数表
private SqlCommand CreateCommand(string ProcName, SqlParameter[] Prams)
{
Open();
SqlCommand Cmd = new SqlCommand(ProcName, Connection);
Cmd.CommandType = CommandType.StoredProcedure; if (Prams != null)
{
foreach (SqlParameter Parameter in Prams)
Cmd.Parameters.Add(Parameter);
} return Cmd;
} //公有方法,实例化一个用于调用存储过程的参数
//输入:
// ParamName - 参数名称
// DbType - 参数类型
// Size - 参数大小
// Direction - 传递方向
// Value - 值
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter Param; if(Size > 0)
Param = new SqlParameter(ParamName, DbType, Size);
else Param = new SqlParameter(ParamName, DbType); Param.Direction = Direction; if (Value != null)
Param.Value = Value; return Param;
} //公有方法,实例化一个用于调用存储过程的输入参数
//输入:
// ParamName - 参数名称
// DbType - 参数类型
// Size - 参数大小
// Value - 值
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
} //公有方法,调用存储过程(不带参数)
//输入:
// ProcName存储过程名
//输出:
// 对Update、Insert、Delete操作返回影响到的行数,其他情况为-1
public int RunProc(string ProcName)
{
int Count = -1;
SqlCommand Cmd = CreateCommand(ProcName, null);
Count = Cmd.ExecuteNonQuery();
Close();
return Count;
} //公有方法,调用存储过程(带参数)
//输入:
// ProcName - 存储过程名
// Params - 用来调用存储过程的参数表
//输出:
// 对Update、Insert、Delete操作返回影响到的行数,其他情况为-1
public int RunProc(string ProcName, SqlParameter[] Params)
{
int Count = -1;
SqlCommand Cmd = CreateCommand(ProcName, Params);
Count = Cmd.ExecuteNonQuery();
Close();
return Count;
} //公有方法,调用存储过程(不带参数)
//输入:
// ProcName存储过程名
//输出:
// 将执行结果以SqlDataReader返回
//注意:使用后主意调用SqlDataReader.Close()方法
public SqlDataReader RunProcGetReader(string ProcName)
{
SqlCommand Cmd = CreateCommand(ProcName, null);
return Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
} //公有方法,调用存储过程(带参数)
//输入:
// ProcName - 存储过程名
// Params - 存储过程需要的参数
//输出:
// 将执行结果以SqlDataReader返回
//注意:使用后主意调用SqlDataReader.Close()方法
public SqlDataReader RunProcGetReader(string ProcName, SqlParameter[] Params)
{
SqlCommand Cmd = CreateCommand(ProcName, Params);
return Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
} //公有方法,调用存储过程(带参数)
//输入:
// ProcName - 存储过程名
// Params - 存储过程需要的参数
//输出:
// 将执行结果以SqlDataReader返回
//注意:使用后主意调用SqlDataReader.Close()方法
public int RunProcGetCount(string ProcName, SqlParameter[] Params)
{
SqlCommand Cmd = CreateCommand(ProcName, Params);
string SCount;
SCount = Cmd.ExecuteScalar().ToString().Trim();
if (SCount == "")
SCount = "0";
Close();
return Convert.ToInt32(SCount);
} //公有方法,调用存储过程(不带参数)
//输入:
// ProcName存储过程名
//输出:
// 将执行结果以DataSet返回
public DataSet GetDataSet(string ProcName)
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(ProcName, Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
} //公有方法,调用存储过程(不带参数)
//输入:
// ProcName存储过程名
//输出:
// 将执行结果以DataSet返回
public DataSet GetDataSet(string ProcName, SqlParameter[] Params)
{
Open();
SqlCommand Cmd = CreateCommand(ProcName, Params);
SqlDataAdapter adapter = new SqlDataAdapter(Cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close(); return dataset;
}
//公有方法,根据Sql语句,返回一个结果数据集
public DataSet GetDataSetSql(string XSqlString)
{
Open();
SqlDataAdapter Adapter = new SqlDataAdapter(XSqlString, Connection);
DataSet Ds = new DataSet();
Adapter.Fill(Ds);
Close();
return Ds;
}
//公有方法,根据Sql语句,插入记录
public int Insert(string XSqlString)
{
int Count = -1;
Open();
SqlCommand cmd = new SqlCommand(XSqlString, Connection);
Count = cmd.ExecuteNonQuery();
Close();
return Count;
}
PROCEDURE [dbo].[Proc_UserAnswer]
(@PaperID int,
@Type varchar(10),
@UserID varchar(50))
AS
begin
declare @sql nvarchar(1000)
if @Type='单选题'
begin
set @sql='select * from UserAnswer,SingleProblem
where [Type]=''单选题'' and
UserAnswer.TitleID=SingleProblem.ID and [PaperID]= ' +Cast(@PaperID AS varchar(10)) + ' and
[UserID]= ' + @UserID
exec sp_executesql @sql
end
else if @Type='多选题'
begin
set @sql='select * from UserAnswer,MultiProblem
where [Type]=''多选题'' and
UserAnswer.TitleID=MultiProblem.ID and [PaperID]= ' +Cast(@PaperID AS varchar(10)) + ' and
[UserID]= ' + @UserID
exec sp_executesql @sql
end
else if @Type='判断题'
begin
set @sql='select * from UserAnswer,JudgeProblem
where [Type]=''判断题'' and
UserAnswer.TitleID=JudgeProblem.ID and [PaperID]= ' +Cast(@PaperID AS varchar(10)) + ' and
[UserID]= ' + @UserID
exec sp_executesql @sql
end
else if @Type='填空题'
begin
set @sql='select * from UserAnswer,FillBlankProblem
where [Type]=''填空题'' and
UserAnswer.TitleID=FillBlankProblem.ID and [PaperID]= ' +Cast(@PaperID AS varchar(10)) + ' and
[UserID]= ' + @UserID
exec sp_executesql @sql
end
else if @Type='问答题'
begin
set @sql='select * from UserAnswer,QuestionProblem
where [Type]=''问答题'' and
UserAnswer.TitleID=QuestionProblem.ID and [PaperID]= ' +Cast(@PaperID AS varchar(10)) + ' and
[UserID]= ' + @UserID
exec sp_executesql @sql
end
@UserID = 'student'
拼接的sql中也要用转义单引号把参数引起来。
UserAnswer,MultiProblem
UserAnswer,JudgeProblem
UserAnswer,FillBlankProblem
UserAnswer,QuestionProblem这些表 是不是包含相同 student 字段 如果是,可以在联合查询的时候 select UserAnswer.student as student from ....
这样处理一下
在SQL 语句的地方打个断点!~~然后放到SQLServer里面运行一下就知道那里写错了!~~~
建议:先检查最基本的东西,比如查询语句真的没错?是不是链接错了数据库(有时你有服务器数据库和本地数据库)。当你确定你写的代码没问题,就换一个DBhelper吧。还不行,出绝招:双手交叉合拢,70度向上仰望天空,大呼:GOD SAVE ME!!
看函数是否为public static DataSet GetDataSet(string sql,string table)
要是这样adapter.Fill(dataset,"表名");
'student'列所在的表!
用英文全角写列名会报列名无效,改用英文半角.....
是不是很雷人呢
不知道对楼主有没有帮助