在将datetime插入Sql数据库时遇到问题 [英] getting an issue while inserting datetime into Sql Database

问题描述

我试图通过C#执行插入语句，但我不知道为什么我得到Sql Exception(从字符串转换日期和/或时间时转换失败.)

代码:-

i was trying to execute insert statement by C# , and i dunno why am i getting that Sql Exception(Conversion failed when converting date and/or time from character string.)

Code:-

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Globalization;
using System.Data.SqlClient;

namespace TestProject
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Test()
        {
            CultureInfo SpecialCulture = new CultureInfo("nb-NO");
            string GDformat = "dd-MM-yyyy HH:mm:ss.fff";
            string GDstr = DateTime.Now.ToString(GDformat, SpecialCulture);
            DateTime GDDate = DateTime.ParseExact(GDstr, GDformat, SpecialCulture, DateTimeStyles.AllowWhiteSpaces);
            SqlConnection InsertConn = new SqlConnection("Data Source=SOLID\\PUREEYEDB1;Initial Catalog=DB1_PureEyez;Integrated Security=True");
            SqlCommand InsertCMD = new SqlCommand();
            InsertCMD.Connection = InsertConn;
            InsertCMD.CommandType = CommandType.Text;
            InsertCMD.CommandText = "insert into Test(TestDateTime)" + "values (''" + "@GDDate" + "'') ";
            InsertCMD.Parameters.Add("@GDDate", SqlDbType.DateTime).Value = GDDate;
            InsertConn.Open();
            if (InsertCMD.ExecuteNonQuery() != 0)
            {
                MessageBox.Show("Succeded");
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Test();
        }
    }
}

问候

Snake

Regards

Snake

推荐答案

尝试替换参数分配以指定格式:

Try replacing the parameter assignment to specify the format:

InsertCMD.Parameters.Add ( "@GDDate", SqlDbType.DateTime ).Value = GDDate.ToString ( "yyyy-MM-dd HH:mm:ss.fff" );

您可能还需要更改命令以指定输入格式:

You might also need to change command to specify the input format:

InsertCMD.CommandText = "insert into Test(TestDateTime) values ( CONVERT([datetime], @GDDate, 121) )";

另外，设置参数后尝试调用InsertCMD.Prepare().

从命令文本的参数声明中删除了定界符. [/EDIT]

Also, try calling InsertCMD.Prepare() after setting the parameter.

Removed delimiters from parameter declaration in command text. [/EDIT]

这篇关于在将datetime插入Sql数据库时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！