Click here to Skip to main content
15,890,512 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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();
        }
    }
}


Regards

Snake
Posted
Updated 22-Jun-11 10:40am
v4
Comments
[no name] 22-Jun-11 16:37pm    
This is a volunteer site, people will answer on their time, not yours. Saying I need help ASAP is rude
snake1 22-Jun-11 16:39pm    
oh, sorry for that

1 solution

Try replacing the parameter assignment to specify the format:
C#
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:
C#
InsertCMD.CommandText = "insert into Test(TestDateTime) values ( CONVERT([datetime], @GDDate, 121) )";


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

[EDIT] Removed delimiters from parameter declaration in command text. [/EDIT]
 
Share this answer
 
v3
Comments
snake1 22-Jun-11 19:24pm    
thanks for your answer , but it doesn't work for me
JOAT-MON 22-Jun-11 19:58pm    
do you always get the same error?
snake1 22-Jun-11 20:39pm    
yeah, it's the same error
snake1 22-Jun-11 21:10pm    
thanks very much for your answer it works now but after changing little thing

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

'@GDDate' should be @GDDate
JOAT-MON 22-Jun-11 21:18pm    
Awesome, glad you got it working! Makes sense that the parameter would fill in its own delimiters. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900