MDX->Recordset: Sorting a field with "[" and "]" in the field name

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by Chet Crome » Thu, 22 May 2003 22:49:12



I'm having trouble using the Sort property of a recordset to sort my data by
a field who's name contains "[" and "]". This field name is generated when I
load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
don't think I have controlover the characters in the name.

How do I sort by this field?

When I put:

rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
or
rs.sort= 1 '(The first field)
or
rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"

I get this error:
 ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

I am using a client-side cursor.
I've tried using about every comgination of CursorType and LockType.

Can someone help me? Seems like this should be easy enough to do; maybe
there is an escape character I can use instead of [ or ] in the field
name???

Thank you.

Chet

 
 
 

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by Chet Crome » Thu, 22 May 2003 22:49:23


I'm having trouble using the Sort property of a recordset to sort my data by
a field who's name contains "[" and "]". This field name is generated when I
load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
don't think I have controlover the characters in the name.

How do I sort by this field?

When I put:

rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
or
rs.sort= 1 '(The first field)
or
rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"

I get this error:
 ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

I am using a client-side cursor.
I've tried using about every comgination of CursorType and LockType.

Can someone help me? Seems like this should be easy enough to do; maybe
there is an escape character I can use instead of [ or ] in the field
name???

Thank you.

Chet

 
 
 

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by Val Mazu » Thu, 22 May 2003 22:54:11


Hi Chet,

I have replied on your previous posting, but looks like all my replies for
that day disappeared somehow. Try to use just field name, without database
and owner names. Something like

rs.sort="MEMBER_CAPTION"

If it does not help, then check what is a field name recordset shows for
that MEMBER_CAPTION field and post it here

--
Val Mazur
Microsoft MVP


Quote:> I'm having trouble using the Sort property of a recordset to sort my data
by
> a field who's name contains "[" and "]". This field name is generated when
I
> load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
> don't think I have controlover the characters in the name.

> How do I sort by this field?

> When I put:

> rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
> or
> rs.sort= 1 '(The first field)
> or
> rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"

> I get this error:
>  ADODB.Recordset error '800a0bb9'
> Arguments are of the wrong type, are out of acceptable range, or are in
> conflict with one another.

> I am using a client-side cursor.
> I've tried using about every comgination of CursorType and LockType.

> Can someone help me? Seems like this should be easy enough to do; maybe
> there is an escape character I can use instead of [ or ] in the field
> name???

> Thank you.

> Chet

 
 
 

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by Chet Crome » Fri, 23 May 2003 00:45:12


Here's a little more info:

rs.Sort="MEMBER_CAPTION" returns the same error:

"ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. "
Here are the results of the following code to get all the field names:

for each fld in rs.fields: debug.print fld.name :next fld

[Agent].[City].[MEMBER_CAPTION]
[Agent].[Agent].[MEMBER_CAPTION]
[Measures].[Coverage].[Year].[All].[2003]
[Measures].[Upsell].[Year].[All].[2003]
[Measures].[Fuel].[Year].[All].[2003]
[Measures].[AddDrv].[Year].[All].[2003]
[Measures].[Other].[Year].[All].[2003]
[Measures].[TTL Revenue].[Year].[All].[2003]
[Measures].[Days].[Year].[All].[2003]
[Measures].[Yield].[Year].[All].[2003]
[Measures].[Ratio].[Year].[All].[2003]
[Measures].[Commission].[Year].[All].[2003]

I want to sort by [Agent].[Agent].[MEMBER_CAPTION]; the MDX statement sorts it by [Agent].[City].[MEMBER_CAPTION] and then [Agent].[Agent].[MEMBER_CAPTION] since they represent different levels in the same dimension.


> Hi Chet,

> I have replied on your previous posting, but looks like all my replies for
> that day disappeared somehow. Try to use just field name, without database
> and owner names. Something like

> rs.sort="MEMBER_CAPTION"

> If it does not help, then check what is a field name recordset shows for
> that MEMBER_CAPTION field and post it here

