By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,827 Members | 2,155 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,827 IT Pros & Developers. It's quick & easy.

Trouble with ORDER BY clause

P: n/a
Hi all

I'm not sure ASP is the problem, but my SQL statement seems fine to me.

This works fine :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2"
set rs = CreateRecordset(strSQL)

This doesn't work :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
[_RechPat].cod_patrim ASC"
set rs = CreateRecordset(strSQL)

Error Number : -2147467259
Error Description : Unspecified Error (in french : Erreur non spécifiée)
1. ORDER BY clauses work fine in some other recordsets.
2. This SQL statement works fine ( i design and test my SQL queries with MS
ACCESS)
3. Database is MS ACCESS 2000
4. Server = IIS 5.0

Any ideas ?

Jul 19 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Peroq wrote:
Hi all

I'm not sure ASP is the problem, but my SQL statement seems fine to
me.

This works fine :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2"
set rs = CreateRecordset(strSQL)

This doesn't work :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2 ORDER
BY [_RechPat].cod_patrim ASC"
set rs = CreateRecordset(strSQL)

Error Number : -2147467259
Error Description : Unspecified Error (in french : Erreur non
spécifiée)
1. ORDER BY clauses work fine in some other recordsets.
2. This SQL statement works fine ( i design and test my SQL queries
with MS ACCESS)
3. Database is MS ACCESS 2000
4. Server = IIS 5.0

Any ideas ?


The first step is to "response.write strsql" so you can verify that the sql
statement string has been created correctly.

If the response.written string is a valid sql statement that can be executed
with no problem in the Access Query Builder, then I suspect a reserved word
problem. Try bracketing the cod_patrim field name: " ...
[_RechPat].[cod_patrim] ASC"

HTH,
Bob Barrows
PS. I urge you to avoid using * in your select list, because ADO has to make
an extra trip to the database to get the column names, impairing the
performance of the application. You should specify the names of the columns
you wish the query to return.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #2

P: n/a
I don't see any issues. I suggest installing the latest version of MDAC
and/or making sure you're using an OLEDB driver to connect to your database,
as opposed to ODBC.

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f***********************@nan-newsreader-01.noos.net...
Hi all

I'm not sure ASP is the problem, but my SQL statement seems fine to me.

This works fine :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2"
set rs = CreateRecordset(strSQL)

This doesn't work :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
[_RechPat].cod_patrim ASC"
set rs = CreateRecordset(strSQL)

Error Number : -2147467259
Error Description : Unspecified Error (in french : Erreur non spécifiée)
1. ORDER BY clauses work fine in some other recordsets.
2. This SQL statement works fine ( i design and test my SQL queries with MS ACCESS)
3. Database is MS ACCESS 2000
4. Server = IIS 5.0

Any ideas ?

Jul 19 '05 #3

P: n/a
Thanks bob and ray for your answers.

But for the moment, it still doesn't work.

"Peroq" <fr*****@yahoo.com> a écrit dans le message de
news:3f***********************@nan-newsreader-01.noos.net...
Hi all

I'm not sure ASP is the problem, but my SQL statement seems fine to me.

This works fine :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2"
set rs = CreateRecordset(strSQL)

This doesn't work :
strSQL = "SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
[_RechPat].cod_patrim ASC"
set rs = CreateRecordset(strSQL)

Error Number : -2147467259
Error Description : Unspecified Error (in french : Erreur non spécifiée)
1. ORDER BY clauses work fine in some other recordsets.
2. This SQL statement works fine ( i design and test my SQL queries with MS ACCESS)
3. Database is MS ACCESS 2000
4. Server = IIS 5.0

Any ideas ?

Jul 19 '05 #4

P: n/a
Did you upgrade MDAC? Also, what does your connection string look like?

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f**********************@nan-newsreader-02.noos.net...
Thanks bob and ray for your answers.

But for the moment, it still doesn't work.

Jul 19 '05 #5

P: n/a
The SQL string (with response.write) :
SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON [_RechPat].cod_niv
= NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
[_RechPat].[cod_patrim]

Error occurs when trying to open the recordset

Jul 19 '05 #6

P: n/a
When you paste this into the SQL query window in Access and run it, what
error do you get?

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f**********************@nan-newsreader-02.noos.net...
The SQL string (with response.write) :
SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON [_RechPat].cod_niv = NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
[_RechPat].[cod_patrim]

Error occurs when trying to open the recordset

Jul 19 '05 #7

P: n/a
1. I didn't update the MDAC yet.

2. Here is my connection string :
Set Conn = Server.createobject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Site Web
T&P\Technique & Patrimoine 2000.mdb;User ID=admin;"

3. When i put this SQL string in ACCESS 2000, it works fine.

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:%2*****************@TK2MSFTNGP10.phx.gbl...
When you paste this into the SQL query window in Access and run it, what
error do you get?

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f**********************@nan-newsreader-02.noos.net...
The SQL string (with response.write) :
SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON

[_RechPat].cod_niv
= NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
[_RechPat].[cod_patrim]

Error occurs when trying to open the recordset


Jul 19 '05 #8

P: n/a
Is your Access database password[less] protected? You're passing a userid
but no password. Try passing both or neither.

If that doesn't help, fire the person who created that directory structure
and then put the database in a path without spaces or other nonsensical
characters.

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f***********************@nan-newsreader-03.noos.net...
1. I didn't update the MDAC yet.

