Trouble with ORDER BY clause

[Peroq]

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 ?

 

[Bob Barrows]

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.

 

[Ray at]

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]

Thanks bob and ray for your answers.

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

 

[Ray at]

Did you upgrade MDAC? Also, what does your connection string look like?

Ray at work

 

[Peroq]

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

 

[Ray at]

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

Ray at work

 

[Peroq]

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.

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

Ray at work

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

 

[Ray at]

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

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.

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

Ray at work

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

 

[Peroq]

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)

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

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.

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

Ray at work

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

 

[Ray at]

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

 

[Bob Barrows]

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

 

[Peroq]

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 ?

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

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]

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

 

[Peroq]

so the essential remains.

See ya.

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

 

[Peroq]

Thanks Bob. You were right.
position was a reserved word.

see ya.