如何在虚拟表的两个日期选择器之间获取数据(占位符)
本文关键字:日期 两个 选择器 之间 占位符 数据 获取 虚拟 | 更新日期: 2023-09-27 18:20:20
所以我的C#代码中有一条sql语句,用于尝试在两个日期范围之间提取数据。唯一的问题是,什么都没有出现。
OpenConnection(conn);
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("Select CampaignName AS 'CAMPAIGN NAME', campaignDescription AS 'CAMPAIGN DESCRIPTION', CASE WHEN EndDate >= GETDATE() and StartDate <= GETDATE() THEN 'ACTIVE' WHEN StartDate >= GETDATE() THEN 'PENDING' ELSE 'CLOSED' END as 'CURRENT STATUS', CONVERT(VARCHAR(11), StartDate,106) + ' - ' + CONVERT(VARCHAR(11),EndDate,106) AS 'CAMPAIGN DATES', Discount AS 'DISCOUNT', [Target] AS 'TARGET', Uptake AS 'UPTAKE', AddedBy AS 'ADDED BY', DateAdded AS 'DATE ADDED' FROM Tbl_Campaign WHERE startDate BETWEEN @from AND @to", conn);
try
{
SqlParameter param;
param = new SqlParameter("@from", SqlDbType.DateTime);
param.Value = txtStartDate.Text;
cmd.Parameters.Add(param);
param = new SqlParameter("@to", SqlDbType.DateTime);
param.Value = txtDateEnd.Text;
cmd.Parameters.Add(param);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
如果你滚动到我的Sql命令的末尾,你会看到我的截止日期和截止日期的两个变量。我尝试在SQL中执行这段代码,如果我用''
引号手动解析日期,它就会起作用。我认为这就是它不检索数据的原因。有人能帮我处理这个请求吗?
我使用了一个文本框来获取日期,因为您的代码中包含"txtStartDate.text"。因此,我尝试使用简单的查询来获取日期范围内的数据。它运行良好。所以,我认为您的查询中存在另一个问题。这是我尝试过的代码:
con = new SqlConnection();
string _connectionString = GetConnectionString();
con.ConnectionString = _connectionString;
con.Open();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("SELECT * FROM FORDSUMD WHERE Dated BETWEEN @from AND @to",con);
try
{
SqlParameter param;
param = new SqlParameter("@from", SqlDbType.DateTime);
param.Value = txtStartDate.Text;
cmd.Parameters.Add(param);
param = new SqlParameter("@to", SqlDbType.DateTime);
param.Value = txtDateEnd.Text;
cmd.Parameters.Add(param);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
public string GetConnectionString(string databaseName = "", string serverName = "")
{
SqlConnectionStringBuilder conString = new SqlConnectionStringBuilder();
if (string.IsNullOrEmpty(serverName))
serverName = ConfigurationManager.AppSettings["ServerName"];
//Assing SQl server name
conString.DataSource = serverName;
conString.InitialCatalog = string.IsNullOrEmpty(databaseName) ? ConfigurationManager.AppSettings["DBName"] : databaseName;
conString.IntegratedSecurity = false;
conString.UserID = ConfigurationManager.AppSettings["UserId"];
conString.Password = ConfigurationManager.AppSettings["Password"];
return conString.ConnectionString;
}
如果要使用DateTimePicker控件而不是文本框控件,只需将txtStartDate.Text替换为dtpStart.Value(DateTimePickr),并将txtDateEnd.Text替换成dtpEnd.Value即可。