> --
> Val Mazur
> Microsoft MVP



> > I'm having trouble using the Sort property of a recordset to sort my data
> by
> > a field who's name contains "[" and "]". This field name is generated when
> I
> > load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
> > don't think I have controlover the characters in the name.

> > How do I sort by this field?

> > When I put:

> > rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
> > or
> > rs.sort= 1 '(The first field)
> > or
> > rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"

> > I get this error:
> >  ADODB.Recordset error '800a0bb9'
> > Arguments are of the wrong type, are out of acceptable range, or are in
> > conflict with one another.

> > I am using a client-side cursor.
> > I've tried using about every comgination of CursorType and LockType.

> > Can someone help me? Seems like this should be easy enough to do; maybe
> > there is an escape character I can use instead of [ or ] in the field
> > name???

> > Thank you.

> > Chet

 
 
 

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by db » Fri, 23 May 2003 09:33:32


I think square brackets are special characters...used to enclose a field name that contains another special character.  So, just a guess, but how about in your sort code if you enclose the table/field names with an additional set of square brackets.  Like so:

rs.sort="[[Agent]].[[Agent]].[[MEMBER_CAPTION]]"

Just a thought.

Good luck,
David

  Here's a little more info:

  rs.Sort="MEMBER_CAPTION" returns the same error:

  "ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. "
  Here are the results of the following code to get all the field names:

  for each fld in rs.fields: debug.print fld.name :next fld

  [Agent].[City].[MEMBER_CAPTION]
  [Agent].[Agent].[MEMBER_CAPTION]
  [Measures].[Coverage].[Year].[All].[2003]
  [Measures].[Upsell].[Year].[All].[2003]
  [Measures].[Fuel].[Year].[All].[2003]
  [Measures].[AddDrv].[Year].[All].[2003]
  [Measures].[Other].[Year].[All].[2003]
  [Measures].[TTL Revenue].[Year].[All].[2003]
  [Measures].[Days].[Year].[All].[2003]
  [Measures].[Yield].[Year].[All].[2003]
  [Measures].[Ratio].[Year].[All].[2003]
  [Measures].[Commission].[Year].[All].[2003]

  I want to sort by [Agent].[Agent].[MEMBER_CAPTION]; the MDX statement sorts it by [Agent].[City].[MEMBER_CAPTION] and then [Agent].[Agent].[MEMBER_CAPTION] since they represent different levels in the same dimension.


  > Hi Chet,
  >
  > I have replied on your previous posting, but looks like all my replies for
  > that day disappeared somehow. Try to use just field name, without database
  > and owner names. Something like
  >
  > rs.sort="MEMBER_CAPTION"
  >
  > If it does not help, then check what is a field name recordset shows for
  > that MEMBER_CAPTION field and post it here
  >
  > --
  > Val Mazur
  > Microsoft MVP
  >
  >
  >


  > > I'm having trouble using the Sort property of a recordset to sort my data
  > by
  > > a field who's name contains "[" and "]". This field name is generated when
  > I
  > > load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
  > > don't think I have controlover the characters in the name.
  > >
  > > How do I sort by this field?
  > >
  > > When I put:
  > >
  > > rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
  > > or
  > > rs.sort= 1 '(The first field)
  > > or
  > > rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"
  > >
  > > I get this error:
  > >  ADODB.Recordset error '800a0bb9'
  > > Arguments are of the wrong type, are out of acceptable range, or are in
  > > conflict with one another.
  > >
  > > I am using a client-side cursor.
  > > I've tried using about every comgination of CursorType and LockType.
  > >
  > > Can someone help me? Seems like this should be easy enough to do; maybe
  > > there is an escape character I can use instead of [ or ] in the field
  > > name???
  > >
  > > Thank you.
  > >
  > > Chet
  > >
  > >
  >
  >

 
 
 

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by Chet Crome » Fri, 23 May 2003 22:08:46


