none
Problem inserting date to SQL Server RRS feed

  • Question

  • I have a DateTimePicker whose format is dd-MM-yyyy. While attempting to insert this value to SQL Server table column, following error is displayed:

    ------ERROR-------------------------
    The conversion of a char data type to a datetime data type
    resulted in an out-of-range datetime value.
    ------------------------------------

    I tried following codes:

    (1) Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate values ('" + dateTimePicker1.Value + "')");

    (2) Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate values ('" + dateTimePicker1.Value.ToString() + "')");

    (3) Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate values ('" + dateTimePicker1.Text + "')");
    Friday, August 17, 2007 5:56 PM

All replies

  • There are a number of issues that crop up when you try to turn a date into a string.

     

    The solution is not to. Instead of adding the string value to your Sql you instead use this Sql:

     

    Code Snippet
    "Insert into TestDate values (@MyDateTime)"

     

     

    You then need to add a parameter for "MyDateTime" and give that the value of the date. This way ADO.Net handles all the date stuff for you, not to mention loads of other problems that can crop up when you do it your way.

     

    I'm not familiar with this ModRes you're using so unfortunately I can't tell you how to add parameters. Basically what needs to happens is that parameters are added to the SqlCommand object, but I don't know how this is exposed in ModRes.

     

    Sean

     

    Friday, August 17, 2007 7:00 PM
  • Sean,

    ModRes is an instance to a Class called ModReUsable which contains following function:

    =====================================================================
     public Int32 InsertNewRecord(string myQuery)
            {
                objModCon.OpenConnection();
                SqlCommand cmdInsert = new SqlCommand(myQuery, objModCon.myCN);
                try
                {
                   Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
                    return RecordsAffected;
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex);
                    return 0;
                }
                finally
                {
                    cmdInsert.Dispose();
                    objModCon.CloseConnection();
                }     

            }
    ======================================================================
    Friday, August 17, 2007 7:07 PM
  • 1) you are best to use parameterized queries as it can resolve some issues as well as it being safer/securer than having a concatinated query (can have SQL injection attacks by user). Even better would be to use Stored Procedures

    2) It could also be the date cultural settings between your Culture/Regional settings and SQL Server - be sure that they are both the same and format the date/time value correctly using say, the DateTime class.

     

    Friday, August 17, 2007 7:39 PM
  • Ok I see how that works.

     

    I'm afraid that function isn't quite sophisticated enough yet. You could try something like this:

     

    Code Snippet

    public void Insert()

    {

    List<SqlParameter> parameters = new List<SqlParameter>();

    parameters.Add(new SqlParameter("MyDateTime", dateTimePicker1.Value));

    Int32 myRes = ModRes.InsertNewRecord("Insert into TestDate values (@MyDateTime)", parameters);

    }

     

    }

    public class ModRes

    {

    public static Int32 InsertNewRecord(string myQuery, List<SqlParameter> parameters)

    {

    objModCon.OpenConnection();

    SqlCommand cmdInsert = new SqlCommand(myQuery, objModCon.myCN);

    cmdInsert.Parameters.AddRange(parameters.ToArray());

    try

    {

    Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();

    return RecordsAffected;

    }

    catch (SqlException ex)

    {

    Console.WriteLine(ex);

    return 0;

    }

    finally

    {

    cmdInsert.Dispose();

    objModCon.CloseConnection();

    }

    }

    }

     

     

     

    I haven't tested it but I reckon that should work. By the way I've made InsertNewRecord a static method. Doesn't really matter, I just like to make stateless methods static 

     

    Sean

    Friday, August 17, 2007 9:17 PM
  • your SQL statement is incorrect, you forgot to tell it which field to insert the data into:

     

    INSERT INTO [TestDate] (ColumnNameHere) VALUES (@MyDateTime)

     

     

    in addition the parameter name is invalid - it should be @MyDateTime (with the @ symbol)

     

     

     

    Friday, August 17, 2007 9:30 PM
  • It still works if you don't specify the column names, provided values() contains values for all of the columns in the correct order and there's no identity column (and timestamps would probably stop it working too, I haven't checked). Rohit's original Sql did the same and I didn't think it worth complicating matters. But yes, it's certainly better to specify the column names.

     

    Regarding the "@" it must be on the parameter name in the Sql but when you add a parameter to the command object it works both with and without the "@". Personally I don't add it, but that's just personal preference.

     

    Sean

    Friday, August 17, 2007 11:55 PM
  •  

    still better keeping it consistant and making sure the "guidelines" are followed otherwise it can come to a point where it works in one place, but not somewhere else.
    Saturday, August 18, 2007 12:45 AM