从我的SQL数据库获取记录,其中日期字段大于当前日期时间约60天。 [英] Getting records from my SQL db where the date field is greater than current datetime about 60 days.
问题描述
这里我想要数据库中的记录...其中db和当前日期时间的差异b / w日期字段大于60天...然后我想显示来自db的记录。
在这里我粘贴了代码。请在查询中告诉我任何修改。
[System.Web.Services.WebMethod]
public static 列表< string> GetCompletionList( string prefixText, int count)
{
using (MySqlConnection con = new MySqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings [ Mysqlcon]。ConnectionString;
使用(MySqlCommand com = new MySqlCommand())
{
// 从pincode中选择Pincode,其中Pincode LIKE'%+ prefixText.ToString( )+
com.CommandText = 从债务支出中选择Cin,其中Cin LIKE'% + prefixText.ToString()+ %'AND Time< NOW() - INTERVAL 60 DAY 跨度>;
com.Parameters.AddWithValue( @ Search,prefixText);
com.Connection = con;
con.Open();
List< string> FullNames = new List< string>();
使用(MySqlDataReader sdr = com.ExecuteReader())
{
while (sdr.Read())
{
FullNames.Add(sdr [ 。CIN跨度>]的ToString());
}
}
con.Close();
return FullNames;
}
}
}
我尝试了什么:
[System.Web.Services.WebMethod]
public static List< string> GetCompletionList(string prefixText,int count)
{
using(MySqlConnection con = new MySqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings [Mysqlcon] .ConnectionString;
使用(MySqlCommand com = new MySqlCommand())
{
//从pincode中选择Pincode,其中Pincode LIKE'%+ prefixText.ToString()+
com.CommandText = 选择Cin来自债务支持Cin LIKE'%+ prefixText.ToString()+%'AND Time< NOW() - INTERVAL 60 DAY;
com.Parameters.AddWithValue(@ Search,prefixText);
com.Connection = con;
con.Open();
List< string> FullNames = new List< string>();
使用(MySqlDataReader sdr = com.ExecuteReader())
{
while(sdr。阅读())
{
FullNames.Add(sdr [Cin]。ToString());
}
}
con.Close();
返回FullNames;
}
}
}
首先不要这样做!不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度,这可能会破坏您的整个数据库。请改用参数化查询。
并尝试:
... WHERE ... AND DATEDIFF(dd,TimeColumn,GETDATE())> = 60 )
Here i want records from database...where the difference b/w date field in db and current datetime is grater than 60 days...then i want display that records from db.
Here i am pasted the code.Please advice me any modifications changes in the Query.
[System.Web.Services.WebMethod]
public static List<string> GetCompletionList(string prefixText, int count)
{
using (MySqlConnection con = new MySqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["Mysqlcon"].ConnectionString;
using (MySqlCommand com = new MySqlCommand())
{
//select Pincode from pincode where Pincode LIKE '%"+prefixText.ToString()+ "
com.CommandText = "Select Cin from debtpending where Cin LIKE '%" + prefixText.ToString() + "%' AND Time < NOW() - INTERVAL 60 DAY";
com.Parameters.AddWithValue("@Search", prefixText);
com.Connection = con;
con.Open();
List<string> FullNames = new List<string>();
using (MySqlDataReader sdr = com.ExecuteReader())
{
while (sdr.Read())
{
FullNames.Add(sdr["Cin"].ToString());
}
}
con.Close();
return FullNames;
}
}
}
What I have tried:
[System.Web.Services.WebMethod]
public static List<string> GetCompletionList(string prefixText, int count)
{
using (MySqlConnection con = new MySqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["Mysqlcon"].ConnectionString;
using (MySqlCommand com = new MySqlCommand())
{
//select Pincode from pincode where Pincode LIKE '%"+prefixText.ToString()+ "
com.CommandText = "Select Cin from debtpending where Cin LIKE '%" + prefixText.ToString() + "%' AND Time < NOW() - INTERVAL 60 DAY";
com.Parameters.AddWithValue("@Search", prefixText);
com.Connection = con;
con.Open();
List<string> FullNames = new List<string>();
using (MySqlDataReader sdr = com.ExecuteReader())
{
while (sdr.Read())
{
FullNames.Add(sdr["Cin"].ToString());
}
}
con.Close();
return FullNames;
}
}
}
Start by not doing it like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
And try:
... WHERE ... AND DATEDIFF(dd, TimeColumn, GETDATE()) >= 60)
这篇关于从我的SQL数据库获取记录,其中日期字段大于当前日期时间约60天。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!