What delimeter should be used for SQL Server in the Recordset Dialog box?

Pg 438 mentions that for MSAccess we could use EndDate >=#Today# as the criterion. I have tried single quotes but still get an error that a Type mismatch on 'GetDate'

Hi there,

If you pass a date from an ASP page to the database, you need indeed single quotes for SQL Server.

However, since you're using the GetDate() function, you don't need a delimiter at all.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks for the quick response, but my error persists!
In reviewing the error message thrown by the browser it
points to a line which reads as follows:

If (GetDate() <> "") Then ' Line 13
  rsEvents__Today = GetDate() ' line 14
End If ' line 15

I got the code to work by commenting out
lines 13 and 15 and changed the function
name in line 14 to Date.
'If (GetDate() <> "") Then 'Line 13
  rsEvents__Today = Date() ' Line 14
'End If ' Line 15

However this seems to be a temporary fix!. There
must be a cleaner way to do this

Correct. GetDate() is a SQL Server function, so you can't use it in ASP. Confusingly, both ASP as Access use Date() to get today's date. However, there is a big difference in where Date() executes.

For example:

Dim someVar
someVar = Date()

defines a variable and assigns it the value of today's date in ASP.

Dim mySqlStatement
mySqlStatement = "SELECT SomeField FROM SomeTable WHERE SomeField > Date()"
' Execute the SQL statement here

In this case, the Date() is passed to Access and so it runs within Access and not in ASP.

The same applies to SQL Server. So you need to use Date() when you're using ASP and GetDate() when you need the date inside a SQL statement.

Can you post the original code you're trying to change? I don't have the book here right now...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

The code is generated automatically when one follows the steps on Page 438:

Try It Out: Your Second Date with the WHERE Clause

1)Open the recordset on events.asp and remove the WHERE clause that filters the EndDate from the SQL Text box in t....
2)Click the plus button on the variables list. Type Today as the name of the variable, 1/2/2100 for the default value, and date as the Run-time value. Expand Tables in the Database items window and then expand the table Events. Click the column EndDate and then click the WHERE button. This adds EndDate to the WHERE clause of the SQL statement

Next type >=#Today# as the criterion for EndDate in the SQL box...

I am using SQL Server as my back end.

The code snippet on page 437 uses Dat() directly in the SQL statement. That means it's send to Access where it is executed. If you want to reuse that example, replace Date() with GetDate().
You should realize that GetDate() also returns the time part, so you may not always get what you expect.

The Try it Out on page 438 uses Date() again; however, this time the code runs in ASP, so there is no need to change anything:

If Date() <> "" Then 'Line 13
  rsEvents__Today = Date() ' Line 14
End If ' Line 15

It's a bit odd to see how Date() is compared with "" but that's how DW sets up variables. In this code, Date() is set in ASP, and then its text representation is sent to the query,
Page 440 describes this in detail.

Note that you should use a single quote (') instead of the hash symbol (#) for a date in SQL server.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.