OleDbCommand数据和时间 [英] OleDbCommand Data and Time

问题描述

大家好，

我正在尝试从我的应用程序运行以下查询，以使用数据库中具有指定日期范围之间的记录的记录来填充gridview.

Hi All,

I am trying to runn the following query from my App to fill a gridview with records from a Database with records between specified date ranges.

DataTable timeEntries = new DataTable("tblTimeSheetDetails");
OleDbCommand selectAlltimeEntries = conn.CreateCommand();
selectAlltimeEntries.CommandText = "SELECT [tblTimesheetDetails].[EmployeeID], [tblTimesheetDetails].[TSProjectCodeID], [tblTimesheetDetails].[TimeSheetDate], [tblTimesheetDetails].[HoursWorked] FROM [tblTimeSheetDetails]";
//conn.Open();
timeEntries.Load(selectAlltimeEntries.ExecuteReader(CommandBehavior.CloseConnection));
DataView dv = new DataView(timeEntries, "EmployeeID = ''?'' AND TimeSheetDate >= ''#" + mondaydate + "#'' AND TimeSheetDate <= ''#" + sundaydate + "#''", "TimeSheetDate", DataViewRowState.CurrentRows);
OleDbParameter p1 = new OleDbParameter();
OleDbParameter p2 = new OleDbParameter();
OleDbParameter p3 = new OleDbParameter();
p1.Value = statuslblUsername.Text;
p2.Value = mondaydate.ToString();
p3.Value = sundaydate.ToString();
//MessageBox.Show({0}, p1.ToString());
dataGridView1.DataSource = dv;

推荐答案

创建命令
在命令文本中使用参数(?)
向命令添加参数(转换为匹配的数据类型)
填充表
使用表进行数据绑定

我没有对真实的数据库进行任何测试，但是它应该可以帮助您开始.

不要忘记添加错误处理！

这是一个示例代码:

Create the command
Use Parameters (?) in command text
Add parameters to command (convert to matching data type)
Fill table
Use Table for data binding

I did no tests with a real database, but it should help you to start.

Dont forget to add error handling!

Here''s a sample code:

using (OleDbConnection conn = new OleDbConnection("CONNECTIONSTRING"))
{
    OleDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT [tblTimesheetDetails].[EmployeeID], [tblTimesheetDetails].[TSProjectCodeID], [tblTimesheetDetails].[TimeSheetDate], [tblTimesheetDetails].[HoursWorked] FROM [tblTimeSheetDetails] where EmployeeID=? AND TimeSheetDate BETWEEN ? AND ?";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(new OleDbParameter("p1", int.Parse(statuslblUsername.Text)));
    cmd.Parameters.Add(new OleDbParameter("p2", DateTime.Parse(mondaydate.Text)));
    cmd.Parameters.Add(new OleDbParameter("p3", DateTime.Parse(sundaydate.Text)));

    conn.Open();
    DataTable timeEntries = new DataTable("tbl");
    timeEntries.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
    dataGridView1.DataSource = timeEntries;
}

大家好.我想我可能会无意中忽略了这个问题.我设法解决了这个问题.再次感谢您的建议.

下面的代码似乎对我有用.

Hi Guys. I think I may have inadvertantly left out the question. I have managed to resolve this. Thanks again for the advise.

The below bit of code seem to have worked for me.

DateTime now = DateTime.Now;
int dayOfWeek = (int)now.DayOfWeek;
dayOfWeek = dayOfWeek == 0 ? 7 : dayOfWeek;
DateTime startOfWeek = now.AddDays(1 - (int)now.DayOfWeek);
DateTime sun = now.AddDays(7 - (int)now.DayOfWeek);
string mondaydate = startOfWeek.ToString("yyyy/MM/dd");
string sundaydate = sun.ToString("yyyy/MM/dd");
DateTime mondaydate1 = Convert.ToDateTime(startOfWeek.ToString("yyyy/MM/dd"));
string sql = @"select EmployeeID, TSProjectCodeID, TimeSheetDate, HoursWorked, TaskCode, Notes from tblTimesheetDetails WHERE EmployeeID = ''" + statuslblUsername.Text + "'' AND TimeSheetDate BETWEEN ''" + mondaydate + "'' AND ''" + sundaydate + "''";

这篇关于OleDbCommand数据和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！