[Microsoft][ODBC Microsoft Access Driver] Syntax Error in ORDER BY clause

[Marc Everlove]

Here is the statement...

SELECT *, rand() as random_number
FROM image_img
ORDER BY random_number DESC LIMIT 1

I suspect that the rand() is not supported in MS Access. I am not sure what
syntex to put in it's place...

Can anybody tell me what I have done wrong?

 

[Jeff Boyce]

Marc

If I were testing this SQL, I would first drop the ORDER BY clause and see
what Access returns for the rest.

What is the purpose of the "LIMIT 1" clause?

Jeff Boyce
<Office/Access MVP>

 

[Marc Everlove]

Wow that was quick!

Ok, I am a network guy, so bear with me.

I will try to drop that statement, and see what happens. I am using an
extension for Dreamweaver... I got the statement from a tutorial. I am
assuming that it is for a MySQL DB as they refer to a php file...

Regarding the limit 1 clause, I have no idea. I am just struggling through
this.

I am ultimately attempting to create an image gallery that allows users to
upload and display images...

Thanks in advance!

 

[Marc Everlove]

ok I tried

SELECT *, rand() as random_number
FROM image_img

and I got:

Undefined function 'rand' in expression

Looks like another looooong night...

Thanks!!!

 

[Van T. Dinh]

The random number function in VBA is Rnd().

If Rnd() is used in an SQL String in Access, it should be OK. However, you
are only using JET (NOT Access) and Rnd() may NOT work for you.

 

[Marc Everlove]

Ok I rewrote the statement so it is now:

SELECT *
FROM image_img
ORDER BY rnd(1(id_img) * 0 + 1)

It doesn't return an error but the image is always the same... It doesn't
randomize!

What's amiss with it???

Thanks in advance!

 

[Dirk Goldgar]

Ok I rewrote the statement so it is now:

SELECT *
FROM image_img
ORDER BY rnd(1(id_img) * 0 + 1)

It doesn't return an error but the image is always the same... It
doesn't randomize!

What's amiss with it???

See this link:

http://www.mvps.org/access/queries/qry0011.htm
Queries: Create a query that returns Random records

 

[Marc Everlove]

Ok, I did my best to follow the directions but now it returns the error:

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

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'randomizer' in expression.

/uptest/ugh02.asp, line 14

Of course line 14 is where I open the recordset...

I added the module to the database, but frankly, I am a noob at this so I am not certain I did it correctly. I have been attempting to look up information on how to accomplish this all, but have not been very successful. However, the randomizer query you linked to has come up a few times, so it is probably the best option. Now if I could just figure out how to make it work...

I really appreciate all the assistance!

 

[Douglas J. Steele]

How are calling your query? Is it from outside of Access (VB, VB.Net, ASP,
etc.)? If so, I'm afraid you're out of luck: you can only use VBA
user-defined functions from inside Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ok, I did my best to follow the directions but now it returns the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'randomizer' in
expression.
/uptest/ugh02.asp, line 14
Of course line 14 is where I open the recordset...
I added the module to the database, but frankly, I am a noob at this so I am
not certain I did it correctly. I have been attempting to look up
information on how to accomplish this all, but have not been very
successful. However, the randomizer query you linked to has come up a few
times, so it is probably the best option. Now if I could just figure out how
to make it work...
I really appreciate all the assistance!

 

[Marc Everlove]

I am attempting to call it from a webpage...
In a nutshell, I am attempting to retreive random images from paths stored
in the database. Basically a photogallery. In a nutshell I would like to
make http://victorsfunnies.com dynamic so I don't have to hand-code
everything in there. I have the file upload process squared away, but I
woule like to display a randome image so the user could click a link that
posts back to the same url just showing a different image...

I am using Dreamweaver 8 and the Interakt extension (which is supposed to
support asp but unsurprisingly doesn't work)

Victorsfunnies.com is basically a proving ground for a far more serious
project I have been working on. I would like to upload and return real
estate listings...

I am going crazy...

I really appreciate your assistance, I value your time. I will post my
code...

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connGallery.asp" -->
<!--#include file="../includes/tNG/tNG.inc.asp" -->
<%
Dim rsImages
Dim rsImages_numRows

Set rsImages = Server.CreateObject("ADODB.Recordset")
rsImages.ActiveConnection = MM_connGallery_STRING
rsImages.Source = "select top 1 image_img.*from image_img where
randomizer() = 0 order by rnd(isnull(image_img.id_img) * 0 + 1)"
rsImages.CursorType = 0
rsImages.CursorLocation = 2
rsImages.LockType = 1
rsImages.Open()

rsImages_numRows = 0
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<img src="<%=tNG_showDynamicThumbnail("../", "../Uploads/",
"{rsImages.filename_img}", 50, 50, true)%>" />
</body>
</html>
<%
rsImages.Close()
Set rsImages = Nothing
%>