2. Here is my connection string :
Set Conn = Server.createobject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Site Web
T&P\Technique & Patrimoine 2000.mdb;User ID=admin;"

3. When i put this SQL string in ACCESS 2000, it works fine.

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:%2*****************@TK2MSFTNGP10.phx.gbl...
When you paste this into the SQL query window in Access and run it, what
error do you get?

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f**********************@nan-newsreader-02.noos.net...
The SQL string (with response.write) :
SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON

[_RechPat].cod_niv
= NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
[_RechPat].[cod_patrim]

Error occurs when trying to open the recordset



Jul 19 '05 #9

P: n/a
I just can't fire myself :)

I updated to MDAC 2.8. nothing better.

My database connection works fine as almost 100 recordset work with it.
I tested the ORDER BY clause on a simple query : it worked fine.
But with this one it doesn't work.

The odd is that error description : "undefined" ???

BTW, are you at work ?
If yes, where are you from cause where i live (France), it's 9 PM.
Kind of late to work ! (i work at home)

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:Of**************@tk2msftngp13.phx.gbl...
Is your Access database password[less] protected? You're passing a userid
but no password. Try passing both or neither.

If that doesn't help, fire the person who created that directory structure
and then put the database in a path without spaces or other nonsensical
characters.

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f***********************@nan-newsreader-03.noos.net...
1. I didn't update the MDAC yet.

2. Here is my connection string :
Set Conn = Server.createobject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Site Web
T&P\Technique & Patrimoine 2000.mdb;User ID=admin;"

3. When i put this SQL string in ACCESS 2000, it works fine.

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:%2*****************@TK2MSFTNGP10.phx.gbl...
When you paste this into the SQL query window in Access and run it, what error do you get?

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f**********************@nan-newsreader-02.noos.net...
> The SQL string (with response.write) :
> SELECT [_RechPat].* FROM _RechPat INNER JOIN NivPatri ON
[_RechPat].cod_niv
> = NivPatri.cod_niv WHERE NivPatri.position=2 ORDER BY
> [_RechPat].[cod_patrim]
>
> Error occurs when trying to open the recordset
>
>
>



Jul 19 '05 #10

P: n/a
I don't know what else to tell you. One other suggestion is to bracket
everything instead of just some of the things. ? I hate the unspecified
errors...

Sorry! Don't fire yourself.

I am at work, but in PA, USA. I'm munching on Freedom Fries as I work. ;]

Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f***********************@nan-newsreader-01.noos.net...
I just can't fire myself :)

I updated to MDAC 2.8. nothing better.

My database connection works fine as almost 100 recordset work with it.
I tested the ORDER BY clause on a simple query : it worked fine.
But with this one it doesn't work.

The odd is that error description : "undefined" ???

BTW, are you at work ?
If yes, where are you from cause where i live (France), it's 9 PM.
Kind of late to work ! (i work at home)

Jul 19 '05 #11

P: n/a
Peroq wrote:
Thanks bob and ray for your answers.

But for the moment, it still doesn't work.

I think we need to see the code used to open the recordset.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #12

P: n/a
I finally found it....

Bob advice was good.
Something was wrong with the reserved words.

I focused on the ORDER BY clause, and the problem was in the WHERE clause
("position" is a reserved word)

I was so sure to have tested that SQL string with that WHERE clause !

Finally, i had a big trouble because of a very small and simple error !

Thanks for helping me.
Sorry to have bothered you with such a silly error.

Have a nice day.

BTW : did all american people really rename the french fries ?

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:uX**************@TK2MSFTNGP12.phx.gbl...
I don't know what else to tell you. One other suggestion is to bracket
everything instead of just some of the things. ? I hate the unspecified
errors...

Sorry! Don't fire yourself.

I am at work, but in PA, USA. I'm munching on Freedom Fries as I work. ;]
Ray at work

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f***********************@nan-newsreader-01.noos.net...
I just can't fire myself :)

I updated to MDAC 2.8. nothing better.

My database connection works fine as almost 100 recordset work with it.
I tested the ORDER BY clause on a simple query : it worked fine.
But with this one it doesn't work.

The odd is that error description : "undefined" ???

BTW, are you at work ?
If yes, where are you from cause where i live (France), it's 9 PM.
Kind of late to work ! (i work at home)


Jul 19 '05 #13

P: n/a

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f**********************@nan-newsreader-02.noos.net...

BTW : did all american people really rename the french fries ?


Nah, I don't think so. We just stopped eating them or believing that they
exist at all. ;] And we now freedom-kiss our wives, too. d:

Ray at work
Jul 19 '05 #14

P: n/a
so the essential remains.

See ya.

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:eL**************@TK2MSFTNGP10.phx.gbl...

"Peroq" <fr*****@yahoo.com> wrote in message
news:3f**********************@nan-newsreader-02.noos.net...

BTW : did all american people really rename the french fries ?


Nah, I don't think so. We just stopped eating them or believing that they
exist at all. ;] And we now freedom-kiss our wives, too. d:

Ray at work

Jul 19 '05 #15

P: n/a
Thanks Bob. You were right.
position was a reserved word.

see ya.

"Bob Barrows" <re******@NOyahoo.SPAMcom> a écrit dans le message de
news:eP**************@tk2msftngp13.phx.gbl...
Peroq wrote:
Thanks bob and ray for your answers.

But for the moment, it still doesn't work.

I think we need to see the code used to open the recordset.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.