ASP Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsProgramming Languages - MoreASP Programming

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old November 12th, 2003, 02:32 PM
davidDuke's Avatar
davidDuke davidDuke is offline
<? echo "Hello World!"; ?>
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: 30pixles by 30pixels
Posts: 125 davidDuke User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 17 sec
Reputation Power: 5
Send a message via AIM to davidDuke Send a message via MSN to davidDuke
Post Access DB Query Prob!

I have this code:

Code:
<%
'Dimension variables
'Holds the Database Connection Object
Dim adoCon
'Holds the recordset for the records in the database
Dim rsEvents
'Holds the SQL query to query the database
Dim strSQL

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("oak.mdb")
'Create an ADO recordset object
Set rsEvents = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database with
strSQL = "SELECT * FROM events order by events.updated DESC LIMIT 0, 1 WHERE events.type = 'past';"
'Open the recordset with the SQL query 
rsEvents.Open strSQL, adoCon
'Loop through the recordset
Do While not rsEvents.EOF
	'Write the HTML to display the contents of the recordset
	Response.Write (rsEvents("ID"))
	Response.Write ("<br>")
	Response.Write (rsEvents("type"))
	Response.Write ("<br>")
	Response.Write (rsEvents("body"))
	Response.Write ("<br>")
	Response.Write (rsEvents("updated"))
	Response.Write ("<br>")
	
	'Move to the next record in the recordset
	rsEvents.MoveNext

Loop

'Reset server variables
rsEvents.Close
Set rsEvents = Nothing
Set adoCon = Nothing

%>
And it keeps giving this error:
Quote:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause.

/oak/test/index.asp, line 25


Line 25 is the SQL query
Code:
strSQL = "SELECT * FROM events order by events.updated DESC LIMIT 0, 1 WHERE events.type = 'past';"


Please Help?

- D#

Reply With Quote
  #2  
Old November 12th, 2003, 03:58 PM
jstrohofer jstrohofer is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: Cincinnati, OH USA
Posts: 111 jstrohofer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 m 3 sec
Reputation Power: 6
I'm thinking the WHERE clause must be before the ORDER BY. I'm not exactly sure what LIMIT 0, 1 does, either, so I'm not sure where that goes.

Maybe something like:

strSQL = "SELECT * FROM events WHERE events.type = 'past' ORDER BY events.updated DESC LIMIT 0, 1;"

If that doesn't work, take out the LIMIT 0, 1 and play with it.

Reply With Quote
  #3  
Old November 12th, 2003, 04:33 PM
Doug G Doug G is offline
Grumpier Old Moderator
Dev Shed God 12th Plane (10500 - 10999 posts)
 
Join Date: Jun 2003
Posts: 10,973 Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level)Doug G User rank is Major General (70000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 1 Day 17 h 34 m 20 sec
Reputation Power: 802
There is no LIMIT clause in Access, it's MySQL specific.

Reply With Quote
  #4  
Old November 12th, 2003, 04:37 PM
davidDuke's Avatar
davidDuke davidDuke is offline
<? echo "Hello World!"; ?>
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: 30pixles by 30pixels
Posts: 125 davidDuke User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 17 sec
Reputation Power: 5
Send a message via AIM to davidDuke Send a message via MSN to davidDuke
whoops

Reply With Quote
  #5  
Old November 12th, 2003, 04:38 PM
davidDuke's Avatar
davidDuke davidDuke is offline
<? echo "Hello World!"; ?>
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: 30pixles by 30pixels
Posts: 125 davidDuke User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 17 sec
Reputation Power: 5
Send a message via AIM to davidDuke Send a message via MSN to davidDuke
Question

Sorry i'm used to MySQL!

Is there any LIMIT type statement is Access?

- D#

Reply With Quote
  #6  
Old November 13th, 2003, 07:53 AM
<%=RS("Tony")%>'s Avatar
<%=RS("Tony")%> <%=RS("Tony")%> is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2003
Location: <%=Online all the Time%>
Posts: 18 <%=RS("Tony")%> User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
You maybe having a problem with the amount of users connecting to your access database (access bogs down if more than 20 users are logged in)

Reply With Quote
  #7  
Old November 13th, 2003, 08:13 AM
jstrohofer jstrohofer is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: Cincinnati, OH USA
Posts: 111 jstrohofer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 m 3 sec
Reputation Power: 6
If I am understanding the LIMIT statement correctly (I don't know a thing about MySQL), what you would want is the following:

You want only 1 row returned for this query (given the 0, 1). You want to start with the first row and grab only 1.

If this is correct, you'd want something like:

strSQL = "SELECT TOP 1 * FROM events WHERE events.type = 'past' ORDER BY events.updated DESC;"


IIRC, there's no way to say in Access to start at the 20th record and grab 10 of them. So if you wanted to do LIMIT 20, 10 I don't see how you could do that unless you did a TOP 30 and looped past the first 20 in a recordset. Not sure if that helps you or not.

J

Reply With Quote
  #8  
Old November 13th, 2003, 01:42 PM
davidDuke's Avatar
davidDuke davidDuke is offline
<? echo "Hello World!"; ?>
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: 30pixles by 30pixels
Posts: 125 davidDuke User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 17 sec
Reputation Power: 5
Send a message via AIM to davidDuke Send a message via MSN to davidDuke
That helps

Reply With Quote
  #9  
Old November 13th, 2003, 01:43 PM
davidDuke's Avatar
davidDuke davidDuke is offline
<? echo "Hello World!"; ?>
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Location: 30pixles by 30pixels
Posts: 125 davidDuke User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 17 sec
Reputation Power: 5
Send a message via AIM to davidDuke Send a message via MSN to davidDuke
Thx!

I won't be having more than 2 MAX at a time looking at the page!

- D#

Reply With Quote
Reply

Viewing: Dev Shed ForumsProgramming Languages - MoreASP Programming > Access DB Query Prob!


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway
Stay green...Green IT