none
Using Paramters with Jet Oledb RRS feed

  • Question

  • I am having problems using DateTime parameters in the Jet Oledb I have the following code

    I would expect the first row to throw the exception but it doesn't. Surely I should get a date back and not a string???

    Any help would be greatly appreciated.

    Colin

    OleDbConnection
    connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Test.accdb;Persist Security Info=True");

     

    OleDbCommand command = new OleDbCommand("SELECT ID, @DateParam FROM Test", connection);
    OleDbParameter param = new OleDbParameter("@DateParam", OleDbType.DBTimeStamp);

    param.Value =

    DateTime.Now;

     

    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
    DataTable table = new DataTable(); 

    command.Parameters.Add(param);
    adapter.Fill(table);

     

    Console.WriteLine(table.Rows[0].Field<string>(1)); // THIS LINE IS FINE
    Console.WriteLine(table.Rows[0].Field<DateTime>(1)); // THROWS InvalidCastException

    Tuesday, November 17, 2009 10:37 PM

Answers

All replies

  • Hi Colin,

     

    Welcome to ADO.NET Data Providers forum!

     

    I can reproduce this problem at my lab.  It seems that the ACE provider incorrectly transfers the datetime typed OldDbParameter.  The problem seems only occur on the datatime typed values.  However, if we retrieve the date/time column value from the Access 2007 files, the provider can transfer the type correctly and we get CLR DateTime type at the client side.  

     

    Currently, the workaround would be to convert the string value to DateTime:

    ========================================================================================
    var s = table.Rows[0].Field<string>(1);

    DateTime d = DateTime.Parse(s);
    ========================================================================================

     

    For the root cause for this issue, I will do a further investigation and consult the product team as well.  If I have any updated messages, I will keep you informed as soon as I can.   Thanks for your patient! 

     

    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.
    Wednesday, November 18, 2009 4:13 AM
    Moderator
  • Thanks for the quick reply. Good luck finding the source of the issue.

    Regards,

    Colin
    Wednesday, November 18, 2009 8:43 PM
  • A few further issues after doing some further digging and matching what I believed to be the correct OLEDB types I found that the problem also seems to extend to the byte, bool and decimal types as the code below demonstrates

    OleDbConnection

     

    connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.....;Persist Security Info=True");

     

    OleDbCommand command = new OleDbCommand(

     

    "SELECT ID, @BoolParam, @ShortParam, @IntParam, @FloatParam, @DecimalParam, @ByteParam, @DoubleParam FROM Test", connection);

     

     

    OleDbParameter param1 = new OleDbParameter("@BoolParam", OleDbType.Boolean);

    param1.Value =

    true;

     

    OleDbParameter param2 = new OleDbParameter("@ShortParam", OleDbType.SmallInt);

    param2.Value = 2;

     

    OleDbParameter param3 = new OleDbParameter("@IntParam", OleDbType.Integer);

    param3.Value = 14;

     

    OleDbParameter param4 = new OleDbParameter("@FloatParam", OleDbType.Single);

    param4.Value = 14.4F;

     

    OleDbParameter param5 = new OleDbParameter("@DecimalParam", OleDbType.Numeric);

    param5.Value = 100.1M;

     

    OleDbParameter param6 = new OleDbParameter("@ByteParam", OleDbType.UnsignedTinyInt);

    param6.Value = 34;

     

    OleDbParameter param7 = new OleDbParameter("@DoubleParam", OleDbType.Double);

    param7.Value = 234.23D;

     

    OleDbDataAdapter adapter = new OleDbDataAdapter(command);

     

    DataTable table = new DataTable();

     

    command.Parameters.Add(param1);

    command.Parameters.Add(param2);

    command.Parameters.Add(param3);

    command.Parameters.Add(param4);

    command.Parameters.Add(param5);

    command.Parameters.Add(param6);

    command.Parameters.Add(param7);

    adapter.Fill(table);

     

    Console.WriteLine(table.Rows[0].Field<bool>(1)); // Exception

     

    Console.WriteLine(table.Rows[0].Field<short>(2)); // OK

     

    Console.WriteLine(table.Rows[0].Field<int>(3)); // OK

     

    Console.WriteLine(table.Rows[0].Field<float>(4)); // OK

     

    Console.WriteLine(table.Rows[0].Field<decimal>(5)); // Exception

     

    Console.WriteLine(table.Rows[0].Field<byte>(6)); // Exception

     

    Console.WriteLine(table.Rows[0].Field<double>(7)); // OK

    Wednesday, November 18, 2009 9:31 PM
  • Hi Colin,

     

    This KB article is recommended by the product team, http://support.microsoft.com/default.aspx/kb/320435.   One correction is that the Access Hyperlink field is really an Access Memo data type, NOT a Text data type (a Text field is limited to only 255 characters, which would not be very useful for hyperlinks).  

     

    This may not explain the exceptions, but we need to make sure the database data types are match up properly. 

     

    For the cause of the exceptions, I will keep you informed if I have any updated messages. 

     

    Have a great 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.
    Monday, November 23, 2009 1:26 AM
    Moderator
  • These are exactly the parameters types I have and still the exceptions occur.

    Thanks

    Colin
    Wednesday, November 25, 2009 8:59 PM
  • Hi Colin,

     

    How about using the OleDbParameter to insert data into the Access data table instead of retrieving the OldDbParameter back?  Do you receive the exception? 

     

    Have a great 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, November 26, 2009 9:02 AM
    Moderator
  • Inserting data is fine it's just the querying that gives the exceptions.

    Regards

    Colin
    Wednesday, December 2, 2009 7:39 PM
  • Hi Colin,

    I think it is a potential issue of this provider.  I will create a new bug ticket on Microsoft Connect and keep you informed of any updated information.

    BTW, any huge impact of this problem at your side?  With the detailed information, I can raise the priority of this issue.  Thanks!

    Have a nice weekend! 
     
    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.
    Friday, December 4, 2009 11:26 AM
    Moderator
  • Hi Colin,

     


    Could you please tell me how the impact of this issue at your side?   Thank you very much!

    Have a nice weekend! 
     
    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.
    Monday, December 21, 2009 7:03 AM
    Moderator
  • The impact isn't huge and can be worked around. It's just that the solution isn't ideal and it does seem like a hack to get round something that should work.

    Regards,

    Colin
    Monday, December 21, 2009 9:00 PM
  •  Hi Colin,

     

    Thank you very much!  I have open a ticket on Microsoft Connect, https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=521988.   If I receive any new information from the product team, I will let you know as soon as possible.  

     

    Merry Christmas & Happy New Year!

     

    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, December 24, 2009 5:39 AM
    Moderator