DatDiif in ASP

[meltingpot]

Got the following query, works great in MS Access, put it into my ASP page and returns ..

Too many paremeters Expected 1'

Checked all the spelling and fields - all ok, It works in MS Acess as a query

Query ....

SELECT DateDiff("d",[BoatPosition.PositionDate],[BoatPosition.EnterDate]) AS LastDate
FROM Boats INNER JOIN BoatPosition ON Boats.HMSTC = BoatPosition.BoatName

Help !!

 

[Sheco]

As a workaround maybe just fetch the value of both date columns and then "do the math" in ASP logic.

 

[meltingpot]

Okkkaaayyy .. being a beginer, thats a bit of a mountain, could you point me in the right direction

 

[Chopstik]

Actually, it's probably not as much of a mountain as you may think. What Sheco is suggesting is that you pull your date values directly from the database and then do your DATEDIFF in ASP instead of your query directly as a workaround. That should get you started...

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[meltingpot]

SO ...

<% SELECT DateDiff("d",[BoatPosition.PositionDate],[BoatPosition.EnterDate]) AS LastDate
FROM Boats INNER JOIN BoatPosition ON Boats.HMSTC = BoatPosition.BoatName
%>

?

 

[Sheco]

I was thinking of a query somethng more along the lines of:

SELECT 
  B.HMSTC 
  ,P.PositionDate
  ,P.EnterDate
FROM Boats B
  INNER JOIN BoatPosition P
     ON B.HMSTC = P.BoatName

So lets just suppose you have an ADO recordset object that was populated with the results of the above query... you could iterate through the recordset and compare the number of days for each row with something like this:

Dim strBoat, iElapsed
Do While Not rsBoats.EoF
  strBoat = rsBoats("HMSTC") & ""
  iElapsed = DateDiff("d", rsBoats("PositionDate"), rsBoats("EnterDate"))

  Response.Write "Name = " & strBoat & "   "
  Response.Write "Days = " & iElapsed 
  Response.Write vbCrLf & "<br>" & vbCrLf
  rsBoats.MoveNext
Loop

Instead of writing everything out to the response, you could choose to only do dome action when the value of [tt]iElapsed > 3[/tt] or really anything you imagine.

 

[meltingpot]

Thanks Sheco - thats really helpful - ill carry on working/understandng. ...

You guys online really make a difference, this has helped so much. ill get when its working.

MANY THANKS

 

[meltingpot]

Hi Sheco .. works great, made a couple of changes thus ..

<%Dim strBoat, iElapsed
Do While Not rsBoats.EoF
strBoat = rsBoats("HMSTC") & ""
iElapsed= DateDiff("h", rsBoats("PositionDate"), now())

Response.Write "HMSTC- "& strBoat & " &nbsp; "
Response.Write "Hourse Since Late Position- " & iElapsed
Response.Write vbCrLf & "<br>" & vbCrLf
rsBoats.MoveNext
Loop
%>

Is there a way of showing a graphic (JPG) if 'PositionDate'(Hourse Since Late Position) is more than 24 hours 1 minute old and one graphic if its less than ..

Many thanks for your great help !!!!!

 

[Sheco]

It sounds like you want more resolution that just hours.

Perhaps if your [tt]iElapsed[/tt] were calculated in minutes instead of hours?

24 hr/day * 60 min/hr = 1440 min/day


Then you can use the \ operator to convert back into hours and discard the remaining minutes.

<%
Dim strBoat, iElapsed
Do While Not rsBoats.EoF
  strBoat = rsBoats("HMSTC") & ""
  iElapsed= DateDiff("d", rsBoats("PositionDate"), now()) 

  Response.Write  "HMSTC- "& strBoat & " &nbsp; "
  Response.Write "Hourse Since Late Position- " & iElapsed [red]\ 24[/red]
  Response.Write vbCrLf & "<br>" & vbCrLf
[red]
  if iElapsed <= 1440 then
    '24 hours or less
    Response.Write "<img src='fresh.jpg'>" & vbCrLf 
  else
    'More than 24 hours
    Response.Write "<img src='stale.jpg'>" & vbCrLf 
  end if
[/red]
  rsBoats.MoveNext
Loop
%>

 

[Sheco]

Whoops!

I should have changed the interval in that DateDiff like this:[tt]
[red]iElapsed= DateDiff("h", rsBoats("PositionDate"), now()) [/red]

[/tt]

 

[meltingpot]

Thanks Sheco - Im not sure its working .. Im using the following code, but when i run it all the boats have a check.jpg when infact they are over 24 hours old, the one record that is less than 24 hours still displays the check.jpg ???

<%
Dim strBoat, iElapsed
Do While Not rsBoats.EoF
strBoat = rsBoats("HMSTC") & ""
iElapsed= DateDiff("h", rsBoats("PositionDate"), now())

Response.Write ""& strBoat & " &nbsp; " & vbCrLf
Response.Write ":" & iElapsed \ 24
Response.Write vbCrLf & "<br>" & vbCrLf

if iElapsed <= 1440 then
'24 hours or less
Response.Write "<img src='check.gif'>" & vbCrLf
else
'More than 24 hours
Response.Write "<img src='uncheck.gif'>" & vbCrLf
end if

rsBoats.MoveNext
Loop
%>

 

[Chopstik]

You've left your iElapsed set to hours, not minutes but you're testing for minutes. Try this:

<%
Dim strBoat, iElapsed
Do While Not rsBoats.EoF
  strBoat = rsBoats("HMSTC") & ""
  iElapsed= DateDiff("h", rsBoats("PositionDate"), now()) 

     Response.Write  ""& strBoat & " &nbsp; " & vbCrLf
  Response.Write ":" & iElapsed \ 24
  Response.Write vbCrLf & "<br>" & vbCrLf

  [COLOR=red]iElapsed = iElapsed \ 24 (shouldn't that be [b]/[/b]?)[/color]
  if iElapsed <= 1440 then
    '24 hours or less
    Response.Write "<img src='check.gif'>" & vbCrLf 
  else
    'More than 24 hours
    Response.Write "<img src='uncheck.gif'>" & vbCrLf 
  end if

  rsBoats.MoveNext
Loop
%>

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[meltingpot]

Hi -- using the /24 I get about 12 or 15 decimal places !!!

 

[Chopstik]

Sorry, I got my logic confused. That line should read as

iElapsed = iElapsed * 60

The reason for this is that your original statement is pulling the difference in hours. However, when you test for it being less than 1440, it is in minutes. If you leave it as is, 24 (or 48 or 72 or however many hours) will be less than 1440. Convert your DateDiff results from hours into minutes and you should be fixed.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike