C#对ACCESS数据库的时间段查询问题

Question

con.Open();
            OdbcCommand cmd = new OdbcCommand("Select count(*) from time where shijian between #"& dateTimePicker1.Value&"# and #"&dateTimePicker2.Value&"#", con);
            int count = Convert.ToInt32(cmd.ExecuteScalar());
            MessageBox.Show(Convert.ToString(count));
            con.Close();
提示FROM子语句语法错误！郁闷大家帮忙看看。

Answer 1

在ACCESS里面的shijian属性是日期/时间

Answer 2

对dateTimePicker1.Value进行格式化一下试试
换成
string.Format("{0:yyyy-MM-dd}",dateTimePicker1.Value)

Answer 3

& 连接好像 VB里面连接字符串的
con.Open();
            OdbcCommand cmd = new OdbcCommand("Select count(*) from time where shijian between #"& dateTimePicker1.Value&"# and #"&dateTimePicker2.Value&"#", con);
            int count = Convert.ToInt32(cmd.ExecuteScalar());
            MessageBox.Show(Convert.ToString(count));
            con.Close(); 
换成            con.Open();
            OdbcCommand cmd = new OdbcCommand( "Select count(*) from time where shijian between #" + string.Format("{0:yyyy-MM-dd}",dateTimePicker1.Value) + "# and #" +  string.Format("{0:yyyy-MM-dd}",dateTimePicker2.Value) + "#", con );
            int count = Convert.ToInt32( cmd.ExecuteScalar() );
            MessageBox.Show( Convert.ToString( count ) );
            con.Close(); 

Answer 4

con.Open(); 
            OdbcCommand cmd = new OdbcCommand("Select count(*) from time where shijian between # dateTimePicker1.Value()# and #dateTimePicker2.Value()#", con); 
            int count = Convert.ToInt32(cmd.ExecuteScalar()); 
            MessageBox.Show(Convert.ToString(count)); 
            con.Close(); 

Answer 5

            con.Open();
            OdbcCommand cmd = new OdbcCommand( "Select count(*) as iCount from [time] where shijian between #" + string.Format("{0:yyyy-MM-dd}",dateTimePicker1.Value) + "# and #" +  string.Format("{0:yyyy-MM-dd}",dateTimePicker2.Value) + "#", con );
            int count = Convert.ToInt32( cmd.ExecuteScalar() );
            MessageBox.Show( Convert.ToString( count ) );
            con.Close(); 试试，可能time字段名与系统的关键字有冲突

Answer 6

"Select count(*) as iCount from [time] where shijian between #" + dateTimePicker1.ToShortDateString() + "#  and  #" +  dateTimePicker2.ToShortDateString()  + "#", 
有些数据库不支持between关键字的话
改为
"Select count(*) as iCount from [time] where shijian < #" + dateTimePicker1.ToShortDateString() + "#  and shijian > #" +  dateTimePicker2.ToShortDateString()  + "#",