//查询按钮
private void btnSearch_Click(object sender, EventArgs e)
{
this.dgvDateReport.DataSource = null; //清空日报
try
{
//string strRow = "vc_EmployeeName";
//string strRowTitle = "姓名";
string strRowCondtion = " where " + GetSearchStringRow(); //获得查询条件 " where vc_EmployeeName='小李'";
string strColCondtion = " where " + GetSearchStringCol(); //获得查询条件 " where vc_EmployeeName='小李'";
//执行存储过程建立日报表
DBOperater.ExecuteSPCreatDayReaport(strRowCondtion, strColCondtion);
dgvDateReport.DataSource = null; //清空数据
string strSQL = "select * from temp";
DataSet dt = SQLHelper.ExecuteDataSet(strSQL);
if (dt != null)
{
dgvDateReport.DataSource = dt.Tables[0].DefaultView; //查询已处理的短信列表SMS_DateReport
}
kryptonHeaderGroup2.ValuesSecondary.Heading = "当前记录: " + dgvDateReport.Rows.Count.ToString() + " 条";
}
catch (Exception ex)
{
DBOperater.LogWriter("SMS.Views.LoadBill error:" + ex.Message);
}
}
//获取列查询字符串
private string GetSearchStringCol()
{
string strTemp = "";
if (chkStart.Checked)
{
strTemp += "dt_ReceiveTime >='" + dtpStart.Value.Date + "' and ";
}
if (chkEnd.Checked)
{
strTemp += "dt_ReceiveTime <='" + dtpEnd.Value.Date + "' and ";
}
strTemp += "1=1";
return strTemp;
}
public static bool ExecuteSPCreatDayReaport(string strRowCondtion, string strColCondtion) //SqlParameter
{
SqlParameter[] strSqlParameter = {
new SqlParameter("@strTabName",SqlDbType.VarChar,50),
new SqlParameter("@strTemTabName",SqlDbType.VarChar,50),
new SqlParameter("@strCol",SqlDbType.VarChar,50),
new SqlParameter("@strRow",SqlDbType.VarChar,50),
new SqlParameter("@strRowTitle",SqlDbType.VarChar,50),
new SqlParameter("@strRowCondtion",SqlDbType.VarChar,50),
//new SqlParameter("@strColCondtion",SqlDbType.VarChar,50) //原因在此处,定义的字符串太小,导致字符串被截断
new SqlParameter("@strColCondtion",SqlDbType.VarChar,500) //定义大一点,成功
};
strSqlParameter[0].Value = "SMS_DateReport";
strSqlParameter[1].Value = "temp";
strSqlParameter[2].Value = "left(convert(varchar(20),dt_ReceiveTime,120),10)";
strSqlParameter[3].Value = "vc_EmployeeName";
strSqlParameter[4].Value = "姓名"; //strRowTitle; //"姓名";
strSqlParameter[5].Value = strRowCondtion; // " where vc_EmployeeName='小李'";
strSqlParameter[5].Value = strColCondtion; // " where left(convert(varchar(20),dt_ReceiveTime,120),10)<=''2012-02-28''';
if (SQLHelper.ExecuteSQLStoredProcedure(strSqlParameter, "CreatDayReaport") > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 直接执行一条简单的sql语句
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns></returns>
public static int ExecuteSQLStoredProcedure(SqlParameter[] prams,string spName)
{
//if (DBConnectionCheck() == "连接成功!")
//{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter Prams in prams)
{
cmd.Parameters.Add(Prams);
}
int val = cmd.ExecuteNonQuery(); //此处出错“在应使用条件的上下文(在 'dt_Receiv' 附近)中指定了非布尔类型的表达式”
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return val;
}
catch (Exception ex)
{
DBOperater.LogWriter("SQLHelper.ExecuteSQLStoredProcedure error:" + ex.Message);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return -1;
}
}
}
出现此错误的原因