Solved

Updating/Sorting a record set

Posted on 2002-04-16
23
252 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
Comment Utility
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
Comment Utility
That doesn't work...
0
 
LVL 11

Accepted Solution

by:
ASPGuru earned 25 total points
Comment Utility
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
 
LVL 11

Expert Comment

by:ASPGuru
Comment Utility
typo
while=whole
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
What line is 135?

Fritz the Blank
0
 

Author Comment

by:demmick
Comment Utility
At the mo line 135 is    
rst_ssctemp1.Sort = "div"

0
 
LVL 11

Expert Comment

by:ASPGuru
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
isn't SSCID an autonumber field?

ASPGuru
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:demmick
Comment Utility
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
Comment Utility
No its not.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
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
Comment Utility
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
Comment Utility
<unsubscribing>
0
 
LVL 1

Expert Comment

by:ParthaChoudhury
Comment Utility
rst_ssctemp1.CursorLocation = aUseClient

- Partha
0
 
LVL 1

Expert Comment

by:ameriani
Comment Utility
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
Comment Utility
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
Comment Utility
A split here, maybe?

Fritz the Blank
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now