Recordset Question

[Rob Meade]

evenin'

k - I've got a recordset that I'm dumping another recordset into - because
the source comes from a database that I cant update (probably makes more
sense to me that bit) - anyway, I need to update the new recordset which I
can do...I think however that I have missed something, as later in my code
it only seems to iterate once, and there should be at least 2 rows in the
recordset...

Can anyone confirm that when I use something like this :

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 "WebsiteURL", adVarchar, 255
testRS.Fields.Append "WebsiteMetaKeywords", adLongVarChar, 2147483647
testRS.Fields.Append "WebsiteMetaDescription", adVarchar, 255
testRS.Fields.Append "Relevance", adInteger

I do this initially before using addNew etc

Then for each row that I want to add I'd use something like this :

testRS.AddNew

testRS("WebsiteID") = RS2("WebsiteID")
testRS("WebsiteName") = RS2("WebsiteName")
testRS("WebsiteDesc") = RS2("WebsiteDesc")
testRS("WebsiteURL") = RS2("WebsiteURL")
testRS("WebsiteMetaKeywords") = RS2("WebsiteMetaKeywords")
testRS("WebsiteMetaDescription") = RS2("WebsiteMetaDescription")
testRS("Relevance") = RS2("Relevance")

testRS.Update

(RS2 in the above is the source recordset incidentally - I have a do while
not rs2.eof around this)...

Just seeking some confirmation that i dont need to do the append fields bit
for each iteration...

Cheers

Rob

 

[Rob Meade]

soz, resolved this one now, it seems i needed to put a testRS.MoveFirst
after adding the rows to get back to the start...

Rob

 

[Manohar Kamath [MVP]]

You need to create fields only once, but you need to use .AddNew everytime
you want to add a new record, and do an Update once you are done.

I am not sure which recordset should have two records, but you can do a
recordset.filter = vbNullString to reset the cursor back to the first
record. I am guessing the cursor is at the second record when it begins to
iterate.

 

[Aaron Bertrand - MVP]

testRS.AddNew

UGH!

Use an INSERT statement.

 

[Aaron Bertrand - MVP]

Of course, I didn't read it all... why don't you use an array or a
dictionary instead of a recordset?

 

[Rob Meade]

...

Of course, I didn't read it all... why don't you use an array or a
dictionary instead of a recordset?

Lo Aaron,

I wouldnt normally use the AddNew/Update stuff, haven't used it in ages, but
found I needed to here, with regards to the array, I needed to be able to
create a recordset and add a 'phantom' field, then after updating this field
for each record be able to sort it into ascending order (the relevance stuff
for the search results) - once sorted its then dumped to the page.

I looked at sorted the arrays but it seemed to all come back to using a
recordset anyway, so I've gone with that for now...

I guess I could have probably used a temporary table in SQL Server maybe,
but I've not used them before and the site this is for is quite a busy one,
therefore I didnt think that creating a multitude of temp tables was a good
idea, I could of course be wrong as I say, I've not done/tried that before
to know.

Regards

Rob