Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Updating/Sorting a record set

Posted on 2002-04-16
23
268 Views
Last Modified: 2009-07-29
Hi all

I need a recordset sorted  but I can seem to order the recordset in SQL as I am updating the recordset in ASp...

I am opening the recordset in the following way..

Query = "SELECT * FROM " & t7

Query3 = "SELECT DISTINCT CLUSTER FROM " & t7 & " order bY CLUSTER"



rst_SSCTEMP1.Open Query, cnnedit,adOpenKeyset
rst_ssctemp1.Sort = "div"
'If server connection was successful
If cnnedit.State <> adStateClosed Then

                   
                         'Update SSCTEMP for all SSCNO AND CLUSTER CHANGES
                         rst_SSCTEMP1.Movefirst
                         for int_Count1 = 1 to Count-1
                         Response.Write int_Count1
                         Response.Write (Request.Form("SSCID_" & int_Count1))
                         if Request.Form("SSCID_" & int_Count1) <> "" then
                         if Request.Form("CLUSTER_" & int_Count1) <> "" then
                         if Request.Form("DPMID_" & int_Count1) <> "" then
                         if Request.Form("HRID_" & int_Count1) <> "" then
                         if Request.Form("DDNAME_" & int_Count1) <> "" then
                         if Request.Form("SMDNAME_" & int_Count1) <> "" then
                         if Request.Form("CBID_" & int_Count1) <> "" then
                         rst_SSCTEMP1.Fields("SSCID") = Request.Form("SSCID_" & int_Count1)
                         rst_SSCTEMP1.Fields("HRDirectorID") = Request.Form("HRID_" & int_Count1)
                         rst_SSCTEMP1.Fields("CLUSTER") = Request.Form("CLUSTER_" & int_Count1)
                         rst_SSCTEMP1.Fields("DDNAME") = Request.Form("DDNAME_" & int_Count1)
                         rst_SSCTEMP1.Fields("SMDNAME") = Request.Form("SMDNAME_" & int_Count1)
                         rst_SSCTEMP1.Fields("CBMGRID") = Request.Form("CBID_" & int_Count1)                                        
                         rst_SSCTEMP1.Fields("DPMID") = Request.Form("DPMID_" & int_Count1)
                         rst_SSCTEMP1.Update


this gives me the following error

ADODB.Recordset error '800a0cb3'

Object or provider is not capable of performing requested operation.

/Bonus/Personnel/nom/population/STRUCTURE_UPDATE.asp, line 135


help
0
Comment
Question by:demmick
  • 8
  • 5
  • 4
  • +4
23 Comments
 
LVL 20

Expert Comment

by:jitganguly
ID: 6945302
Instead of adOpenkeyset use adOpenDynamic

This line from
rst_SSCTEMP1.Open Query, cnnedit,adOpenKeyset
change to
rst_SSCTEMP1.Open Query, cnnedit,adOpenDynamic
0
 

Author Comment

by:demmick
ID: 6945360
That doesn't work...
0
 
LVL 11

Accepted Solution

by:
ASPGuru earned 25 total points
ID: 6945377
neither keyset nor dynamic are recommended for web applications. this is overkill...

you also don't need the moveFirst to insert data in a table.... actually you don't even need to return the while table data in a recordset...

use this code instead:

function InsertRS(conn,table,fields,values)
     dim rs
     Set rs = Server.CreateObject("ADODB.Recordset")
     rs.CursorType = 3 'adOpenStatic
     rs.LockType = 3 'adLockOptimistic
     rs.open "SELECT * FROM " & table & " WHERE 0=1", conn
     rs.AddNew fields, values
     rs.close
     Set rs = Nothing
end function

insertRS "yourconnectionString", "tablename", Array("field1", "field2",....), Array("val1",2,...)

ASPGuru
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 11

Expert Comment

by:ASPGuru
ID: 6945380
typo
while=whole
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6945382
What line is 135?

Fritz the Blank
0
 

Author Comment

by:demmick
ID: 6945387
At the mo line 135 is    
rst_ssctemp1.Sort = "div"