How are calling your query? Is it from outside of Access (VB, VB.Net, ASP,
etc.)? If so, I'm afraid you're out of luck: you can only use VBA
user-defined functions from inside Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ok, I did my best to follow the directions but now it returns the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'randomizer'
in expression.
/uptest/ugh02.asp, line 14
Of course line 14 is where I open the recordset...
I added the module to the database, but frankly, I am a noob at this so I
am not certain I did it correctly. I have been attempting to look up
information on how to accomplish this all, but have not been very
successful. However, the randomizer query you linked to has come up a few
times, so it is probably the best option. Now if I could just figure out
how to make it work...
I really appreciate all the assistance!

See this link:

http://www.mvps.org/access/queries/qry0011.htm
Queries: Create a query that returns Random records

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

As I implied, if you're not calling from within Access, I don't believe
there's any way you can return random rows.

You might be able to have a list of possible images in memory, and choose
one at random from that and explicitly select that image back. Or if you
number your images, you could choose a random number and explicitly select
the image with that number.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am attempting to call it from a webpage...
In a nutshell, I am attempting to retreive random images from paths stored
in the database. Basically a photogallery. In a nutshell I would like to
make http://victorsfunnies.com dynamic so I don't have to hand-code
everything in there. I have the file upload process squared away, but I
woule like to display a randome image so the user could click a link that
posts back to the same url just showing a different image...

I am using Dreamweaver 8 and the Interakt extension (which is supposed to
support asp but unsurprisingly doesn't work)

Victorsfunnies.com is basically a proving ground for a far more serious
project I have been working on. I would like to upload and return real
estate listings...

I am going crazy...

I really appreciate your assistance, I value your time. I will post my
code...

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connGallery.asp" -->
<!--#include file="../includes/tNG/tNG.inc.asp" -->
<%
Dim rsImages
Dim rsImages_numRows

Set rsImages = Server.CreateObject("ADODB.Recordset")
rsImages.ActiveConnection = MM_connGallery_STRING
rsImages.Source = "select top 1 image_img.*from image_img where
randomizer() = 0 order by rnd(isnull(image_img.id_img) * 0 + 1)"
rsImages.CursorType = 0
rsImages.CursorLocation = 2
rsImages.LockType = 1
rsImages.Open()

rsImages_numRows = 0
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<img src="<%=tNG_showDynamicThumbnail("../", "../Uploads/",
"{rsImages.filename_img}", 50, 50, true)%>" />
</body>
</html>
<%
rsImages.Close()
Set rsImages = Nothing
%>

How are calling your query? Is it from outside of Access (VB, VB.Net,
ASP, etc.)? If so, I'm afraid you're out of luck: you can only use VBA
user-defined functions from inside Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ok, I did my best to follow the directions but now it returns the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'randomizer'
in expression.
/uptest/ugh02.asp, line 14
Of course line 14 is where I open the recordset...
I added the module to the database, but frankly, I am a noob at this so I
am not certain I did it correctly. I have been attempting to look up
information on how to accomplish this all, but have not been very
successful. However, the randomizer query you linked to has come up a few
times, so it is probably the best option. Now if I could just figure out
how to make it work...
I really appreciate all the assistance!

Ok I rewrote the statement so it is now:

SELECT *
FROM image_img
ORDER BY rnd(1(id_img) * 0 + 1)

It doesn't return an error but the image is always the same... It
doesn't randomize!

What's amiss with it???

See this link:

http://www.mvps.org/access/queries/qry0011.htm
Queries: Create a query that returns Random records

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

message [...]

Or if you number your images, you could choose a random number and
explicitly select the image with that number.

That's what I was going to suggest.

 

[Dirk Goldgar]

I am attempting to call it from a webpage...

Sorry, I didn't realize that you were executing your query from outside
Access. Under those circumstances, you won't be able to use VBA
functions, so that approach to picking random records won't work. I'd
go with Doug Steele's idea of generatig a random number in code, and the
selecting the record that has that number assigned to it.

 

[Marc Everlove]

I was thinking as much...

The unique key is an autonumbered field so I was thinking I could use that,
BUT if I delete records the count won't be contiguous.

*Sigh*

I have SQL but I only have 10 licenses so I can't use it on a production
website. $4,000 for a processor licence is a bit steep for this project.
According to my predictions it would take 7,736 years to show a ROI based on
my current Adsense earnings...

This makes a good argument for going with MySQL, but I feel like that's
almost like colaberating with the enemy... Frankly I am trying to learn
to program and I woule like to learn ONE language and get proficient, before
I go on to others. ASP.Net is where I think the future lays, but it seems
all the tutorials I come across are for php...

