none
Problem Creating DEFAULT constraint in ADO.NET Jet Sql RRS feed

  • Question

  • Look at the following code. It seems that I can insert into a table with a DateTime literal that has a time however I cannot create a DEFAULT constraint with the same type of DATETIME literal. I can create DEFAULT DATETIME constraints that only use DATES but not DATETIMES. Is this expected behaviour?



    OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Projects\\Samples\\OledbTest\\OledbTest\\bin\\Debug\\Test.accdb;Persist Security Info=True");
    
    connection.Open();
    
    OleDbCommand commandCreate = new OleDbCommand("CREATE TABLE Test1 (DateColumn DATETIME)", connection);
    
    commandCreate.ExecuteNonQuery();
    
    OleDbCommand commandAlter = new OleDbCommand("ALTER TABLE Test1 ALTER COLUMN DateColumn DATETIME DEFAULT #01/01/2010#", connection);
    
    commandAlter.ExecuteNonQuery();
    
    OleDbCommand commandInsert = new OleDbCommand("INSERT INTO Test1 (DateColumn) VALUES (#01/01/2010 10:12:12#)", connection);
    
    commandInsert.ExecuteNonQuery();
    
    OleDbCommand commandAlterFail = new OleDbCommand("ALTER TABLE Test1 ALTER COLUMN DateColumn DATETIME DEFAULT #01/01/2010 10:12:12#", connection);
    
    commandAlterFail.ExecuteNonQuery(); // This line throws an exception
    
    
    Monday, February 8, 2010 10:40 PM

Answers

  • Hi Colin,

     

    The problem is the format of the DEFAULT value in the JET SQL command.  Based on my test, the following format is working fine:

    =========================================================================================================

    OleDbCommand commandAlterFail = new OleDbCommand("ALTER TABLE Test1 ALTER COLUMN DateColumn DATETIME DEFAULT #01/01/2010#/#10:12:12#", connection);

    commandAlterFail.ExecuteNonQuery();

    =========================================================================================================

     

    Besides, the format can be different on different platforms (different locales).   We can verify the correct format in Access UI.  The datetime format in your sample codes will be automatically converted to this correct format: “#01/01/2010#/#10:12:12#”.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, February 11, 2010 1:41 PM
    Moderator

All replies

  • Hi Colin,

     

    Thank you very much for reporting this problem.  I can repro this issue at my lab.   I will consult the product team to verify if it is a product problem or designed behavior.   BTW, I have also tested that it work fine on TIME values as well.  But the DATETIME value throws the exception “Syntax error in ALTER TABLE statement”.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, February 9, 2010 1:16 PM
    Moderator
  • Hi Colin,

     

    The problem is the format of the DEFAULT value in the JET SQL command.  Based on my test, the following format is working fine:

    =========================================================================================================

    OleDbCommand commandAlterFail = new OleDbCommand("ALTER TABLE Test1 ALTER COLUMN DateColumn DATETIME DEFAULT #01/01/2010#/#10:12:12#", connection);

    commandAlterFail.ExecuteNonQuery();

    =========================================================================================================

     

    Besides, the format can be different on different platforms (different locales).   We can verify the correct format in Access UI.  The datetime format in your sample codes will be automatically converted to this correct format: “#01/01/2010#/#10:12:12#”.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, February 11, 2010 1:41 PM
    Moderator
  • This works but it is a date time literal format that is both different from any I have seen and different from datetime literals elsewhere. Can you point at where this format is documented as I am unable to find it?
    Thursday, February 11, 2010 9:55 PM
  • Hi Colin,

     

    I found this format from Access IDE.  However, the product team told me that the format should be similar with the VB.NET date type, http://msdn.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx.   Unfortunately, I cannot make it work unless I put the #/# in the middle of the date and time value.   The problem seems only occurs in the setting the DEFAULT value.  The format similar with “#1/1/2010 10:00:00#” is working fine in other statements like INSERT.   I will update this thread as soon as I get any new information.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, February 16, 2010 6:09 AM
    Moderator