CreateODBCDate

[atkearns]

I am in the process of moving a website from one server to another. The old
server was running Coldfusion 6.1, and the new server is running 7.0. There is
an SQL Server database integrated with the site and I am having trounle
connecting to it on the new server. I have the data source all setup in
Coldfusion Administrator. The error I get is on multiple pages:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'schedule'.
(it's not always 'schedule', there are other object names that come up for
other pages)

The error always occurs on a line that has CreateODBCDate in it. Here's an
example of one (the error is on line 18):

11<cfset publicClause= "AND ((schedule.RegOnline)="& public_web &")">
12
13<cfquery datasource="test_for_sql" name="d_upcoming"
cachedwithin=#CreateTimeSpan(0,6,0,0)#>
14 SELECT DISTINCT eventName, event.eventID
15 FROM schedule, event
16 WHERE schedule.eventID= event.eventID AND RegOnline = 1 AND (schedule.date
BETWEEN
17 #CreateODBCDate(startDate)# AND
18 #CreateODBCDate(endDate)#) #publicClause#
19 ORDER BY event.eventName ASC
20</cfquery>

I don't know if this is an issue with the new Coldfusion version, or if it has
something to do with the new server being able to talk to the data source.
Please, any ideas may help.

Thanks,
Andrew

[Dan Bracuk]

Could be your variables.

Dump #CreateODBCDate(startDate)# AND #CreateODBCDate(endDate)#

and see if you get anything. If it works, the problem is something else.

Originally posted by: atkearns
I am in the process of moving a website from one server to another. The old
server was running Coldfusion 6.1, and the new server is running 7.0. There is
an SQL Server database integrated with the site and I am having trounle
connecting to it on the new server. I have the data source all setup in
Coldfusion Administrator. The error I get is on multiple pages:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'schedule'.
(it's not always 'schedule', there are other object names that come up for
other pages)

The error always occurs on a line that has CreateODBCDate in it. Here's an
example of one (the error is on line 18):

11<cfset publicClause= "AND ((schedule.RegOnline)="& public_web &")">
12
13<cfquery datasource="test_for_sql" name="d_upcoming"
cachedwithin=#CreateTimeSpan(0,6,0,0)#>
14 SELECT DISTINCT eventName, event.eventID
15 FROM schedule, event
16 WHERE schedule.eventID= event.eventID AND RegOnline = 1 AND (schedule.date
BETWEEN
17 #CreateODBCDate(startDate)# AND
18 #CreateODBCDate(endDate)#) #publicClause#
19 ORDER BY event.eventName ASC
20</cfquery>

I don't know if this is an issue with the new Coldfusion version, or if it has
something to do with the new server being able to talk to the data source.
Please, any ideas may help.

Thanks,
Andrew

[atkearns]

It still doesn't work.. this time the error reads:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near
'!'.

Here's how I commented out those lines:

WHERE schedule.eventID= event.eventID AND RegOnline = 1 AND (schedule.date
<!--BETWEEN
#CreateODBCDate(startDate)# AND
#CreateODBCDate(endDate)#-->) #publicClause#

[Kronin555]

You used an HTML comment. You have to use a Coldfusion comment.

<!--- coldfusion comment --->
<!-- html comment -->

[atkearns]

Oops sorry.
OK w/ the correct tag it still gives me this error for the same line:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the
keyword 'AND'.

[Kronin555]

Look at your query here:
SELECT DISTINCT eventName, event.eventID
FROM schedule, event
WHERE schedule.eventID= event.eventID AND RegOnline = 1 AND (schedule.date
BETWEEN
#CreateODBCDate(startDate)# AND
#CreateODBCDate(endDate)#) #publicClause#
ORDER BY event.eventName ASC

If you comment out what you listed above, you get:
SELECT DISTINCT eventName, event.eventID
FROM schedule, event
WHERE schedule.eventID= event.eventID AND RegOnline = 1 AND (schedule.date
BETWEEN ) #publicClause#
ORDER BY event.eventName ASC

This isn't a syntactically-correct query. If you're going to comment out the
date stuff, comment it all out. Also, keep in mind that your publicClause
string starts with an AND, so you want to make sure that your query doesn't
result in an "AND AND" condition, as that will throw an error as well...

[paross1]

Why are you starting your comment after schedule.date? Now your query looks
like this:

1<cfset publicClause= "AND ((schedule.RegOnline)="& public_web &")">
12
13<cfquery datasource="test_for_sql" name="d_upcoming"
cachedwithin=#CreateTimeSpan(0,6,0,0)#>
14 SELECT DISTINCT eventName, event.eventID
15 FROM schedule, event
16 WHERE schedule.eventID= event.eventID AND RegOnline = 1 AND (schedule.date)
#publicClause#
19 ORDER BY event.eventName ASC
20</cfquery>

Which is nonesense (the AND (schedule.date) part)

Shouldn't you start your comment before AND (schedule.date) ? <!---AND
(schedule.date)

Phil

[Dan Bracuk]

What you thought you heard me say is different than what I thought I told you
to try. If I may, I'll rephrase my suggestion.

Put this code right before your query. The if the variables display, the
problem is something else.

<cfdump var="start date odbc is #CreateODBCDate(startDate)# and end date odbc
is #CreateODBCDate(endDate)#">

Originally posted by: atkearns
It still doesn't work.. this time the error reads:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near
'!'.

Here's how I commented out those lines:

WHERE schedule.eventID= event.eventID AND RegOnline = 1 AND (schedule.date
<!--BETWEEN
#CreateODBCDate(startDate)# AND
#CreateODBCDate(endDate)#-->) #publicClause#