Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 29th, 2004, 03:18 PM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sql Satement help??


I want to choose all articles from a two week span. From today's date back 2 weeks. I tried to use this script but it's not working. I thought something like this would do it but I am not sure.


SQLStr="SELECT * FROM ABNNews WHERE (date = " & (date-14) & ") ORDER by Date DESC"

Thanks for your help

__________________
-----------------------------------------------------------
\"Don\'t follow someone who\'s not going anywhere\" John Mason
 
Old March 29th, 2004, 04:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 344
Thanks: 0
Thanked 1 Time in 1 Post
Default

You may need to convert the Date-14 part into an actual date, try printing out the SQL just before you run it and then see if it will run in SQL Plus / Query Anaylser.
 
Old March 29th, 2004, 04:19 PM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't have Query Anaylser, I don't have access to the DB. All I can do is use asp to manipulate what's there, Add/delete/modify. I am using the above script on another table and it's works, but I use it as (-1) to select the day before today. So I am not sure what you are asking me to do.

 
Old March 29th, 2004, 09:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

try
SQLStr="SELECT * FROM ABNNews WHERE (date >= " & dateadd("d",-14,date) & ") ORDER by Date DESC"

However, I recommend not using 'date' as a field name in the database, try ArticleDate or some thing more descriptive and less likely to be mistaken for a keyword.

======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
 
Old March 29th, 2004, 09:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Sorry, changed that to dateadd("d",-14,date)

======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
 
Old March 30th, 2004, 09:48 AM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your help, too bad I cannot change the table names, I don't have access, unless you can do it with asp. But the above staement is returning all the records in the table. The date coloumn stores the date 03/26/2004. If the helps, maybe it thinks it's 14 years not days. Just a thought.

Thanks
 
Old March 30th, 2004, 10:32 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

It will return all records because you tell it to return all records ;)

Take a look at this:

WHERE Date >= Date -14

Because your column is called Date, the Access driver doesn't see it as a column name, but as a function name. It's about the same as this:

WHERE i >= i - 14

i will always be larger than i - 14, so Date will also be larger than Date - 14

IMO, you should change the name of the Date column in the database. If you can't do it, tell the one who created the database to do it for you.

Alternatively, you may try to wrap the column name in angled brackets:

WHERE ([date] >= #" & dateadd("d",-14,date) & "#)

HtH,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 30th, 2004, 10:39 AM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks rodmcleay and Imar, I think your right, I will have to try and change the column
name in the DB. Nothing I do seems to work. Thanks for your help and I will see what I can do

 
Old March 30th, 2004, 11:02 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hmmm, looks like I am talking crap. Just ran a quick test:

"SELECT ID, date FROM Test WHERE (date >= #" & dateadd("d", -14, date) & "#)"

This query results in:

SELECT ID, date FROM Test WHERE (date >= #3/16/2004#)

And when I send that to the database, it seems to work correctly. That is, it skips a record with a date like 3/11/2004.

What Access version is your database? Any idea what ADO version you are using?
Also, did you add the # characters to delimit the date?

(Even though it may work, you should follow Rod's advice: using reserved words for column names is not a wise thing to do).

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 30th, 2004, 11:11 AM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am using an sql DB and when I use the # characters I get an error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '#'.

/news/default.asp, line 21

That is the error, maybe that can help with the info you requsted.

Thanks






Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
creating ssis packagte for sql server to sql serer Laxmikant_it ASP.NET 3.5 Professionals 0 November 26th, 2008 12:23 AM
Converting from MS SQL 2005 to Sql Epress edition saif44 SQL Language 0 February 16th, 2007 04:17 PM
Failed to copy objects from SQL server to SQL Serv monfu SQL Server 2000 4 December 4th, 2005 05:54 PM
Move SQL DB from one sql to another sql server Israr SQL Server 2000 3 January 24th, 2005 02:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.