array/recordset?

Discussion in 'ASP General' started by Rob Meade, Feb 8, 2004.

  1. Rob Meade

    Rob Meade Guest

    Lo all,

    Ok - this is what I was aiming to do, and then I thought - naahhh, that cant
    be right!

    query database
    results to recordset
    results to array using GetRows
    update values in one column in array
    <BOING>
    realised you cant sort an array easily..."hmmm, perhaps if I put the array
    contents into a recordset I could sort them"

    <DUH>

    The above seems pretty stupid to me, ie, recordset, to array, to recordset -
    where finally I *will* actually need them in an array anyway....

    So, is it possible to update a recordset once its pulled from the database?
    It's probably a stupid question, but its not something I've ever done before
    so wasn't sure? If so, a small example would be handy...

    I'm basically calculating a field which needs to be stored back against the
    row whilst iterating through the whole lot.

    Any info appreciated.

    Regards

    Rob
    Rob Meade, Feb 8, 2004
    #1
    1. Advertising

  2. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > Lo all,
    >
    > Ok - this is what I was aiming to do, and then I thought - naahhh,
    > that cant be right!
    >
    > query database
    > results to recordset
    > results to array using GetRows
    > update values in one column in array
    > <BOING>
    > realised you cant sort an array easily..."hmmm, perhaps if I put the
    > array contents into a recordset I could sort them"
    >
    > <DUH>
    >
    > The above seems pretty stupid to me, ie, recordset, to array, to
    > recordset - where finally I *will* actually need them in an array
    > anyway....
    >
    > So, is it possible to update a recordset once its pulled from the
    > database? It's probably a stupid question, but its not something I've
    > ever done before so wasn't sure? If so, a small example would be
    > handy...
    >
    > I'm basically calculating a field which needs to be stored back
    > against the row whilst iterating through the whole lot.
    >
    > Any info appreciated.
    >
    > Regards
    >
    > Rob


    Why can't the calculation be done in the query used to open the recordset,
    then use the calculated column in the Order By clause? That would be the
    most efficient means of doing this.

    Alternatively:
    By opening the recordset using a LockType of adLockBatchOptimistic (4), and
    setting the ActiveConnection property to Nothing, you can disconnect a
    recordset from the database (you can even close and destroy the Connection
    at this point if you wish - it's usually a good idea unless you plan on
    using the connection later on in the page). This will allow you to update
    the recordset without affecting the data in the database. And once you have
    updated the data, you can use the recordset's Sort method to sort the
    results before using getrows to convert it to an array.

    Set rs=server.createobject("adodb.recordset")
    rs.cursorlocation = adUseClient
    rs.Open SQL, cn, ,adLockBatchOptimistic,adCmdText
    set rs.activeconnection=nothing
    cn.close:set cn=nothing

    or, if you don't have the constants defined:
    Set rs=server.createobject("adodb.recordset")
    rs.cursorlocation = 3
    rs.Open SQL, cn, ,4,1
    set rs.activeconnection=nothing
    cn.close:set cn=nothing

    You can now do your updates, using the Update method.

    If you wanted to save these changes back to the database, you would set the
    activeconnection property back to an open connection, and call UpdateBatch
    to send the changes to the database.


    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #2
    1. Advertising

  3. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    Hey ho Bob :eek:)

    > Why can't the calculation be done in the query used to open the recordset,
    > then use the calculated column in the Order By clause? That would be the
    > most efficient means of doing this.


    Indeed it would - but - I dont know how in a SQL statement I can count the
    total number of times each word form the search criteria is found in each
    field, and across the 3 cases of all words / any words / exact phrase -
    hence doing out of SQL and in ASP...

    > Alternatively:
    > By opening the recordset using a LockType of adLockBatchOptimistic (4),

    and
    > setting the ActiveConnection property to Nothing, you can disconnect a
    > recordset from the database (you can even close and destroy the Connection
    > at this point if you wish - it's usually a good idea unless you plan on
    > using the connection later on in the page). This will allow you to update
    > the recordset without affecting the data in the database.


    The column in question is what I like to call a fairy, it doesnt really
    exist (but I guess it kinda does) :eek:)

    ie, I select 4 real fields for example, and then I add one, like this :

    SELECT websiteid, websitename, websitedesc, websitemeta, 0 AS Relevance
    FROM....

    its the Relevance field that will be updated(hopefully) in the ASP, once
    each row is updated and sorted based on Relevance, I'll then drop it all
    into an array for display to the page etc..

    > And once you have updated the data, you can use the recordset's Sort

    method to sort the
    > results before using getrows to convert it to an array.


    yep - thats what I want :)

    I think my current connection string (used vastly across the entire site on
    different pages to do different stuff etc) is causing me my current error :

    Set objCommand2 = Server.CreateObject("ADODB.Command")
    Set RS2 = Server.CreateObject("ADODB.Recordset")
    objCommand2.CommandText = SQL2
    objCommand2.CommandType = adCmdText
    Set objCommand2.ActiveConnection = objConnection2
    RS2.Open objCommand2,,adOpenKeySet, adLockOptimistic

    Current error :

    Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
    Multiple-step OLE DB operation generated errors. Check each OLE DB status
    value, if available. No work was done.
    /parasolit/statics/mainbody-search-results.asp, line 631
    Is this my commands on the bottom line of the above that are causing the
    problems with the update? Is it safe to change these to something else
    without affecting the rest of the app? (maybe worth having a new connection
    string just for this etc)...
    Any more help appreciated,
    Regards
    Rob
    Rob Meade, Feb 8, 2004
    #3
  4. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    > Hey ho Bob :eek:)
    >
    >> Why can't the calculation be done in the query used to open the
    >> recordset, then use the calculated column in the Order By clause?
    >> That would be the most efficient means of doing this.

    >
    > Indeed it would - but - I dont know how in a SQL statement I can
    > count the total number of times each word form the search criteria is
    > found in each field, and across the 3 cases of all words / any words
    > / exact phrase - hence doing out of SQL and in ASP...


    If it was SQL Server, this would be do-able in a stored procedure using a
    temp table or table variable, but ... I think you're using Access ...?


    >
    >> Alternatively:
    >> By opening the recordset using a LockType of adLockBatchOptimistic
    >> (4), and setting the ActiveConnection property to Nothing, you can
    >> disconnect a recordset from the database (you can even close and
    >> destroy the Connection at this point if you wish - it's usually a
    >> good idea unless you plan on using the connection later on in the
    >> page). This will allow you to update the recordset without affecting
    >> the data in the database.

    >
    > The column in question is what I like to call a fairy, it doesnt
    > really exist (but I guess it kinda does) :eek:)
    >
    > ie, I select 4 real fields for example, and then I add one, like this
    > :
    >
    > SELECT websiteid, websitename, websitedesc, websitemeta, 0 AS
    > Relevance FROM....


    Calculated fields are not updatable. So that leaves the
    original-recordset-update solution out. This leaves three options.

    1. Use javascript in your server-side code. Sorting an array is a one-liner
    in javascript
    2. Use an ad hoc recordset
    3. Write code to sort the array

    Here's a link to a post I made back in July in response to Robb Meade (was
    that you?):
    http://groups.google.com/groups?q=d...m=&rnum=1


    Here's a link to another post I made in which I compared the performance of
    techniques 2 and 3:
    http://groups.google.com/groups?hl=...t&ie=UTF-8&oe=UTF-8&q=Ordering+FSO+output+%3F



    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #4
  5. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    > If it was SQL Server, this would be do-able in a stored procedure using a
    > temp table or table variable, but ... I think you're using Access ...?


    Nope - I am using SQL Server..

    I'd really like to post up what I have on this page at the moment, but its a
    bit big!

    > Calculated fields are not updatable. So that leaves the
    > original-recordset-update solution out.


    SHIT! I've just spent ages trying to work out why this kept failing,
    buggery bollox, wish I'd know that little bit of knowledge a while
    back....cheers though Bob - saves me spending the night beating the server
    with a stick!

    > This leaves three options.


    > 1. Use javascript in your server-side code. Sorting an array is a

    one-liner in javascript

    I'd rather not if I can do without it etc.

    > 2. Use an ad hoc recordset


    ad hoc recordset? Sounds interesting - whats one of them then?

    > 3. Write code to sort the array
    >
    > Here's a link to a post I made back in July in response to Robb Meade
    > (was that you?):


    indeed it was :eek:)

    Checking the links now...

    Cheers

    Rob
    Rob Meade, Feb 8, 2004
    #5
  6. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    >

    http://groups.google.com/groups?q=d...m=&rnum=1

    reading/trying the adhoc recordset etc...got a few errors, fixed the first
    (i think) with the inclusion of the adovbs.inc file.
    fixed the next by adding an equals sign in the rs.sort "data" line...
    another by adding "'s to the animals in the brackets (array etc)

    Have this now - but still causing an error

    <!--#Include File="_includescripts/adovbs.inc"-->
    <%
    dim ar, i , rs
    ar=array("dog","cat","bird","monkey")
    response.write "Before Sort:<BR>"
    for i = 0 to ubound(ar)
    response.write ar(i) & "<BR>"
    next

    Set rs=server.createobject("adodb.recordset")
    'again, use the datatype that makes sense for your data
    rs.fields.append "data",adVarChar,20
    rs.open
    for i = 0 to ubound(ar)
    rs.addnew "data", ar(i)
    next
    rs.sort = "data"
    i=0
    do until rs.eof
    ar(i) = rs(0)
    i=i+1
    loop
    rs.close
    set rs=nothing
    response.write "After Sort:<BR>"
    for i = 0 to ubound(ar)
    response.write ar(i) & "<BR>"
    next
    %>

    Any ideas?

    I'm obviously missing something....

    Cheers

    Rob
    Rob Meade, Feb 8, 2004
    #6
  7. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    >>

    >

    http://groups.google.com/groups?q=d...m=&rnum=1
    >
    > reading/trying the adhoc recordset etc...got a few errors, fixed the
    > first (i think) with the inclusion of the adovbs.inc file.
    > fixed the next by adding an equals sign in the rs.sort "data" line...
    > another by adding "'s to the animals in the brackets (array etc)
    >
    > Have this now - but still causing an error


    What error? I can't see your screen ...


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #7
  8. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    >>

    >

    http://groups.google.com/groups?q=d...m=&rnum=1
    >
    > reading/trying the adhoc recordset etc...got a few errors, fixed the
    > first (i think) with the inclusion of the adovbs.inc file.
    > fixed the next by adding an equals sign in the rs.sort "data" line...


    Wait a minute. Sort is a method, not a property. The "=" sign should not be
    there.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #8
  9. Rob Meade

    Bob Barrows Guest

    Oops - disregard the last about Sort being a method - brain fart here.


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #9
  10. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    > What error? I can't see your screen ...


    Soz, working in loft, getting very cold up here now :eek:/

    Microsoft VBScript runtime error '800a0009'
    Subscript out of range: '4'
    /parasolit/test.asp, line nn

    its the 2nd line of

    do until rs.eof
    ar(i) = rs(0)
    i=i+1
    loop
    Rob Meade, Feb 8, 2004
    #10
  11. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    >> What error? I can't see your screen ...

    >
    > Soz, working in loft, getting very cold up here now :eek:/
    >
    > Microsoft VBScript runtime error '800a0009'
    > Subscript out of range: '4'
    > /parasolit/test.asp, line nn
    >
    > its the 2nd line of
    >
    > do until rs.eof
    > ar(i) = rs(0)
    > i=i+1
    > loop



    hmm. The other link contains tested code. You should probably look at that.
    In the meantime, I'll debug this air code to see what's wrong ...
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #11
  12. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    > Oops - disregard the last about Sort being a method - brain fart here.


    lol - ok - it didnt work without it anyway :)

    Oh, and I'm trying to use what you have there as a basis for what I'm trying
    to do - I've check the adovbs.inc file and I dont see an obvious replacement
    for my SQL Server TEXT data type? Is it adLongVarChar - ie, loads of text is
    fine?

    Regards

    Rob
    Rob Meade, Feb 8, 2004
    #12
  13. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    >>

    >

    http://groups.google.com/groups?q=d...m=&rnum=1
    >
    > reading/trying the adhoc recordset etc...got a few errors, fixed the
    > first (i think) with the inclusion of the adovbs.inc file.


    Instead of adovbs.inc, see this: http://www.aspfaq.com/show.asp?id=2112

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #13
  14. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    > hmm. The other link contains tested code. You should probably look at

    that.
    > In the meantime, I'll debug this air code to see what's wrong ...


    I changed a bit of it myself, to remove the bottom section (from i=0) and
    just added a do while not rs.eof - response.write the values etc, and out
    they popped...

    I'm guessing the next bit is to dump them back into an array? Can one not
    use getRows() there etc?
    Rob Meade, Feb 8, 2004
    #14
  15. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    > Instead of adovbs.inc, see this: http://www.aspfaq.com/show.asp?id=2112


    Will check in a bit, think its having a bad day :eek:) I got "There was an
    error" at the top..

    Rob
    Rob Meade, Feb 8, 2004
    #15
  16. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    >> Oops - disregard the last about Sort being a method - brain fart
    >> here.

    >
    > lol - ok - it didnt work without it anyway :)
    >
    > Oh, and I'm trying to use what you have there as a basis for what I'm
    > trying to do - I've check the adovbs.inc file and I dont see an
    > obvious replacement for my SQL Server TEXT data type? Is it
    > adLongVarChar - ie, loads of text is fine?


    See http://www.able-consulting.com/ADODataTypeEnum.htm for a handy datatype
    mapping chart.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #16
  17. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    >>

    >

    http://groups.google.com/groups?q=d...m=&rnum=1
    >
    > reading/trying the adhoc recordset etc...got a few errors, fixed the
    > first (i think) with the inclusion of the adovbs.inc file.
    > fixed the next by adding an equals sign in the rs.sort "data" line...
    > another by adding "'s to the animals in the brackets (array etc)
    >
    > Have this now - but still causing an error


    OK - this code is tested and works:
    <%
    dim ar, i , rs
    ar=array("dog","cat","bird","monkey")
    response.write "Before Sort:<BR>"
    for i = 0 to ubound(ar)
    response.write ar(i) & "<BR>"
    next

    Set rs=server.createobject("adodb.recordset")
    'again, use the datatype that makes sense for your data
    rs.fields.append "data",adVarChar,20
    rs.open
    for i = 0 to ubound(ar)
    rs.addnew "data", ar(i)
    next
    rs.sort = "data"
    i=0
    rs.MoveFirst
    for i = 0 to ubound(ar)
    ar(i) = rs(0)
    if not rs.eof then rs.MoveNext
    next
    rs.close
    set rs=nothing
    response.write "After Sort:<BR>"
    for i = 0 to ubound(ar)
    response.write ar(i) & "<BR>"
    next
    %>

    HTH,
    Bob Barrows


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #17
  18. Rob Meade

    Bob Barrows Guest

    Rob Meade wrote:
    > "Bob Barrows" wrote ...
    >
    >> hmm. The other link contains tested code. You should probably look
    >> at that. In the meantime, I'll debug this air code to see what's
    >> wrong ...

    >
    > I changed a bit of it myself, to remove the bottom section (from i=0)
    > and just added a do while not rs.eof - response.write the values etc,
    > and out they popped...
    >
    > I'm guessing the next bit is to dump them back into an array? Can one
    > not use getRows() there etc?


    No reason why not.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #18
  19. Rob Meade

    Rob Meade Guest

    Rob Meade, Feb 8, 2004
    #19
  20. Rob Meade

    Rob Meade Guest

    "Bob Barrows" wrote ...

    > OK - this code is tested and works:


    Plopped that on and yep running a treat, many thanks...ASPFAQ wouldnt fire
    up from that link, although when I browsed to it and entered 2112 as search
    criteria the article popped up, using that now - thanks again - oh, and the
    link you posted and that of the article were the same - odd...

    So, using that as an example I know have the following :

    Set testRS = Server.CreateObject("ADODB.RecordSet")
    testRS.Fields.Append "WebsiteID", adInteger
    testRS.Fields.Append "WebsiteName", adVarchar, 20
    testRS.Fields.Append "WebsiteDesc", adVarchar, 255
    testRS.Fields.Append "WebsiteMetaKeywords", adText
    testRS.Fields.Append "WebsiteMetaDescription", adVarchar255
    testRS.Fields.Append "Relevance", adInteger

    So I guess now I just iterate through my original database recordset,
    populate this one, do what I need to do with the relevance field, sort the
    new recordset, and then fire it out to an array.

    Sound about right?

    Rob
    Rob Meade, Feb 8, 2004
    #20
    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. Hung Huynh
    Replies:
    8
    Views:
    290
    Bob Barrows
    Sep 24, 2003
  2. Dana
    Replies:
    3
    Views:
    356
    Bob [BVP]
    Feb 26, 2004
  3. Tim Pollard
    Replies:
    8
    Views:
    143
    Ray Costanzo [MVP]
    Dec 3, 2004
  4. Guest
    Replies:
    2
    Views:
    101
    Guest
    Apr 14, 2005
  5. Dana
    Replies:
    2
    Views:
    182
    Martin Walke
    Feb 25, 2004
Loading...

Share This Page