Hi guys,

I am using VS 2005 with SQL 2005 and latest version of LLBLGen.

I am trying to return the minutes that a request has taken so it is a DateDiff between the datetime requested and the current date of course. I am using a dynamic list to get the data. Following line:

fields[7].ExpressionToApply = new DbFunctionCall("DATEDIFF", new object[] { "mi", RequestFields.DateTimeRequested, new DbFunctionCall("GETDATE", null) });

throws an error saying:

System.Data.SqlClient.SqlException: Invalid parameter 1 specified for datediff.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near 'preLink'.

When i comment out the expression the query runs with out any errors.

THis is the query that llblgen generates with the expression:

exec sp_executesql N'SELECT [preLink].[dbo].[Request].[RequestId], [preLink].[dbo].[Request].[RequestNumber], [preLink].[dbo].[Person].[LastName], 
[preLink].[dbo].[Person].[Initials], [preLink].[dbo].[Person].[FirstName], [preLink].[dbo].[Person].[IDNumber] AS [Idnumber], [preLink].[dbo].[Request].[DateTimeRequested], 
[preLink].[dbo].[Site].[Name] AS [SiteName], DATEDIFF(@LOcdf1b7c31, [preLink].[dbo].[Request].[DateTimeRequested], GETDATE()) AS [TurnAroundTime], (SELECT TOP 1 
[preLink].[dbo].[Station].[Name] FROM ( [preLink].[dbo].[Station]  INNER JOIN [preLink].[dbo].[Site_Station]  ON  
[preLink].[dbo].[Station].[StationId]=[preLink].[dbo].[Site_Station].[StationId]) WHERE ( [preLink].[dbo].[Site_Station].[SiteId] = [preLink].[dbo].[Site].[SiteId])) AS 
[PatientLocation] FROM (((( [preLink].[dbo].[Request]  INNER JOIN [preLink].[dbo].[RequestMonitor]  ON  
[preLink].[dbo].[Request].[RequestId]=[preLink].[dbo].[RequestMonitor].[RequestId]) INNER JOIN [preLink].[dbo].[Patient]  ON  
[preLink].[dbo].[Patient].[PatientId]=[preLink].[dbo].[Request].[PatientId]) INNER JOIN [preLink].[dbo].[Person]  ON  
[preLink].[dbo].[Person].[PersonId]=[preLink].[dbo].[Patient].[PatientId]) INNER JOIN [preLink].[dbo].[Site]  ON  
[preLink].[dbo].[Site].[SiteId]=[preLink].[dbo].[Request].[PerformSiteId]) WHERE ( ( [preLink].[dbo].[RequestMonitor].[InstrumentId] IN (@InstrumentId2, @InstrumentId3, 
@InstrumentId4))) ORDER BY [preLink].[dbo].[Request].[DateTimeRequested] DESC',N'@LOcdf1b7c31 nvarchar(2),@InstrumentId2 uniqueidentifier,@InstrumentId3 
uniqueidentifier,@InstrumentId4 
uniqueidentifier',@LOcdf1b7c31=N'mi',@InstrumentId2='1DE160B0-DD6F-DB11-BA37-00188B18D3A4', @InstrumentId3='74355BB3-D76F-DB11-BA37-00188B18D3A4', @InstrumentId4='D6064267-D76F-DB11-BA37-00188B18D3A4'

Thanks very much, Russell

Hello,

it seems there is no error in the syntax for the dbfunctioncall method. If you try to execute it with the query analyser replacing parameters, did you have the same error?

Hi,

It works perfectly when the values are replaced using Query analyzer. I guess that you can't use DateDiff like that as it does not accept a string as it's first parameter.

It is trying to insert "mi" where it should be mi. I don't think there is a way of doing this. I'll just have to calculate the date difference in code.

Thanks for the great support.

This isn't supported yet, we currently only support functions which accept parameters, not functions which accept hardcoded values, like datediff.

This is scheduled for v2.1.

You may implement IExpression on a class with code copied from the sourcecode of DbFunctionCall, to return the needed query text.