Hello Everybody,
Can somebody please give me a clue as to why this error would be happening?

Microsoft OLE DB Provider for SQL Server error '80040e07'
Syntax error converting datetime from character string.
/sites/escapesvacations/admin/view_employ_listings.asp, line 78

line 78 is my Set objSelect = .Execute line
I have written out out my strPreview and strOrder they are correct. Now what I really cannot
figure out is that I do not get this error message if I choose to sort by Pos_ID, DatePosted or Display.

Set objCmd = Server.CreateObject("ADODB.Command")
    With objCmd
        .ActiveConnection = strConnect
        .CommandText = "sp_SelectPositions"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@strLocation", adVarChar, adParamInput, 50, strLocation)
        .Parameters.Append .CreateParameter("@strOrder", adInteger, adParamInput, , strOrder)
'line 78 Set objSelect = .Execute
    End With

CREATE PROCEDURE sp_SelectPositions
(
@strLocation varchar(50),
@strOrder int
)
AS
IF @strLocation = 'All'
BEGIN
SELECT Pos_ID, Title, Location, DatePosted, Display
FROM Employment
END
ELSE
BEGIN
SELECT Pos_ID, Title, Location, DatePosted, Display
FROM Employment
WHERE Location = @strLocation
ORDER BY
CASE
    WHEN @strOrder = 1 THEN Pos_ID
    WHEN @strOrder = 2 THEN Title
    WHEN @strOrder = 3 THEN SalaryRange
    WHEN @strOrder = 4 THEN Location
    WHEN @strOrder = 5 THEN DatePosted
    WHEN @strOrder = 6 THEN Display
END
END

Thanks in advanced
Mike

__________________
Peace
Mike
http://www.eclecticpixel.com

Hi

You would typicly get this error when the Date/Time format in the Where clause or insert etc. cannot be converted by SQL server to match the data/Time format on the field in the DB. This could become tricky especially if you are using the client side date/time to pass to the server. The format might vary across machines which might explain why this could be intermittend depending on the client setting. How do you currently get the date and build the Date parameter before you pass it to the server?

Regards
Marnus

When the informtion is first inserted it is by Date() in the ASP script. I could switch that over to the GetDate() in the stored procedure when inserting into the DB. I will go ahead and switch that and see if I get the same error.

For future reference, when creating a case with different Order By then cannot be different datatypes, this article showed how to resolved this problem
http://www.aspfaq.com/show.asp?id=2501