Tried that, no luck. :) I wonder if there's some sort of escape code I should use to represent the field name???

  I think square brackets are special characters...used to enclose a field name that contains another special character.  So, just a guess, but how about in your sort code if you enclose the table/field names with an additional set of square brackets.  Like so:

  rs.sort="[[Agent]].[[Agent]].[[MEMBER_CAPTION]]"

  Just a thought.

  Good luck,
  David


    Here's a little more info:

    rs.Sort="MEMBER_CAPTION" returns the same error:

    "ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. "
    Here are the results of the following code to get all the field names:

    for each fld in rs.fields: debug.print fld.name :next fld

    [Agent].[City].[MEMBER_CAPTION]
    [Agent].[Agent].[MEMBER_CAPTION]
    [Measures].[Coverage].[Year].[All].[2003]
    [Measures].[Upsell].[Year].[All].[2003]
    [Measures].[Fuel].[Year].[All].[2003]
    [Measures].[AddDrv].[Year].[All].[2003]
    [Measures].[Other].[Year].[All].[2003]
    [Measures].[TTL Revenue].[Year].[All].[2003]
    [Measures].[Days].[Year].[All].[2003]
    [Measures].[Yield].[Year].[All].[2003]
    [Measures].[Ratio].[Year].[All].[2003]
    [Measures].[Commission].[Year].[All].[2003]

    I want to sort by [Agent].[Agent].[MEMBER_CAPTION]; the MDX statement sorts it by [Agent].[City].[MEMBER_CAPTION] and then [Agent].[Agent].[MEMBER_CAPTION] since they represent different levels in the same dimension.


    > Hi Chet,
    >
    > I have replied on your previous posting, but looks like all my replies for
    > that day disappeared somehow. Try to use just field name, without database
    > and owner names. Something like
    >
    > rs.sort="MEMBER_CAPTION"
    >
    > If it does not help, then check what is a field name recordset shows for
    > that MEMBER_CAPTION field and post it here
    >
    > --
    > Val Mazur
    > Microsoft MVP
    >
    >
    >


    > > I'm having trouble using the Sort property of a recordset to sort my data
    > by
    > > a field who's name contains "[" and "]". This field name is generated when
    > I
    > > load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
    > > don't think I have controlover the characters in the name.
    > >
    > > How do I sort by this field?
    > >
    > > When I put:
    > >
    > > rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
    > > or
    > > rs.sort= 1 '(The first field)
    > > or
    > > rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"
    > >
    > > I get this error:
    > >  ADODB.Recordset error '800a0bb9'
    > > Arguments are of the wrong type, are out of acceptable range, or are in
    > > conflict with one another.
    > >
    > > I am using a client-side cursor.
    > > I've tried using about every comgination of CursorType and LockType.
    > >
    > > Can someone help me? Seems like this should be easy enough to do; maybe
    > > there is an escape character I can use instead of [ or ] in the field
    > > name???
    > >
    > > Thank you.
    > >
    > > Chet
    > >
    > >
    >
    >

 
 
 

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by Jerry II » Sat, 24 May 2003 01:42:16


The correct quoting is to double up closing brackets. In your case it should be "[[Agent]]].[[Agent]]].[[MEMBER_CAPTION]]]". It looks though it would be a lot easier (and faster) to receive the recordset already sorted.

