Odd limitation with CF and Access XP?

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

March 19th, 2005, 08:21 AM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

Odd limitation with CF and Access XP?

Is there somee sort of maximum rows for an Access and CF connection?

I have isolated a problem down to a single issue. If I try to grab data at row 942 or higher I get nothing returned. Everything below it is fine. Though I can't find anything inconsistent in the data that would be messing anything up!

Here are the two rows:

Code:

ID	Date		Time		Duration	Episode			Series
941	02/15/2005	11:00:00 am	0:27:40		Alexander The Great	WESTERN TRADITION I & II, THE

942	02/15/2005	11:30:00 am	0:27:40		Hellenistic Age, The	WESTERN TRADITION I & II, THE

And here is the page's code (simplified for isolation).

Code:

<cfquery name="schedule_all" datasource="month">
SELECT *
FROM month 
WHERE Duration >= '0:14:00'
</cfquery>

<html>
<head>
<title><cfoutput>#schedule_all.Series[942]#</cfoutput></title>
</head>

<body>
<cfoutput>
Series: #schedule_all.Series[942]#<br>
Episode: #schedule_all.Episode[942]#<br>
Date/Time:#schedule_all.Date[942]# at #TimeFormat(schedule_all.Time[942], 'h:mm tt')#
</cfoutput>

</body>
</html>

Again - if I use 941 or lower it works fine. When I used= 942 or higher, it doesn't work!

March 19th, 2005, 08:32 AM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

Oddly - the following code displays all results fine (up to 2000+), including 941 and 942...what's going on here? Why can't I grab them specifically as shown above?

Code:

<table>
<cfoutput>
<cfloop query="schedule_all" startrow="1">
<tr><td>ID: #schedule_all.ID# | Series: #schedule_all.Series# | 
Episode: #schedule_all.Episode# | 
Date/Time:#schedule_all.Date# at #TimeFormat(schedule_all.Time, 'h:mm tt')#</td></tr>
</cfloop>
</cfoutput>
</table>

March 19th, 2005, 09:40 AM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,626

Time spent in forums: 1 Week 4 Days 10 h 6 m 34 sec
Reputation Power: 53

Are you sure that the first query is returning rows 941 and 942? Do a CFDUMP to confirm that they aren't there. If they aren't I suspect it has something to do with the where clause.

Just to be clear, no, there is no "limitation" using CF and Access. If the records aren't showing up it's something in your query or your output code.

__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian.
How to Post a Question in the Forums

March 19th, 2005, 01:16 PM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

Quote:

Originally Posted by kiteless

Just to be clear, no, there is no "limitation" using CF and Access. If the records aren't showing up it's something in your query or your output code.

Thanks.

I've realized that I'm (or CF) referring to the wrong ID. i.e. the Access database of course has an autonumber column "ID" for sorting records. This is different however from the furthest-left column I see when I do cfdump, which is apparently generated by CF and refers to different rows depending on the query.

example:

Code:

CF'sID  DATE          DURATION    EPISODE                 ID      SERIES                   TIME 
128     02/04/2005     0:29:02     Reading as a Writer     313     ENGLISH COMPOSITION     6:30:00 pm

The problem I'm having in my first-post example is that #schedule_all.ID[941]# is bringing up a record according to the far-leftmost CF's ID, rather than my database name's ID.

In particular, I'm passing a variable from another cfm page, and its bringing up the wrong record. Nonetheless, I've at least realized why it "stops" at 941 - because that's the last row, even though my access ID goes up to 2000 something.

How do I correctly distinguish between the two?

March 19th, 2005, 03:34 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,626

Time spent in forums: 1 Week 4 Days 10 h 6 m 34 sec
Reputation Power: 53

Remember that when you use the array notation in the query output, you are referencing the row number of the query, not the ID field in your database. So when you say #schedule_all.ID[941]#, you are just getting the ID value that is in the 941st position in the query. It's not referencing your ID value, it's referencing the row number of the query result set itself, just like when you reference an array element by number, you're specifying the array position, not the VALUE that is in that array position. Can you just output it like this?

<cfoutput query="schedule_all">
ID: #schedule_all.id#<br>
Series: #schedule_all.Series#<br>
Episode: #schedule_all.Episode#<br>
Date/Time:#schedule_all.Date# at #TimeFormat(schedule_all.Time, 'h:mm tt')#
</cfoutput>

If you want to be able to get at a specific VALUE, you'll have to loop over the query and find the value that you want. You could also use a query of queries and specify a specific ID value in the WHERE clause.

March 20th, 2005, 08:47 AM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

Thanks kiteless - that makes sense.

Code:

<cfquery name="schedule_all" datasource="month">
SELECT *
FROM month 
WHERE Duration >= '0:14:00' and ID = #URL.ID#
</cfquery>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: