Recordset Question

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

  1. Rob Meade

    Rob Meade Guest

    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, Feb 9, 2004
    #1
    1. Advertising

  2. Rob Meade

    Rob Meade Guest

    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
     
    Rob Meade, Feb 9, 2004
    #2
    1. Advertising

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

    --
    Manohar Kamath
    Editor, .netWire
    www.dotnetwire.com


    "Rob Meade" <> wrote in message
    news:IbTVb.605$D%...
    > 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
    >
    >
    >
    >
     
    Manohar Kamath [MVP], Feb 9, 2004
    #3
  4. Aaron Bertrand - MVP, Feb 9, 2004
    #4
  5. Of course, I didn't read it all... why don't you use an array or a
    dictionary instead of a recordset?

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "Rob Meade" <> wrote in message
    news:IbTVb.605$D%...
    > 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
    >
    >
    >
    >
     
    Aaron Bertrand - MVP, Feb 9, 2004
    #5
  6. Rob Meade

    Rob Meade Guest

    "Aaron Bertrand - MVP" wrote ...

    > 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
     
    Rob Meade, Feb 10, 2004
    #6
    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. Vikas Yadav
    Replies:
    0
    Views:
    1,158
    Vikas Yadav
    Sep 9, 2003
  2. Gian Paolo Clarici
    Replies:
    0
    Views:
    827
    Gian Paolo Clarici
    Jul 21, 2004
  3. Hung Huynh
    Replies:
    8
    Views:
    349
    Bob Barrows
    Sep 24, 2003
  4. Maria Kovacs

    Recordset Display Question

    Maria Kovacs, Jan 27, 2004, in forum: ASP General
    Replies:
    8
    Views:
    153
    Bob Lehmann
    Jan 29, 2004
  5. Rob Meade

    Another recordset question

    Rob Meade, Mar 1, 2004, in forum: ASP General
    Replies:
    2
    Views:
    113
    Rob Meade
    Mar 1, 2004
Loading...

Share This Page