从字符串转换日期和/或时间时转换失败 [英] Conversion failed when converting date and/or time from character string

问题描述

大家好日子..

我正在尝试将下拉列表值插入/保存到我的数据库。

但是，总是出现转换失败的错误从字符串转换日期和/或时间时......



我在代码中尝试过Convert.ToDateTime（）和DateTime.Parse（）等方法但错误仍然存​​在。



是的..classStart和classEnd的数据类型是DATETIME。



在此先感谢您的帮助。



我尝试过：



这是我的代码...

<前lang =c＃> 受保护 void Page_Load（ object sender，EventArgs e）
{
// 在下拉列表中设置开始时间timedd& timedd2
DateTime StartTime = DateTime.MinValue.AddHours（ 8 ）;
// 在下拉列表中设置结束时间timedd& timedd2
DateTime EndTime = DateTime.MinValue.AddDays（ 1 ）;

for （ var i = StartTime; i < = EndTime; i = i.AddMinutes（ 30 ））
{
timedd.Items.Add （i.ToShortTimeString（））;
timedd2.Items.Add（i.ToShortTimeString（））;
}
}

受保护 void saveSchedule_Click（ object sender，EventArgs e）
{

SqlConnection conn = new SqlConnection（ConfigurationManager.ConnectionStrings [ db]。ConnectionString）;

尝试
{
conn.Open（）;
string addquery = INSERT INTO [Schedule ]（[className]，[classStart]，[classEnd]，[classDes]）VALUES（@ className，@ classStart，@ classEnd，@ classDes）;
SqlCommand cmd = new SqlCommand（addquery，conn）;
cmd.Parameters.AddWithValue（ @ className，className.Text）;
cmd.Parameters.AddWithValue（ @ classStart，Convert.ToDateTime（timedd.SelectedItem） 。值））;
cmd.Parameters.AddWithValue（ @ classEnd，Convert.ToDateTime（timedd2.SelectedItem） 。值））;
cmd.Parameters.AddWithValue（ @ classDes，classdes.Text）;

int rs = cmd.ExecuteNonQuery（）;
if （rs == 1 ）
{
result1。 Visible = true ;
result1.Text = 记录已成功添加！;
Response.AddHeader（ REFRESH， 5; URL = Schedule.aspx）;

}
else
{
result1.Visible = 真;
result1.Text = 请再试一次......;
}

}
catch （例外情况）
{
Response.Write（ 错误： + ex.ToString（））;
}

解决方案

首先使用调试器查看您传递给哪个字段的确切内容。

确定哪个是日期/时间值并查看您传递的确切格式 - 如果它是真实的日期或时间，那么您没事。

转换使用TryParse（或TryParseExact）将其转换为DateTime值，并将转换后的值传递给SQL，而不是从下拉列表中读取的原始值。

很可能是下拉列表中的值是字符串，并且它们没有与SQL Server计算机上配置的日期相同的格式，因此它会抛出异常，因为它无法正确转换它。通过传递DateTime值，可以消除该问题。

首先以正确的格式转换值

 //在这里放置一个断点，看看dateFormat看起来像
 
 var classstartFormat = Convert.ToString（timedd.SelectedItem.Value）; 
 
 //使用您看到的格式，使用DateTime.ParseExact（）将此值正确读入DateTime对象
 DateTime classstart = DateTime.ParseExact（classstartFormat，yyyy-MM- dd，null）; 
 
 //将参数设置为值
 cmd.Parameters.AddWithValue（@ classStart，classstart）; 

Good day everyone..
I am trying to insert/save dropdownlist value to my database.
However, it always comes out with the error which "Conversion failed when converting date and/or time from character string..."

I had tried ways such as Convert.ToDateTime() and DateTime.Parse() in my codes but the error still occurs.

And yes.. the datatype for the "classStart" and "classEnd" are DATETIME.

Thanks in advance for the help.

What I have tried:

Here is my codes...

protected void Page_Load(object sender, EventArgs e)
    {
        // Set the start time in dropdownlist timedd & timedd2
        DateTime StartTime = DateTime.MinValue.AddHours(8);
        // Set the end time in dropdownlist timedd & timedd2
        DateTime EndTime = DateTime.MinValue.AddDays(1);

        for (var i = StartTime; i <= EndTime; i = i.AddMinutes(30))
        {
            timedd.Items.Add(i.ToShortTimeString());
            timedd2.Items.Add(i.ToShortTimeString());
        }
}

protected void saveSchedule_Click(object sender, EventArgs e)
    {

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString);
        
        try
        {
            conn.Open();
            string addquery = "INSERT INTO [Schedule] ([className],[classStart],[classEnd],[classDes]) VALUES (@className, @classStart, @classEnd, @classDes)";
            SqlCommand cmd = new SqlCommand(addquery, conn);
            cmd.Parameters.AddWithValue("@className", className.Text); 
            cmd.Parameters.AddWithValue("@classStart", Convert.ToDateTime(timedd.SelectedItem.Value));
            cmd.Parameters.AddWithValue("@classEnd", Convert.ToDateTime(timedd2.SelectedItem.Value));
            cmd.Parameters.AddWithValue("@classDes", classdes.Text);
            
            int rs = cmd.ExecuteNonQuery();
            if (rs == 1)
            {
                result1.Visible = true;
                result1.Text = "Record Has Been Added Successfully !";
                Response.AddHeader("REFRESH", "5; URL = Schedule.aspx");

            }
            else
            {
                result1.Visible = true;
                result1.Text = "Please try again...";
            }

        }
        catch (Exception ex)
        {
            Response.Write("Error: " + ex.ToString());
        }

解决方案

Start by using the debugger to look at exactly what you are passing to which field.
Identify which is the date / time value and look at the exact format you are passing it in - If it's a genuine date or time then you're fine.
Convert it to a DateTime value using TryParse (or TryParseExact) and pass that converted value to SQL instead of the "raw" value you are reading from your drop down.
Chances are that the values in your drop down are strings, and they don't have a format that is the same as dates are configured on your SQL Server machine, so it throws an exception because it can't convert it correctly. By passing DateTime values, you eliminate that problem.

First convert the value in proper format

// Place a breakpoint here and see what dateFormat looks like

var classstartFormat = Convert.ToString(timedd.SelectedItem.Value);

// Using the format that you see, use DateTime.ParseExact() to properly read this value into a DateTime object
DateTime classstart= DateTime.ParseExact(classstartFormat,"yyyy-MM-dd", null);

// Set the parameter to the value
cmd.Parameters.AddWithValue("@classStart", classstart);

这篇关于从字符串转换日期和/或时间时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！