c#和access2003匹配日期时数据类型不匹配错误的问题 [英] problem of datatype mismatch error while matching date with c# and access2003

问题描述

我是asp的新手.
建设一个酒店网站.我需要将asp窗口中的日期签入与签出日期进行比较
在访问数据库中.结帐是访问中的日期/时间.通过数据网格显示结果.但是当我只是比较它时，只需给出数据类型不匹配条件表达式即可.我正确地连接访问数据库.信息提供了不同的查询.我使用日历通过文本框传递日期.
这是我的代码.

i m new to asp.
building a site for hotel. where i need to compare the date checkin which is in asp window with checkout date
in access database.the checkout is date/time in access. via a data grid result is shown. but when i m just compare it just give data type mismatch criteria expression. i rightly connect access database. info provide for different query. i use a calender to pass the date via textbox.
these is my code.

protected void Button1_Click(object sender, EventArgs e)
    {
        ds = new DataSet();
        st ="select * from abc where status = ''v''and checkout= ''"  convert.ToDateTime(TextBox1.Text)"''";
        adp = new OleDbDataAdapter(st, cn);
        int r = adp.Fill(ds, "app");
        GridView1.DataSource = ds;
        GridView1.DataMember = "app";
        GridView1.DataBind();
        int intCount;
        intCount = ds.Tables["app"].Rows.Count;
        Label1.Text = intCount.ToString();

    }
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        TextBox1.Text = Calendar1.SelectedDate.ToString(MM/dd/yy);

       }

非常感谢.
请帮助我.

thanks in advanced.
please help me.

推荐答案

这永远行不通:

This is never going to work:

st ="select * from abc where status = 'v'and checkout= '"  convert.ToDateTime(TextBox1.Text)"'";

首先，它不会编译:

Firstly, it won''t compile:

st ="select * from abc where status = 'v'and checkout= '" + convert.ToDateTime(TextBox1.Text) + "'";

可以，但仍然无法正常工作.

Access(像所有SQL数据库一样)期望它的日期为ISO格式:"yyyy-MM-dd"，但是在连接字符串时，会在DateTime上导致隐式ToString，这将根据当前选择的Culture来为您提供日期.对于英国，则为"dd/MM/yyyy"，对于美国，则为"MM/dd/yyyy"，其中的任意一个均可与Access配合使用.

相反，请使用参数化查询:

Would, but is still not going to work.

Access (like all SQL databases) expects it''s dates in ISO format: "yyyy-MM-dd", but when you concatenate strings you cause an implicit ToString on your DateTime which will give you the date according to the currently select Culture. For the UK, that is "dd/MM/yyyy", for the US "MM/dd/yyyy" - niether of which will work with Access.

Instead, use a Parametrized query:

st ="select * from abc where status = 'v'and checkout=@DT";
adp = new OleDbDataAdapter(st, cn);
adp.SelectCommand.Paramaters.AddWithValue("@DT", convert.ToDateTime(TextBox1.Text));

始终使用参数:不仅可以消除此类问题，而且还可以改善对SQL注入攻击的保护(Google鲍比表")

Always use Parameters: not only to they eliminate problems like this, but they improve your protection against SQL Injection Attacks (Google "Bobby Tables")

您的代码可以是:

your code could be:

protected void Button1_Click(object sender, EventArgs e)
{
    ds = new DataSet();
    st = string.Format("select * from abc where status = 'v' and checkout= '{0}'", Calendar1.SelectedDate);
    adp = new OleDbDataAdapter(st, cn);
    int r = adp.Fill(ds, "app");
    GridView1.DataSource = ds;
    GridView1.DataMember = "app";
    GridView1.DataBind();
    int intCount;
    intCount = ds.Tables["app"].Rows.Count;
    Label1.Text = intCount.ToString();
}

这篇关于c#和access2003匹配日期时数据类型不匹配错误的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！