Hi
Is there a simple way of inserting a new record into the middle of an existing recordset?

I discovered that the objRS.addNew jumps to the bottom line, adding the new record at the end of the recordset, from where ever the current cursor position is.



I'm on my first larger web-site creation (still in my spare time and in fun/friends purpose dough.) with a log-on user system, all coded in ASP / ADO - connected to an Access DB.

I created a menu system that is displaying only the links relevant to a certain user logged on. I gathered all the Links in a DB "Menue"-table. I'm just about creating an admin tool for adding new Links to the Menue through a simple HTML-form interface.

I wanted to be able of updating existing links and their sources, adding new and erasing old no-longer-wanted links.
For these tasks I made three SubRoutines. Then sub createLink were ment to add a new Link to a given position in the Menue.

I set the cursor to a - by the form submitted - given position in Menue recordset and then used the .addNew of the recordset object. This did ad a new record only it was created at the end of the recordset.

Here's the Sub:


Sub createLink
On Error Resume Next
Dim rsMenue
Set rsMenue = Server.CreateObject("ADODB.Recordset")
rsMenue.Open "Menue", strConnect, adOpenDynamic, adLockOptimistic, adCmdTable

rsMenue.Move lngSelectedPos-2
rsMenue.AddNew
rsMenue("HyperLink") = "NEW LINK"
rsMenue.Update

If Err.Number <> 0 Then
     strResponse = Error"
Else
     strResponse = "Success"
End If

rsMenue.Close
Set rsMenue = Nothing
End Sub


Do I have to copy all records from the given position and down to bottom line - to to a temporary array, deleting the same records in the DB, adding the new record, and lastly paste the array-held records back to the Menue as new records?

Thanks everyone for a great forum!
/ Paw

Well let me tell you what I understood with your question.

You want the user to add a new link which is positioned in some place out of earlier existing links. Now, if the user is just editing the existing record, it wont effect the position since it would place the link at the same position. But if it is a new link being placed in the list, it would add it to the last next recordset and would place it in the end. may be you can query the display according to the date of edition or addition of the links if at all there is a date field in the table.

Let me know if I am clear

Regards

sinapra

Well, the Menue of the homepage is meant to be a graphical mirror of how all link-records are arranged in the DB table.


Table:
HyperLink | Source

start 123.asp
forum forum.asp
admin administration.asp
contact me info.asp


Start is supposed to show at the top of the Menue
and lastly the contact information.

Now I want to add a new link between the forum- and admin- links.
The new "How to use forum"-link should be next after the forum-link.

But! The .addNew method will only create the new record at the end/bottom of the table..

Then you may want to add one more column to that table that will decide the order of the links to be displayed. This way when any new link to be added inbetween, this column would help you do that.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Adding an extra column would solve the problem to get it all sorted by numbers, but since this would include updating a load of records anyway and needs to be sorted out everytime the leftside menue of the page is displayed (that would be quite a lot of sorting in just a minute)...

I just finished somewhat of a solution to the problem.
It does the job.


Sub createLink
   On Error Resume Next
   Dim rsMenue
   Set rsMenue = Server.CreateObject("ADODB.Recordset")
   rsMenue.Open "Menue", strConnZite, adOpenStatic, adLockPessimistic, adCmdTable

   If (lngSelectedPos) > rsMenue.RecordCount Then
      lngSelectedPos = rsMenue.RecordCount +1
      rsMenue.MoveLast
      rsMenue.AddNew
      rsMenue("HyperLink") = "NEW LINK"
   Else
     Dim arrFeilds, varBookmark, fld, intRow, intLastRow, intCol, intLastCol
      rsMenue.Move lngSelectedPos -1
      varBookmark = rsMenue.Bookmark

      arrFeilds = rsMenue.GetRows (adGetRowsRest, adBookmarkCurrent)
      intLastCol = UBound(arrFeilds, 1)
      intLastRow = UBound(arrFeilds, 2)

      rsMenue.Bookmark = varBookmark
      rsMenue("MenueType") = 0
      rsMenue("MenueQual") = 0
      rsMenue("HyperLink") = "NEW LINK"
      rsMenue("Source") = Empty
      rsMenue.Update
      rsMenue.MoveNext

      If rsMenue.EOF <> True Then
      For intRow = 0 To intLastRow -1
         intCol = 0
         For Each fld in rsMenue.Fields
            rsMenue(fld.Name) = arrFeilds(intCol, intRow)
            intCol = intCol + 1
         Next
         rsMenue.MoveNext
      Next
      End If

      rsMenue.AddNew
      intCol = 0
      For Each fld in rsMenue.Fields
         rsMenue(fld.Name) = arrFeilds(intCol, intLastRow)
         intCol = intCol + 1
      Next
   End If
   rsMenue.Update

   If Err.Number <> 0 Then
      strResponse = "Unknown <b>error</b>!"
   Else
      strResponse = "New link added"
   End If

   rsMenue.Close
   Set rsMenue = Nothing
End Sub

Does this resolve? Looks like you are adding more rows without flushing out the existing.

_________________________
- Vijay G
Strive for Perfection