Problem writing to database

[Alex Fletcher]

Hi,

I'm pretty new to NLog so please forgive my basic question. 

I've inherited a Winforms application written by some contractors, I'm mainly a database developer but I've managed to do some development of the application but I sometimes struggle with tracking down error messages encountered by users, therefore I'm attempting to retrofit logging into the application via NLog, I first encountered the issue in NLog 4.4.5 and I've since updated to 4.4.12 and that hasn't solved the problem. I've verified that NLog is catching the errors correctly as it will output to a text file but when I try to direct it to a database output I can't get it to work. 

My problem is that I can only get errors written to the database only if I don't pass any parameters to the insert statement (which is pretty useless). That is to say that the following in my NLog.config file works:

<target name="database" xsi:type="Database">
     <commandText>INSERT INTO [tblException] (DbVersionID, ExceptionDateTime) SELECT MAX(DbVersionID), GETDATE() FROM tblDbVersion</commandText>

      <dbProvider>System.Data.SqlServerCe.4.0</dbProvider>
     <connectionString>Data Source=${basedir}\Database.sdf</connectionString>
 </target>

But this doesnt':

<target name="database" xsi:type="Database">
 <commandText>INSERT INTO [tblException] (DbVersionID, ExceptionDateTime, Message) SELECT MAX(DbVersionID), GETDATE(), @message FROM tblDbVersion</commandText>

 <parameter name="@message" layout="${message}" />

 <dbProvider>System.Data.SqlServerCe.4.0</dbProvider>
 <connectionString>Data Source=${basedir}\Database.sdf</connectionString>
 </target>

I've enabled internal logging and the following is what I get:

2017-11-28 11:26:45.8063 Trace Executing Text: INSERT INTO [tblException] (DbVersionID, ExceptionDateTime, Message) SELECT MAX(DbVersionID), GETDATE(), @Message FROM tblDbVersion

2017-11-28 11:26:45.8063 Trace   Parameter: '@message' = 'Test Error Message' (String)

2017-11-28 11:26:45.8063 Error Error when writing to database. Exception: System.Data.SqlServerCe.SqlCeException (0x80004005): A parameter is not allowed in this location. Ensure that the '@' sign is in a valid location or that parameters are valid at all in this SQL statement.

   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)

   at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()

   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)

   at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()

   at NLog.Targets.DatabaseTarget.WriteEventToDatabase(LogEventInfo logEvent)

   at NLog.Targets.DatabaseTarget.Write(LogEventInfo logEvent)

It appears that the parameter isn't getting replaced in the query before it's being run. I tried adding single quotes in the command text just in case that would help but it just resulted in the literal string '@message' being inserted into the database field. 

I can't see anything that I've done differently to the examples, so any help would be appreciated.

Regards,

Alex

[Alex Fletcher]

I also posted this to Stack Overflow here:

https://stackoverflow.com/questions/47569636/nlog-parameters-not-passed-to-query-when-writing-to-database/

It seems the solution to my problem was to remove the select statement from my command. SQLCE does not appear to support passing parameters into the select part of the SQL query.