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]

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.

[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" <fr_pero@yahoo.com> wrote in message
news:3f97b729$0$13467$79c14f64@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 ?
>
>
>

[Peroq]

Thanks bob and ray for your answers.

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

"Peroq" <fr_pero@yahoo.com> a écrit dans le message de
news:3f97b729$0$13467$79c14f64@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 ?
>
>
>

[Ray at]

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

Ray at work

"Peroq" <fr_pero@yahoo.com> wrote in message
news:3f9812a7$0$4468$79c14f64@nan-newsreader-02.noos.net...

> Thanks bob and ray for your answers.
>
> But for the moment, it still doesn't 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" <fr_pero@yahoo.com> wrote in message
news:3f9816ba$0$4467$79c14f64@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
>
>
>

[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.

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:%23ImKl%23YmDHA.424@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_pero@yahoo.com> wrote in message
> news:3f9816ba$0$4467$79c14f64@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
> >
> >
> >

>
>

[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

"Peroq" <fr_pero@yahoo.com> wrote in message
news:3f981adc$0$24626$79c14f64@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:%23ImKl%23YmDHA.424@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_pero@yahoo.com> wrote in message
> > news:3f9816ba$0$4467$79c14f64@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
> > >
> > >
> > >

> >
> >

>
>

[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)

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:OfLFZSZmDHA.1764@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_pero@yahoo.com> wrote in message
> news:3f981adc$0$24626$79c14f64@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:%23ImKl%23YmDHA.424@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_pero@yahoo.com> wrote in message
> > > news:3f9816ba$0$4467$79c14f64@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
> > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[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

"Peroq" <fr_pero@yahoo.com> wrote in message
news:3f982711$0$16385$79c14f64@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)

[Bob Barrows]

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.

[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 ?

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
message de news:uX8fklZmDHA.2528@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_pero@yahoo.com> wrote in message
> news:3f982711$0$16385$79c14f64@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)

>
>

[Ray at]

"Peroq" <fr_pero@yahoo.com> wrote in message
news:3f982ac4$0$6840$79c14f64@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

[Peroq]

so the essential remains.

See ya.

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

>
> "Peroq" <fr_pero@yahoo.com> wrote in message
> news:3f982ac4$0$6840$79c14f64@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
>
>

[Peroq]

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

see ya.

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> a écrit dans le message de
news:ePVeqqZmDHA.3320@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.
>
>