array/recordset?

R

Rob Meade

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
 
B

Bob Barrows

Rob said:
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
 
R

Rob Meade

...

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
 
B

Bob Barrows

Rob said:
...

Hey ho Bob :eek:)


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

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/[email protected]&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
 
R

Rob Meade

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

Rob Meade

...
http://groups.google.com/[email protected]&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
 
B

Bob Barrows

Rob said:
...

http://groups.google.com/[email protected]&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 ...
 
R

Rob Meade

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

Bob Barrows

Rob said:
...


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

Rob Meade

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

Rob Meade

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

Bob Barrows

Rob said:
...

http://groups.google.com/[email protected]&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
 
B

Bob Barrows

Rob said:
...


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

Rob Meade

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top