0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6945412
you probably cannot sort clientside with a serverside cursor.....

but why sorting at all, when you just want to insert data...

and why don't you sort with the "ORDER BY" clause in the sql statement if you want to sort...

ASPGuru
0
 

Author Comment

by:demmick
ID: 6945423
I am just fixing this piece of code so they way it has been written is not 100% correct.  But the way the records are updated in this is that on the previeous pages the users update informatiuon in a table.  when the table is drawn the information is sorted by "Div" and each obf the boxes drawn have the record number attached as a name.  Now when they records are updated in the second page the recordset need to be opened sorted by div and we go through each field and update the corresponding field.  I have tried to take away the sort function when the records are drawn on the previous page and then the pages work fine(as I remove the sort from the second page).

I hope this helps!!
0
 

Author Comment

by:demmick
ID: 6945428
ASP Guru

I have tried to put the order by clause in but it still gives me the same error.
0
 

Author Comment

by:demmick
ID: 6945437
Well I tell a lie not quite the same error....  

it gives me the following error

ADODB.Recordset error '800a0cb3'

Object or provider is not capable of performing requested operation.

/bonus/personnel/nom/population/STRUCTURE_UPDATE.asp, line 150


Line 150 is...... rst_SSCTEMP1.Fields("SSCID") = Request.Form("SSCID_" & int_Count1)


0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6945441
isn't SSCID an autonumber field?

ASPGuru
0
 

Author Comment

by:demmick
ID: 6945449
Well I tell a lie not quite the same error....  

it gives me the following error

ADODB.Recordset error '800a0cb3'

Object or provider is not capable of performing requested operation.

/bonus/personnel/nom/population/STRUCTURE_UPDATE.asp, line 150


Line 150 is...... rst_SSCTEMP1.Fields("SSCID") = Request.Form("SSCID_" & int_Count1)


0
 

Author Comment

by:demmick
ID: 6945454
No its not.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6945461
What sort of field is SSCID? Text or numeric? I am wondering if the code is trying to concactinate text and numeric data, and as a result, when you place it in the field, you might be getting some sort of data mismatch.

Fritz the Blank
0
 

Author Comment

by:demmick
ID: 6945472
its all test.  I will try and I will try on convert the data.  But the code falls down when I sort the data.  It doesn't seem to have anything to do with the type of data I am trying to force in there.  I think I need to change the way the recordset is opening.
0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6945496
<unsubscribing>
0
 
LVL 1

Expert Comment

by:ParthaChoudhury
ID: 6945627
rst_ssctemp1.CursorLocation = aUseClient

- Partha
0
 
LVL 1

Expert Comment

by:ameriani
ID: 6945906
I read in http://microsoft.com and am wondering that maybe your error is due to the one mention in this article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q193352
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6974123
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if they are still open in 14 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20151272.html
http://www.experts-exchange.com/questions/Q.20267241.html
http://www.experts-exchange.com/questions/Q.20274988.html
http://www.experts-exchange.com/questions/Q.20278352.html
http://www.experts-exchange.com/questions/Q.20278832.html
http://www.experts-exchange.com/questions/Q.20281277.html
http://www.experts-exchange.com/questions/Q.20290097.html



*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @14 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6974145
A split here, maybe?

Fritz the Blank
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6974227
That sounds good to me, let's give demmick a few days to respond here and if nothing transpires, we'll process a point split.

Your response and help is very much appreciated.

Moondancer - EE Moderator
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6979041
demmick logged in 4/29, after this request was posted but did not respond so I am processing point splits now.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6979060
Points for fritz_the_blank -> http://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20295181
Poins for jitganguly  -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20295182
Points for ParthaChoudhury -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20295183
Points for ameriani -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20295185
Please comment in your respective questions to complete this.
Thanks,
Moondancer - EE Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Adding Datediff to staistics page 2 62
Voice recognition ASP or ASP.NET or JavaScript 2 70
Issues with Insert statement 12 29
html Uncheck Checkbox 2 25
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question