Current Recordset does not support updating...

K

kmar

Guppy
Hi everyone,

I've got a problem, I first had my website running with an Access db, without any problems, since I moved it to MSSQL some pages generate an error I don't know how to fix. The error occurs when I try updating a record.

This is the code:
if request.querystring("repair") = "true" then
rsMaster.open "select categoryid, sortorder from categories order by sortorder asc", dbConnection, 2, 3
index = 1
do until rsMaster.EOF
rsMaster("sortorder") = index
index = index + 1
rsMaster.Update
rsMaster.MoveNext
loop
rsMaster.close
end if
And this is the error I get:

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

I thought I would be some kind of permission problem, so I sent in a ticket to have that checked but it appears that the login I am using to insert data has full permissions to the database (dbo rights).

I don't know what else the problem can be. Any suggestions?

Thanks!
 
Hi devorem, thank you for your reply!

I'll see if I can change some things to the code later today. I'll keep you posted. Thanks again, hope it helps!

Kind Regards
 
Hi Kmar,
the error is in your open
rsMaster.open "select categoryid, sortorder from categories order by sortorder asc", dbConnection, 2, 3

try this
rsMaster.open "select categoryid, sortorder from categories order by sortorder asc", dbConnection, 1,1
that should do the trick for you.
Paul
 
Thanks for the reply Paul!

But I had already fixed the problem with this bit of code:


dbconnection.execute("UPDATE forums SET sortorder = " & index & " WHERE forumid = " & rsMaster("forumid"))

But thanks anyway,

Regards Kmar
 
Guys... I have the similar issue. Your help/assistance will be appreciated...

I had the scripts running and connecting to MS Access... and all works dandy

But when I try to make it work with Oracle Database... and when I try to ADD record, I get the Error.
Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
include/DocInfo_aspFunctions.asp, line 188
Click to expand...

It suggests that Line 188 is in Error

The following is my code for that page:
Code: 
<%

function DocInfo_QryStr

  'strqry="SELECT  UNIQUE_ID, DOCUMENT_TYPE AS [Document Type],  PROPERTY_ADDRESS AS [Property Address], " & _
   '              " POSTAL_CODE AS [Postal Code], LAST_NAME AS [Last Name], " & _
    '             " FIRST_NAME AS [First Name], Left([Document_description],50) AS Description,  DOCUMENT_LOCATION AS " & _
     '            " [Document Location],  REGISTRATION_NUMBER AS [Registration Number],  DOCUMENT_DATE AS [Document Date], " & _
      '           " COUNCIL_APPROVAL AS [Council Approval] " & _
       '          " FROM DLTS_DOCUMENT_DETAIL "
         strqry="SELECT Unique_Id, DOCUMENT_TYPE, PROPERTY_ADDRESS, " & _
                 " POSTAL_CODE, LAST_NAME," & _
                 " FIRST_NAME, Document_description As Description, DOCUMENT_LOCATION," & _
                 " REGISTRATION_NUMBER, DOCUMENT_DATE, " & _
                 " COUNCIL_APPROVAL" & _
                 " FROM DLTS_DOCUMENT_DETAIL "
  docinfo_qrystr=strqry

end function

sub MainMenu

%>
<center>
<table border="0" cellpadding="0" cellspacing="0" width="500">
    <tr>
        <td align="center" bgcolor="#ffffff"><font color="#000000" size="5" face="arial, sans-serif">
        <b><u>Document Management System</u></b></td>
    </tr>
</table>
<br>

<% if session("admin") then
      tbsz=300
   else
      tbsz=200
   end if 
%>
<table border="0" cellpadding="0" cellspacing="0" width="<% =tbsz %>">
    <tr>
        <td align="center" width="100" bgcolor="#000000"
        height="20" onmouseover="this.bgColor='#772299';" onmouseout="this.bgColor='#000000';"><a href="docinfo_search.asp"><font
        color="#FFFFFF" size="1" face="arial"><b>Find Document</b></font></a></td>
    <% if session("admin")=0 then %>

    <td align="center" width="100" bgcolor="#000000"
        height="10" onmouseover="this.bgColor='#772299';" onmouseout="this.bgColor='#000000';"><a href="docinfo_login.asp"><font color="#FFFFFF"
        size="1" face="arial"><b>Login Admin </b></font></a></td>
    <% end if %>
       <% if session("admin")=1 then %>
            <td align="center" width="100" bgcolor="#000000"
             height="10" onmouseover="this.bgColor='#772299';" onmouseout="this.bgColor='#000000';"><a href="docinfo_edit.asp"><font color="#FFFFFF"
             size="1" face="arial"><b>Add New </b></font></a></td>
       <% end if %>
    </tr>
</table>
</center>
<%

end sub

function RecExist(strqry)

      set rs=server.createobject("adodb.recordset") 
      rs.open strqry, strconnection

      if rs.eof then
         RecExist=0
      else
         RecExist=1
      end if

      rs.close
      set rs=nothing