My only alternative that I can see at this point is to use a 3rd party
Windows based web host and run MS SQL and go that route. I am just about
ready to take a flying leap from my desk...

*thud*

Thanks for all of your help...

Best Regards,
Marc Everlove

As I implied, if you're not calling from within Access, I don't believe
there's any way you can return random rows.

You might be able to have a list of possible images in memory, and choose
one at random from that and explicitly select that image back. Or if you
number your images, you could choose a random number and explicitly select
the image with that number.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am attempting to call it from a webpage...
In a nutshell, I am attempting to retreive random images from paths
stored in the database. Basically a photogallery. In a nutshell I would
like to make http://victorsfunnies.com dynamic so I don't have to
hand-code everything in there. I have the file upload process squared
away, but I woule like to display a randome image so the user could click
a link that posts back to the same url just showing a different image...

I am using Dreamweaver 8 and the Interakt extension (which is supposed to
support asp but unsurprisingly doesn't work)

Victorsfunnies.com is basically a proving ground for a far more serious
project I have been working on. I would like to upload and return real
estate listings...

I am going crazy...

I really appreciate your assistance, I value your time. I will post my
code...

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connGallery.asp" -->
<!--#include file="../includes/tNG/tNG.inc.asp" -->
<%
Dim rsImages
Dim rsImages_numRows

Set rsImages = Server.CreateObject("ADODB.Recordset")
rsImages.ActiveConnection = MM_connGallery_STRING
rsImages.Source = "select top 1 image_img.*from image_img where
randomizer() = 0 order by rnd(isnull(image_img.id_img) * 0 + 1)"
rsImages.CursorType = 0
rsImages.CursorLocation = 2
rsImages.LockType = 1
rsImages.Open()

rsImages_numRows = 0
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<img src="<%=tNG_showDynamicThumbnail("../", "../Uploads/",
"{rsImages.filename_img}", 50, 50, true)%>" />
</body>
</html>
<%
rsImages.Close()
Set rsImages = Nothing
%>

How are calling your query? Is it from outside of Access (VB, VB.Net,
ASP, etc.)? If so, I'm afraid you're out of luck: you can only use VBA
user-defined functions from inside Access.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ok, I did my best to follow the directions but now it returns the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function
'randomizer' in expression.
/uptest/ugh02.asp, line 14
Of course line 14 is where I open the recordset...
I added the module to the database, but frankly, I am a noob at this so
I am not certain I did it correctly. I have been attempting to look up
information on how to accomplish this all, but have not been very
successful. However, the randomizer query you linked to has come up a
few times, so it is probably the best option. Now if I could just figure
out how to make it work...
I really appreciate all the assistance!



Ok I rewrote the statement so it is now:

SELECT *
FROM image_img
ORDER BY rnd(1(id_img) * 0 + 1)

It doesn't return an error but the image is always the same... It
doesn't randomize!

What's amiss with it???

See this link:

http://www.mvps.org/access/queries/qry0011.htm
Queries: Create a query that returns Random records

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I was thinking as much...

The unique key is an autonumbered field so I was thinking I could use
that, BUT if I delete records the count won't be contiguous.

*Sigh*

I have SQL but I only have 10 licenses so I can't use it on a
production website. $4,000 for a processor licence is a bit steep for
this project. According to my predictions it would take 7,736 years
to show a ROI based on my current Adsense earnings...

This makes a good argument for going with MySQL, but I feel like
that's almost like colaberating with the enemy... Frankly I am
trying to learn to program and I woule like to learn ONE language and
get proficient, before I go on to others. ASP.Net is where I think
the future lays, but it seems all the tutorials I come across are for
php...

My only alternative that I can see at this point is to use a 3rd party
Windows based web host and run MS SQL and go that route.

What are the characteristics of this website? What kind of traffic do
you expect? Is it possible you could use MSDE for the database?

 

[Marc Everlove]

Hmmm, Now that is something I hadn't considered...

MSDE goes by concurrent connections correct? I imagine that might just work.

I already have MS SQL installed on the server. It doesn't really matter
though because I am not using it for anything. I had a chat with a fellow
from MS Licencing and he gave me the lowdown on running SQL. From what I
understand with SQL it's per seat licensing, so it is impossible to run
anything other than a Processor licence...

I hadn't even considered MSDE. I think I will need to uninstall SQL, but
Cest 'la Vie.

The url is http://victorsfunnies.com although I have the scripts I have been
discussing elsewhere. Currently Vic's Funnies is hand coded for each
image... You can see where it gets tedious...

Thanks! Hope you get a chuckle out of the site...