Trouble with ORDER BY clause

Discussion in 'ASP General' started by Peroq, Oct 23, 2003.

  1. Peroq

    Peroq Guest

    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, Oct 23, 2003
    #1
    1. Advertising

  2. Peroq

    Bob Barrows Guest

    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.
     
    Bob Barrows, Oct 23, 2003
    #2
    1. Advertising

  3. Peroq

    Ray at Guest

    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" <> wrote in message
    news:3f97b729$0$13467$...
    > 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, Oct 23, 2003
    #3
  4. Peroq

    Peroq Guest

    Thanks bob and ray for your answers.

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

    "Peroq" <> a écrit dans le message de
    news:3f97b729$0$13467$...
    > 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, Oct 23, 2003
    #4
  5. Peroq

    Ray at Guest

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

    Ray at work

    "Peroq" <> wrote in message
    news:3f9812a7$0$4468$...
    > Thanks bob and ray for your answers.
    >
    > But for the moment, it still doesn't work.
     
    Ray at, Oct 23, 2003
    #5
  6. Peroq

    Peroq Guest

    Re: Trouble with ORDER BY clause : more info

    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, Oct 23, 2003
    #6
  7. Peroq

    Ray at Guest

    Re: Trouble with ORDER BY clause : more info

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

    Ray at work

    "Peroq" <> wrote in message
    news:3f9816ba$0$4467$...
    > 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, Oct 23, 2003
    #7
  8. Peroq

    Peroq Guest

    Re: Trouble with ORDER BY clause : more info

    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%...
    > When you paste this into the SQL query window in Access and run it, what
    > error do you get?
    >
    > Ray at work
    >
    > "Peroq" <> wrote in message
    > news:3f9816ba$0$4467$...
    > > 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, Oct 23, 2003
    #8
  9. Peroq

    Ray at Guest

    Re: Trouble with ORDER BY clause : more info

    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" <> wrote in message
    news:3f981adc$0$24626$...
    > 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%...
    > > When you paste this into the SQL query window in Access and run it, what
    > > error do you get?
    > >
    > > Ray at work
    > >
    > > "Peroq" <> wrote in message
    > > news:3f9816ba$0$4467$...
    > > > 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, Oct 23, 2003
    #9
  10. Peroq

    Peroq Guest

    Re: Trouble with ORDER BY clause : more info

    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:...
    > 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" <> wrote in message
    > news:3f981adc$0$24626$...
    > > 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%...
    > > > When you paste this into the SQL query window in Access and run it,

    what
    > > > error do you get?
    > > >
    > > > Ray at work
    > > >
    > > > "Peroq" <> wrote in message
    > > > news:3f9816ba$0$4467$...
    > > > > 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, Oct 23, 2003
    #10
  11. Peroq

    Ray at Guest

    Re: Trouble with ORDER BY clause : more info

    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" <> wrote in message
    news:3f982711$0$16385$...
    > 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, Oct 23, 2003
    #11
  12. Peroq

    Bob Barrows Guest

    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.
     
    Bob Barrows, Oct 23, 2003
    #12
  13. Peroq

    Peroq Guest

    I'M SO SORRY !!!!!!!!!!!!

    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:...
    > 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" <> wrote in message
    > news:3f982711$0$16385$...
    > > 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)

    >
    >
     
    Peroq, Oct 23, 2003
    #13
  14. Peroq

    Ray at Guest

    Re: I'M SO SORRY !!!!!!!!!!!!

    "Peroq" <> wrote in message
    news:3f982ac4$0$6840$...
    >
    > 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
     
    Ray at, Oct 23, 2003
    #14
  15. Peroq

    Peroq Guest

    Re: I'M SO SORRY !!!!!!!!!!!!

    so the essential remains.

    See ya.

    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> a écrit dans le
    message de news:...
    >
    > "Peroq" <> wrote in message
    > news:3f982ac4$0$6840$...
    > >
    > > 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, Oct 23, 2003
    #15
  16. Peroq

    Peroq Guest

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

    see ya.

    "Bob Barrows" <> a écrit dans le message de
    news:...
    > 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, Oct 23, 2003
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Smith John

    Order by Clause....

    Smith John, Dec 7, 2004, in forum: ASP .Net
    Replies:
    7
    Views:
    526
    John Smith
    Dec 7, 2004
  2. Soren Kuula
    Replies:
    2
    Views:
    545
    Soren Kuula
    Feb 1, 2004
  3. Index
    Replies:
    6
    Views:
    429
    Jerry Coffin
    Jun 1, 2006
  4. cspoh
    Replies:
    0
    Views:
    270
    cspoh
    Jul 31, 2003
  5. Stephan Kämper
    Replies:
    2
    Views:
    259
    Stephan Kämper
    Jan 18, 2004
Loading...

Share This Page