Access SQL query in ASP.net?

• jn-946563

  Newbie

  Points: 1

  More actions

  November 20, 2008 at 12:36 pm

  #196323

  We have a query in access that works very well. SQL syntax as follows:

  --- --- ---

  SELECT dbo_CallLog.CallID AS Ticket, dbo_CallLog.Category, dbo_CallLog.CallDesc, dbo_Asgnmnt.Notes, CDate((dbo_Asgnmnt!DTLastMod/60/60/24)+25569) AS LastMod, dbo_Asgnmnt.Assignee, dbo_Asgnmnt.Resolution

  FROM (dbo_CallLog LEFT JOIN dbo_Detail ON dbo_CallLog.CallID = dbo_Detail.CallID) LEFT JOIN dbo_Asgnmnt ON dbo_CallLog.CallID = dbo_Asgnmnt.CallID

  WHERE (((dbo_Asgnmnt.Assignee)="Joe Nelson") AND ((CDate(([dbo_Asgnmnt]![DTLastMod]/60/60/24)+25569))>Now()-7))

  ORDER BY dbo_CallLog.CallID;

  --- --- ---

  However, when we cut and paste this code as a SQL SELECT statement in an ASP.net web page, it errors out at the CDate parts...

  Any thoughts on how to convert this to a 'real' SQL syntax?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738176

  More actions

  December 1, 2008 at 8:15 am

  #905659

  CDATE isn't a valid T-SQL function, it's ASP.

  How is the ASP syntax structured?

  Usually you'd do something like

  strCMD = "SELECT dbo_CallLog.CallID AS Ticket, dbo_CallLog.Category, dbo_CallLog.CallDesc, dbo_Asgnmnt.Notes, " + CDate((dbo_Asgnmnt!DTLastMod/60/60/24)+25569)+

  " AS LastMod, dbo_Asgnmnt.Assignee, dbo_Asgnmnt.Resolution " + ...

  Or if you submit the variable to SQL, you'd need to do a CAST or CONVERT to reformat.