如下demo:
protected void Page_Load(object sender, EventArgs e)
{
string datetime = Session["dateTime"].ToString(); string sql = "select * from Score where date='" + datetime + "'";
GridView1.DataSource = DBHelper.GetDataTable(sql);
//注意查询和update的时候都要加上where 日期
//数据库中加一列日期列
}现在数据库中Score表我已经增加了字段datetime这一列,下面是我之前未增加datetime这一列字段的score结构表:
DeptID int Checked
PID varchar(50) Unchecked
VoteID varchar(50) Unchecked
SCORE_1 numeric(18, 0) Checked
SCORE_2 numeric(10, 0) Checked
SCORE_3 numeric(10, 0) Checked
SCORE_4 numeric(10, 0) Checked
SCORE_5 numeric(10, 2) Checked
SCORE_6 numeric(10, 0) Checked
SCORE_Total numeric(10, 2) Checked
Identities varchar(1) Checked
staff表:StaffId varchar(50) Unchecked
Name varchar(50) Checked
DeptID int Checked
PositionID int Checked
Positions varchar(20) Checked
UntilTime varchar(20) Checked
LeadStaff int Checked
IDcard varchar(21) Checked
Sex varchar(2) Checked
Birthday varchar(10) Checked
Nationa varchar(2) Checked
school varchar(20) Checked
GraduationTime varchar(10) Checked
Professional varchar(20) Checked
Technology varchar(20) Checked
Qualifications varchar(20) Checked
Does varchar(2) Checked
TimExpired varchar(10) Checked
Years varchar(10) Checked
Res varchar(50) Checked
Unchecked
之前的select查询语句:sql = string.Format("select name as 员工姓名,score_1 as '{0}',score_2 as '{1}',score_3 as '{2}',score_4 as '{3}',score_total as 合计 from dbo.Score join staff on Score.pid=staff.staffid where (staff.deptid={4} or staff.deptid={5}) and LeadStaff=0 union "
+ " select Name as 员工姓名,null,null,null,null,null from staff where staffid not in(select pid from score) and (deptid={4} or deptid={5}) and LeadStaff=0 ", dt.Rows[0][0].ToString(), dt.Rows[1][0].ToString(), dt.Rows[2][0].ToString(), dt.Rows[3][0].ToString(), "40", "39");
}
现在因为又增加一条datetime记录,我怎么修改上面的select语句实现:
页面上我选择dropdownlist的月份数字后,可以将这个月份数字插入到score表中的新增加的datetime这一字段中
protected void Page_Load(object sender, EventArgs e)
{
string datetime = Session["dateTime"].ToString(); string sql = "select * from Score where date='" + datetime + "'";
GridView1.DataSource = DBHelper.GetDataTable(sql);
//注意查询和update的时候都要加上where 日期
//数据库中加一列日期列
}现在数据库中Score表我已经增加了字段datetime这一列,下面是我之前未增加datetime这一列字段的score结构表:
DeptID int Checked
PID varchar(50) Unchecked
VoteID varchar(50) Unchecked
SCORE_1 numeric(18, 0) Checked
SCORE_2 numeric(10, 0) Checked
SCORE_3 numeric(10, 0) Checked
SCORE_4 numeric(10, 0) Checked
SCORE_5 numeric(10, 2) Checked
SCORE_6 numeric(10, 0) Checked
SCORE_Total numeric(10, 2) Checked
Identities varchar(1) Checked
staff表:StaffId varchar(50) Unchecked
Name varchar(50) Checked
DeptID int Checked
PositionID int Checked
Positions varchar(20) Checked
UntilTime varchar(20) Checked
LeadStaff int Checked
IDcard varchar(21) Checked
Sex varchar(2) Checked
Birthday varchar(10) Checked
Nationa varchar(2) Checked
school varchar(20) Checked
GraduationTime varchar(10) Checked
Professional varchar(20) Checked
Technology varchar(20) Checked
Qualifications varchar(20) Checked
Does varchar(2) Checked
TimExpired varchar(10) Checked
Years varchar(10) Checked
Res varchar(50) Checked
Unchecked
之前的select查询语句:sql = string.Format("select name as 员工姓名,score_1 as '{0}',score_2 as '{1}',score_3 as '{2}',score_4 as '{3}',score_total as 合计 from dbo.Score join staff on Score.pid=staff.staffid where (staff.deptid={4} or staff.deptid={5}) and LeadStaff=0 union "
+ " select Name as 员工姓名,null,null,null,null,null from staff where staffid not in(select pid from score) and (deptid={4} or deptid={5}) and LeadStaff=0 ", dt.Rows[0][0].ToString(), dt.Rows[1][0].ToString(), dt.Rows[2][0].ToString(), dt.Rows[3][0].ToString(), "40", "39");
}
现在因为又增加一条datetime记录,我怎么修改上面的select语句实现:
页面上我选择dropdownlist的月份数字后,可以将这个月份数字插入到score表中的新增加的datetime这一字段中
}
sql = string.Format("update Score set datetime ={0}
where Name = {1}",mytime,name)