Jerry

  Tried that, no luck. :) I wonder if there's some sort of escape code I should use to represent the field name???

    I think square brackets are special characters...used to enclose a field name that contains another special character.  So, just a guess, but how about in your sort code if you enclose the table/field names with an additional set of square brackets.  Like so:

    rs.sort="[[Agent]].[[Agent]].[[MEMBER_CAPTION]]"

    Just a thought.

    Good luck,
    David


      Here's a little more info:

      rs.Sort="MEMBER_CAPTION" returns the same error:

      "ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. "
      Here are the results of the following code to get all the field names:

      for each fld in rs.fields: debug.print fld.name :next fld

      [Agent].[City].[MEMBER_CAPTION]
      [Agent].[Agent].[MEMBER_CAPTION]
      [Measures].[Coverage].[Year].[All].[2003]
      [Measures].[Upsell].[Year].[All].[2003]
      [Measures].[Fuel].[Year].[All].[2003]
      [Measures].[AddDrv].[Year].[All].[2003]
      [Measures].[Other].[Year].[All].[2003]
      [Measures].[TTL Revenue].[Year].[All].[2003]
      [Measures].[Days].[Year].[All].[2003]
      [Measures].[Yield].[Year].[All].[2003]
      [Measures].[Ratio].[Year].[All].[2003]
      [Measures].[Commission].[Year].[All].[2003]

      I want to sort by [Agent].[Agent].[MEMBER_CAPTION]; the MDX statement sorts it by [Agent].[City].[MEMBER_CAPTION] and then [Agent].[Agent].[MEMBER_CAPTION] since they represent different levels in the same dimension.


      > Hi Chet,
      >
      > I have replied on your previous posting, but looks like all my replies for
      > that day disappeared somehow. Try to use just field name, without database
      > and owner names. Something like
      >
      > rs.sort="MEMBER_CAPTION"
      >
      > If it does not help, then check what is a field name recordset shows for
      > that MEMBER_CAPTION field and post it here
      >
      > --
      > Val Mazur
      > Microsoft MVP
      >
      >
      >


      > > I'm having trouble using the Sort property of a recordset to sort my data
      > by
      > > a field who's name contains "[" and "]". This field name is generated when
      > I
      > > load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
      > > don't think I have controlover the characters in the name.
      > >
      > > How do I sort by this field?
      > >
      > > When I put:
      > >
      > > rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
      > > or
      > > rs.sort= 1 '(The first field)
      > > or
      > > rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"
      > >
      > > I get this error:
      > >  ADODB.Recordset error '800a0bb9'
      > > Arguments are of the wrong type, are out of acceptable range, or are in
      > > conflict with one another.
      > >
      > > I am using a client-side cursor.
      > > I've tried using about every comgination of CursorType and LockType.
      > >
      > > Can someone help me? Seems like this should be easy enough to do; maybe
      > > there is an escape character I can use instead of [ or ] in the field
      > > name???
      > >
      > > Thank you.
      > >
      > > Chet
      > >
      > >
      >
      >

 
 
 

MDX->Recordset: Sorting a field with "[" and "]" in the field name

Post by Chet Crome » Sat, 24 May 2003 04:50:30


If I could figure out how to get the MDX Order() function to sort it by the X level of the ROWS dimension instead of only by something on the COLUMNS dimension, I'd do that. But alas, I have not found that yet, either, and if I can get it done this way, it does have some clear benefits to my application.

Your idea didn't work for me, though. I still get the following error:

ADODB.Recordset error '800a0bb9' :Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I tried:
rs.Sort="[[Agent]]].[[Agent]]].[[MEMBER_CAPTION]]]"
rs.Sort="[[[Agent]]].[[Agent]]].[[MEMBER_CAPTION]]]]"
rs.Sort="[Agent]].[Agent]].[MEMBER_CAPTION]]"
rs.Sort="[[Agent]].[Agent]].[MEMBER_CAPTION]]]"

I'm beginning to wonder if I'm not able to sort a recordset returned from an MDX statement ran against a cube?????

Keep ideas coming, if you've got any! Thanks for the ideas.

