array/recordset?

[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

[Bob Barrows]

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"

[Rob Meade]

"Bob Barrows" wrote ...

Hey ho Bob :o)

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) :o)

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

[Bob Barrows]

Rob Meade wrote:

"Bob Barrows" wrote ...

Hey ho Bob :o)

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) :o)

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=do...phx.gbl&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=e...output%2B%253F

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"

[Rob Meade]

"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 :o)

Checking the links now...

Cheers

Rob

[Rob Meade]

"Bob Barrows" wrote ...

http://groups.google.com/groups?q=do...phx.gbl&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

[Bob Barrows]

Rob Meade wrote:

"Bob Barrows" wrote ...

http://groups.google.com/groups?q=do...phx.gbl&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]

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"

[Rob Meade]

"Bob Barrows" wrote ...

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

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

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

[Bob Barrows]

Rob Meade wrote:

"Bob Barrows" wrote ...

http://groups.google.com/groups?q=do...phx.gbl&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"

[Rob Meade]

"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

[Bob Barrows]

Rob Meade wrote:

"Bob Barrows" wrote ...

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

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

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]

Rob Meade wrote:

"Bob Barrows" wrote ...

http://groups.google.com/groups?q=do...phx.gbl&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"

[Rob Meade]

"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]

"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 :o) I got "There was an
error" at the top..

Rob

[Bob Barrows]

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]

Rob Meade wrote:

"Bob Barrows" wrote ...

http://groups.google.com/groups?q=do...phx.gbl&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]

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"

[Rob Meade]

"Bob Barrows" wrote ...

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

Fantastic link Bob - thanks :o)

Rob

[Rob Meade]

"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]

"Bob Barrows" wrote ...

No reason why not.

cool :)

[Rob Meade]

"Rob Meade" wrote ...

testRS.Fields.Append "WebsiteMetaKeywords", adText

that should have read :

testRS.Fields.Append "WebsiteMetaKeywords", adLongVarChar, nnn

with regards to the nnn - does it matter what size i use here? For example,
I know the sizes of my varchar fields from my sql server database, but I do
not know exactly how many characters will be here, so would me putting the
maximum be a stupid thing to do or acceptable under the circumstances?

Cheers

Rob

[Bob Barrows]

Rob Meade wrote:

"Rob Meade" wrote ...

testRS.Fields.Append "WebsiteMetaKeywords", adText

that should have read :

testRS.Fields.Append "WebsiteMetaKeywords", adLongVarChar, nnn

with regards to the nnn - does it matter what size i use here?

No. The DefinedSize argument should be ignored for this datatype, since it's
a variable length datatype. I don't think you even have to supply it.

Do you have access to the ADO online help? The answers to many of your
questions can be found there:
http://msdn.microsoft.com/library/en...ireference.asp

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"

[Rob Meade]

"Bob Barrows" wrote ...

No. The DefinedSize argument should be ignored for this datatype, since it's a variable length datatype. I don't think you even have to supply it.
Thats what I was hoping, but it errors out when I dont.. :o/
Do you have access to the ADO online help? The answers to many of your
questions can be found there:

http://msdn.microsoft.com/library/en...ireference.asp

Thanks once again Bob for the link and info :o)

I've just managed to get my initial recordset into the new recordset, do
what I had to do with the relevance, and then dump it to the page - bloody
hell! Needs a bit of work still, and only working for 'any words' at the
moment - as my brain appears to have turned to slush - but getting there...

Can't guarantee I'm done with this thread yet (bet you can! :D) - but I just
wanted to say a big thank you for all of the help you've given me this
afternoon/evening - its very much appreciated.

With regards to the library file I'll be trying to use that in future, I
assume it contains all of the info needed that I was using before, so can
just dump my adovbs.inc's and use that instead...

Thanks again,

Rob