错误将varchar数据类型转换为日期时间数据类型会导致超出范围的值 [英] Error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

问题描述

是的，当我在文本框中插入数据时，我遇到了问题。问题是当我正在搜索数据10/10/2010时，它工作得很好但是当我尝试搜索其他日期时（例如25 / 11/2013），我会得到错误将varchar数据类型转换为日期时间数据类型会导致超出范围的值。如果我将日期更改为11/22/2013，我将收到错误，字符串未被识别为有效的DateTime。我的代码有问题吗？我将把错误发生在下面。

 protected void searchdata_Click（object sender，EventArgs e）
 {
 Label1.Text = locID.ToString（）; 
 adddate（）; 
 byday2（）; 
 
} 
 
 public void adddate（）
 {
 today = DateTime.Parse（txtStartDate.Text）; //字符串未被识别为有效的DateTime 
 DateTime answer = today.AddDays（1）; 
 adddate2 = answer; 
} 
 public void byday2（）
 {
 Label9.Text = adddate2.ToShortDateString（）; 
 Label10.Text = txtStartDate.Text; 
 if（Byday.Checked == true）
 {
 if（Maxdata.Checked == true）
 {
 //tabletable.hidden = true; 
 lblmaxdata.Visible = true; 
 lblmaxdata.Text =最高数据; 
 
 string connectionString =Data Source = .\\SQLEXPRESS; AttachDbFilename = C：\\Users\\shafiq\\Desktop\\cuba \\ App_Data\\radiation.mdf; Integrated Security = True; User Instance = True; 
 
 //创建SQLDataSource。 
 SqlDataSource sqlDataSource = new SqlDataSource（）; 
 sqlDataSource.ID =SqlDataSource123; 
 this.Page.Controls.Add（sqlDataSource）; 
 
 //将ConnectionString绑定到SQLDataSource。 
 sqlDataSource.ConnectionString = connectionString; 
 sqlDataSource.SelectCommand =SELECT top 3 [date]，[data] FROM [loc1] WHERE（（[data]> ='2'）AND（[date]> ='+ today + '）AND（[date]<'+ adddate2 +'））ORDER BY [data] DESC，[date] DESC; 
 
 //检索记录后将SQLDataSource绑定到GridView。 
 gridmaxdata.DataSource = sqlDataSource; 
 gridmaxdata.DataBind（）; //将varchar数据类型转换为日期时间数据类型会导致超出范围的值。 
} 
} 
} 

解决方案

问题在于我只调用日期时间数据错。这是正确的代码。

 SqlDataSource.SelectCommand =SELECT top 3 [date]，[data] FROM [loc1] WHERE（（[data]> ='2 '）AND（[date]> ='+ today.ToLongDateString（）+'）AND（[date]<'+ adddate2.ToLongDateString（）+'））ORDER BY [data] DESC，[日期] DESC; 

在配置文件中添加此密钥 -

 <  全球化    enableclientbasedculture   =  true    culture   =  en-GB    uiculture   =   zh    /  >  

此外，在您的查询中传递日期时，请使用日期变量，例如： -



其中fromdate' + Convert.ToDateTime（addDate2）.ToString（yyyy-MM-dd）+'



希望有帮助

hye, i'm having a problem when i'm inserting data in textbox.the problem is when i'm searching for data 10/10/2010, it work perfectly but when i try to search other date (eg 25/11/2013), i would get error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. if i change the date to 11/22/2013, i will get error, String was not recognized as a valid DateTime. is there something wrong with my code? i will put where the error occur below.

protected void searchdata_Click(object sender, EventArgs e)
{
    Label1.Text = locID.ToString();
    adddate();
    byday2();

}

public void adddate()
{
    today = DateTime.Parse(txtStartDate.Text);//String was not recognized as a valid DateTime
    DateTime answer = today.AddDays(1);
    adddate2 = answer;
}
public void byday2()
{
    Label9.Text = adddate2.ToShortDateString();
    Label10.Text = txtStartDate.Text;
    if (Byday.Checked == true)
    {
        if (Maxdata.Checked == true)
        {
            //tablemax.hidden = true;
            lblmaxdata.Visible = true;
            lblmaxdata.Text = "Highest Data";
            
            string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\shafiq\\Desktop\\cuba\\App_Data\\radiation.mdf;Integrated Security=True;User Instance=True";

            // Create SQLDataSource.
            SqlDataSource sqlDataSource = new SqlDataSource();
            sqlDataSource.ID = "SqlDataSource123";
            this.Page.Controls.Add(sqlDataSource);

            // Bind ConnectionString to SQLDataSource.
            sqlDataSource.ConnectionString = connectionString;
            sqlDataSource.SelectCommand = "SELECT top 3 [date], [data] FROM [loc1] WHERE (([data] >= '2') AND ([date] >= '" + today + "') AND ([date] < '" + adddate2 + "')) ORDER BY [data] DESC, [date] DESC";

            // Bind SQLDataSource to GridView after retrieving the records.
            gridmaxdata.DataSource = sqlDataSource;
            gridmaxdata.DataBind(); //The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
        }
    }
}

解决方案

the problem was that i only call for the datetime data wrongly. here is the correct code.

SqlDataSource.SelectCommand = "SELECT top 3 [date], [data] FROM [loc1] WHERE (([data] >= '2') AND ([date] >= '" + today.ToLongDateString() + "') AND ([date] < '" + adddate2.ToLongDateString() + "')) ORDER BY [data] DESC, [date] DESC";

Add this key in your config file -

<globalization enableclientbasedculture="true" culture="en-GB" uiculture="en" />

Also while passing date in your query use the date variable as like for eg:-

where fromdate between ' + Convert.ToDateTime(addDate2).ToString("yyyy-MM-dd") + "'"

Hope it helps

这篇关于错误将varchar数据类型转换为日期时间数据类型会导致超出范围的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！