Chet


  The correct quoting is to double up closing brackets. In your case it should be "[[Agent]]].[[Agent]]].[[MEMBER_CAPTION]]]". It looks though it would be a lot easier (and faster) to receive the recordset already sorted.

  Jerry

    Tried that, no luck. :) I wonder if there's some sort of escape code I should use to represent the field name???

      I think square brackets are special characters...used to enclose a field name that contains another special character.  So, just a guess, but how about in your sort code if you enclose the table/field names with an additional set of square brackets.  Like so:

      rs.sort="[[Agent]].[[Agent]].[[MEMBER_CAPTION]]"

      Just a thought.

      Good luck,
      David


        Here's a little more info:

        rs.Sort="MEMBER_CAPTION" returns the same error:

        "ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. "
        Here are the results of the following code to get all the field names:

        for each fld in rs.fields: debug.print fld.name :next fld

        [Agent].[City].[MEMBER_CAPTION]
        [Agent].[Agent].[MEMBER_CAPTION]
        [Measures].[Coverage].[Year].[All].[2003]
        [Measures].[Upsell].[Year].[All].[2003]
        [Measures].[Fuel].[Year].[All].[2003]
        [Measures].[AddDrv].[Year].[All].[2003]
        [Measures].[Other].[Year].[All].[2003]
        [Measures].[TTL Revenue].[Year].[All].[2003]
        [Measures].[Days].[Year].[All].[2003]
        [Measures].[Yield].[Year].[All].[2003]
        [Measures].[Ratio].[Year].[All].[2003]
        [Measures].[Commission].[Year].[All].[2003]

        I want to sort by [Agent].[Agent].[MEMBER_CAPTION]; the MDX statement sorts it by [Agent].[City].[MEMBER_CAPTION] and then [Agent].[Agent].[MEMBER_CAPTION] since they represent different levels in the same dimension.


        > Hi Chet,
        >
        > I have replied on your previous posting, but looks like all my replies for
        > that day disappeared somehow. Try to use just field name, without database
        > and owner names. Something like
        >
        > rs.sort="MEMBER_CAPTION"
        >
        > If it does not help, then check what is a field name recordset shows for
        > that MEMBER_CAPTION field and post it here
        >
        > --
        > Val Mazur
        > Microsoft MVP
        >
        >
        >


        > > I'm having trouble using the Sort property of a recordset to sort my data
        > by
        > > a field who's name contains "[" and "]". This field name is generated when
        > I
        > > load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
        > > don't think I have controlover the characters in the name.
        > >
        > > How do I sort by this field?
        > >
        > > When I put:
        > >
        > > rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
        > > or
        > > rs.sort= 1 '(The first field)
        > > or
        > > rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"
        > >
        > > I get this error:
        > >  ADODB.Recordset error '800a0bb9'
        > > Arguments are of the wrong type, are out of acceptable range, or are in
        > > conflict with one another.
        > >
        > > I am using a client-side cursor.
        > > I've tried using about every comgination of CursorType and LockType.
        > >
        > > Can someone help me? Seems like this should be easy enough to do; maybe
        > > there is an escape character I can use instead of [ or ] in the field
        > > name???
        > >
        > > Thank you.
        > >
        > > Chet
        > >
        > >
        >
        >

 
 
 

1. MDX->Recordset: Sorting a field with "[" and "]" in the field name

I'm having trouble using the Sort property of a recordset to sort my data by
a field who's name contains "[" and "]". This field name is generated when I
load an MDX query (ADOMD OLAP data source) in to an ADODB recordset, so I
don't think I have controlover the characters in the name.

How do I sort by this field?

When I put:

rs.sort="[Agent].[Agent].[MEMBER_CAPTION]"
or
rs.sort= 1 '(The first field)
or
rs.sort="[[Agent].[Agent].[MEMBER_CAPTION]]"

I get this error:
 ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

I am using a client-side cursor.
I've tried using about every comgination of CursorType and LockType.

Can someone help me? Seems like this should be easy enough to do; maybe
there is an escape character I can use instead of [ or ] in the field
name???

Thank you.

Chet

2. System Exception on D3NT

3. Help inserting records on multi-table form

4. How to make a "decimal"-field to an "integer"-field

5. Start an e-commerce with SQLServer7

6. max of ("...","...","..")

7. aReport."Field".SetFilter("this")?

8. Record.open "", "URL=..."

9. The+instruction+at+"0x1f90db9b"+referenced+memory+at+"0x7fa03794"+The+memory+cou

10. "."and ","