end function 

function RunQry(strqry)

      recAff=0
      set cnn=server.createobject("adodb.connection") 
      cnn.open strconnection
      cnn.execute strqry, recAff
      if recaff<>0 then
         runqry=1
      else
         runqry=0
      end if
      cnn.close
      set cnn=nothing
end function 

Function Addnew_Street (dt)
    dim rs 

    dt=ucase(trim(dt))
    strqry="Select * from DLTS_LKP_STREET WHERE street='" & dt & "'"
   
    set rs=server.createobject("adodb.recordset")
    rs.open strqry, strconnection, 2, 3

    if rs.eof then
       rs.addnew
       rs("Street")=dt
       rs("Municipality")="MARKHAM"
       rs.update
    end if
    rs.close         
    set rs=nothing

End function 

Function Addnew_DocType_Data (dt)
    dim rs 

    dt=ucase(trim(dt))
    strqry="Select * From DLTS_LKP_DOCUMENT_TYPE " & _
           " Where Document_Type_Description='" & ucase(dt) & "'"

    set rs=server.createobject("adodb.recordset")
    rs.open strqry, strconnection, 2, 3
    if rs.eof then
       set rs_dtid=server.createobject("adodb.recordset")
  
       strqry="Select (Max(document_type_id)+1) as new_dtid From DLTS_LKP_DOCUMENT_TYPE "
       rs_dtid.open  strqry, strconnection, 2, 3

       new_dtid=1 

       if isnumeric(rs_dtid(0)) then
          new_dtid=rs_dtid(0).value
       end if
       set rs_dtid=nothing       

       rs.addnew
       rs("document_type_id")=new_dtid
       rs("dococument_type_description")=ucase(dt)
       rs.update
       rs.close         
       set rs=nothing
    end if

End function 

function Update_DocInfo_Data(strqry, data, noFld, updtAs)

    dim rs 
    dim rs_uid

    set rs=server.createobject("adodb.recordset")
    rs.open strqry, strconnection, 2, 3

    if lcase(updtAs)="edit" then

       if not rs.eof then
          for i=0 to nofld
              if isdate(data(i)) then
                 rs(i)=datevalue(data(i))
              else
                 rs(i)=data(i)
              end if
          next
          rs.update
          updatedata="Data has been updated successfully"
       else
         updateData="Due to some error data could not be updated."
       end if
    end if

    if lcase(updtAs)="new" then

       set rs_uid=server.createobject("adodb.recordset")
  
       strqry="Select (Max(unique_id)+1) as new_unqid From DLTS_DOCUMENT_DETAIL "
       rs_uid.open strqry, strconnection, 2, 3
       newuid=rs_uid(0).value

       if not isnumeric(newuid) then
          data(10)=1
       else        
          data(10)=newuid
       end if

       rs_uid.close
       set rs_uid=nothing

       rs.addnew
       for i=0 to nofld
           if isdate(data(i)) then
              rs(i)=datevalue(data(i))
           else
              rs(i)=data(i)
           end if
       next
       rs.update
       updatedata="New data has been added successfully"
    end if
    rs.close
    set rs=nothing

end function 

%>
 
I suggest changing your code to update the record with a SQL statement, not using a recordset. Just like the original poster did.

Mark
 
I had the same problem when I converted from access to sql server. It seems that at least some of the tables lost their primary key.
Without the primary key the recordset seems to become read only.
I have re- setup the primary key info and i am now able to do recordset updates.

I hope this is of some help.

Mark.
 
Using the recordset will allow you to have granular control of your update, especially if you want it transactionalized. Then again, if this is what you need/want.

Otherwise, of course using the connectionobject.Execute(sqlstatement) is fine.

Honestly, I am wondering why don't you just take advantage of stored procedures and/or functions to handle queries so you can pass data as parameters (and yada, yada, yada) and make it less likely for SQL injection to happen? Oh well, maybe its just me, but I'd rather separate the SQL statements from my scripts. :p

Anyway, you may want to visit http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp.
 
Having read this thread I think I need to post a request for help. I am having the same problem updating to Oracle although I can read the data from Oracle. I am new to this game although I have written VB.Net programs to interact with an Access database.

So does anybody know of a good website/books where I can find some help on VB.Net and Oracle interaction or offer any help.
 
If your using a ms SQL server then you need more permissions to use the rs.addnew method. The way to correct this is open up the MMC and go to the SQL Server Logins under the security tab. Once you have done this you need to open the account that you use to connect to the database and select the "Server Roles" tab. In there there is a permission called "Bulk Insert Administrations", you need that permission to use rs.addNew because you could have a transaction to update hundreds of rows which would use rs.addNew but for single records they would expect you to execute a SQL script.

Its a little silly really because if you really wanted to do some mass work then you could just write your own script with a loop and generate the SQL to insert this even would probally work faster on machines with little free memory.
 
You must log in or register to reply here.
Back
Top