Enter datetime access database

Question

I started working with log4net today. It started really well and I got my first text-based logfiles for my application. Then I took the next step and tried logging into the Accessdatabase log_db.accdb in the table t_log_dat. This turned out goot at first and i was able to log data of the type string and int32. And the the problems started when i tried to include the timestamp as Datetime. here is my ground-settings for the appender and what i have tried so far for the @log_date parameter

<appender name="AdoNetAppender_Access" type="log4net.Appender.AdoNetAppender">
  <connectionString value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=log_db.accdb" />
  <commandText value="INSERT INTO t_log_dat ([dt_timestanp],[str_message],[int_thread]) VALUES (@log_date,@message, @thread)" />

First Try: As described in the Documentation for MS-Access. https://logging.apache.org/log4net/release/config-examples.html

<parameter>
    <parameterName value="@log_date" />
    <dbType value="String" />
    <size value="255" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date" />
    </layout>
</parameter>

Second Try: Manipulate String, so it follows the specification for insert into ms access. Something like this '2015-08-09 09:23:00'

<parameter>
    <parameterName value="@log_date"/>
    <dbType value="String"/>
    <size value="255" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="'%date{yyyy'-'MM'-'dd HH':'mm':'ss}'" />
    </layout>
</parameter>

My Last Try was to use the basic log4net RawTimestampLayout

<parameter>
    <parameterName value="@log_date" />
    <dbType value="DateTime" />
    <layout type="log4net.Layout.RawTimeStampLayout" />
</parameter>

However, none of the Above worked for me. I always get the following Error when i build my application

log4net:ERROR [AdoNetAppender] ErrorCode: GenericFailure. Exception while writing to database System.Data.OleDb.OleDbException (0x80040E07): Datentypenkonflikt in Kriterienausdruck. bei System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

EDIT - Error Translates to

Data Type Mismatch

Does anyone know how I can enter a Timestamp in a access Database using log4net?

P.S.: This Question is different from Configuring log4net to write on a database, since I am specifically trying to figure out how to write a datetime-value to an access-database using log4net. The Thread mentioned above just asks for general help writing to a database using log4net. Also the provided answer just shows how to activate tracing for log4net, which i have already included in this question.

Possible duplicate of [Configuring log4net to write on a database](http://stackoverflow.com/questions/28024282/configuring-log4net-to-write-on-a-database) — MethodMan, Sep 05 '16 at 00:43
You're going to need a value that matches the data type of the `[dt_timestanp]` column — stuartd, Sep 06 '16 at 15:30
Yes, dt_timestanp is a DateTime value. in my third try i was hoping to get it in. But still the same error. — jan-seins, Sep 06 '16 at 17:13

score 0 · Accepted Answer · edited May 23 '17 at 11:46

I got the Source from https://github.com/apache/log4net and i don't think it is possible to insert a DateTime into access atm. The Problem is the function prepare in AdoNetAppender.cs:

        virtual public void Prepare(IDbCommand command)
    {
        // Create a new parameter
        IDbDataParameter param = command.CreateParameter();

        // Set the parameter properties
        param.ParameterName = ParameterName;

        if (!m_inferType)
        {
            param.DbType = DbType;
        }
        if (Precision != 0)
        {
            param.Precision = Precision;
        }
        if (Scale != 0)
        {
            param.Scale = Scale;
        }
        if (Size != 0)
        {
            param.Size = Size;
        }

        // Add the parameter to the collection of params
        command.Parameters.Add(param);
    }

When param.DbType = DbType; sets DateTime for the OleDdParameter it also automatically sets DBTimeStamp as OleDbType. That is a well known error. You can see e.g. here: Trying to insert DateTime.Now into Date/Time field gives "Data type mismatch" error So unless there is a change there it is probably not possible to insert a DateTime-value into ms-access using log4net.

Enter datetime access database

1 Answers1