Professional Web Applications Themes

Trouble with ORDER BY clause - ASP

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

  1. #1

    Default Trouble with ORDER BY clause

    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 Guest

  2. #2

    Default Re: Trouble with ORDER BY clause

    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 Guest

  3. #3

    Default Re: Trouble with ORDER BY clause

    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> wrote in message
    news:3f97b729$0$13467$79c14f64nan-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 Guest

  4. #4

    Default Re: Trouble with ORDER BY clause

    Thanks bob and ray for your answers.

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

    "Peroq" <fr_pero> a écrit dans le message de
    news:3f97b729$0$13467$79c14f64nan-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 Guest

  5. #5

    Default Re: Trouble with ORDER BY clause

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

    Ray at work

    "Peroq" <fr_pero> wrote in message
    news:3f9812a7$0$4468$79c14f64nan-newsreader-02.noos.net...
    > Thanks bob and ray for your answers.
    >
    > But for the moment, it still doesn't work.

    Ray at Guest

  6. #6

    Default 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 Guest

  7. #7

    Default 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" <fr_pero> wrote in message
    news:3f9816ba$0$4467$79c14f64nan-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 Guest

  8. #8

    Default 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%23YmDHA.424TK2MSFTNGP10.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> wrote in message
    > news:3f9816ba$0$4467$79c14f64nan-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 Guest

  9. #9

    Default 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" <fr_pero> wrote in message
    news:3f981adc$0$24626$79c14f64nan-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.424TK2MSFTNGP10.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> wrote in message
    > > news:3f9816ba$0$4467$79c14f64nan-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 Guest

  10. #10

    Default 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:OfLFZSZmDHA.1764tk2msftngp13.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> wrote in message
    > news:3f981adc$0$24626$79c14f64nan-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.424TK2MSFTNGP10.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> wrote in message
    > > > news:3f9816ba$0$4467$79c14f64nan-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 Guest

  11. #11

    Default 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" <fr_pero> wrote in message
    news:3f982711$0$16385$79c14f64nan-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 Guest

  12. #12

    Default Re: Trouble with ORDER BY clause

    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 Guest

  13. #13

    Default 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:uX8fklZmDHA.2528TK2MSFTNGP12.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> wrote in message
    > news:3f982711$0$16385$79c14f64nan-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)
    >
    >

    Peroq Guest

  14. #14

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


    "Peroq" <fr_pero> wrote in message
    news:3f982ac4$0$6840$79c14f64nan-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


    Ray at Guest

  15. #15

    Default 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:eLfp2vZmDHA.2772TK2MSFTNGP10.phx.gbl...
    >
    > "Peroq" <fr_pero> wrote in message
    > news:3f982ac4$0$6840$79c14f64nan-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 Guest

  16. #16

    Default Re: Trouble with ORDER BY clause

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

    see ya.

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

    Peroq Guest

Similar Threads

  1. order form trouble
    By jade3189 in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: July 13th, 07:58 PM
  2. ORDER BY clause in asp.net dataset
    By davidxtaylor in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: February 22nd, 07:05 PM
  3. Strange results of ORDER BY clause when item begins with slash orbackslash
    By SCassidy@overlandstorage.com in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 19th, 09:36 PM
  4. Where Clause: Order of Precedence
    By GH in forum ASP Database
    Replies: 2
    Last Post: July 20th, 08:09 PM
  5. ORDER BY clause causes read-only recordset
    By Brian in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 9th, 07:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139