Member Avatar for mweshk

Run time error 3251 Current Recordset does not support updating, this may be a limitation of the provider, or of the selected Locktype

Friends, I have a VB application that is using Oracle backend database. My code is as below but returns the above error when I try to add data.Kindly assist.

Private Sub cmdsave_Click()
rs.AddNew
rs!ID = txtID.Text
rs!Name = txtNAME.Text
rs!GROUPID = txtGROUPID.Text
rs!DATERECEIVED = txtDATERECEIVED.Text
rs!MODEL = txtMODEL.Text
rs!SERIALNO = txtSERIALNO.Text
rs!CERTIFIEDBY = txtCERTIFIEDBY.Text
rs!PROCUREMENTOFFICER = txtPROCUREMENTOFFICER.Text
rs!RECIEVEDBY = txtRECIEVEDBY.Text
rs.Update
txtID.Text = ""
txtNAME.Text = ""
txtGROUPID.Text = ""
txtDATERECEIVED.Text = ""
txtMODEL.Text = ""
txtSERIALNO.Text = ""
txtCERTIFIEDBY.Text = ""
txtPROCUREMENTOFFICER.Text = ""
txtRECIEVEDBY.Text = ""

If rs.BOF <> True And rs.EOF <> True Then
rs.MoveLast
i = Val(rs!ID)
i = i + 1
txtID.Text = i
Else
txtID.Text = 1
End If
'cmdRefresh_Click
txtID.SetFocus
'deIT.rsrecieve.Update
End Sub
Private Sub Form_Load()
'txtNAME.SetFocus

End Sub

Dim i As Integer, j As Integer
cn.ConnectionString = "Provider=MSDAORA.1;Password=3389mine;User ID=system;Data Source=192.168.167.22;Persist Security Info=True"
cn.Open
rs.Open "select * from ITINVENTORY order by ID", cn, adOpenDynamic, adLockOptimistic
If rs.BOF <> True And rs.EOF <> True Then
rs.MoveLast
i = Val(rs!ID)
i = i + 1
txtID.Text = i
Else
txtID.Text = 1
End If

txtNAME.Text = ""
  • 2 Contributors
  • 1 Reply
  • 459 Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

Run-time error 3251 means the ADO Recordset you opened is read‑only — ADO is telling you the Recordset/driver combination cannot perform updates. Common triggers are: the provider/cursor/lock combination doesn’t support updates, the SELECT is non‑updatable (ORDER BY, joins, DISTINCT, aggregates, views without update support), or the connection/recordset objects were never created or the open code never ran (as noted, code sitting outside a Sub won’t execute).

Short, practical fixes to try first: declare and New your Connection and Recordset, set a client‑side cursor before opening, and avoid ORDER BY in the SELECT used for an updatable Recordset. Example pattern:

Private cn As ADODB.Connection
Private rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "(your provider/DSN here)"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM ITINVENTORY", cn, adOpenKeyset, adLockOptimistic

rs.AddNew
rs("Name") = txtName.Text
rs.Update

A more robust, provider‑independent approach is to issue an explicit INSERT (parameterized) instead of relying on Recordset AddNew/Update:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = "INSERT INTO ITINVENTORY (ID, Name) VALUES (?, ?)"
  .CommandType = adCmdText
  .Parameters.Append .CreateParameter("p1", adInteger, adParamInput, , CInt(txtID.Text))
  .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 200, txtName.Text)
  .Execute
End With

Checklist / cautions: move connection/rs setup into Form_Load (so it runs), ensure the table has a real primary key or ROWID (providers often need a key to generate updates), check that the DB user has INSERT/UPDATE rights, avoid using the SYSTEM account for app connections, and consider switching from the old Microsoft Oracle provider to Oracle’s supported OLE DB/ODBC driver if you’re on modern Windows.

Member Avatar for BitBlt

The first thing I notice is that the code from line 40 through 53 is not inside a sub or function, and appears never to execute. I also see no DIM statement for either cn or rs (maybe at the module level, or global?)

So, based on the code you posted, either your connection and recordset variables are never instantiated or opened, or you have variable scoping or typing issues.

If these are just excerpts or snippets then you need to give more context. Otherwise, we're just guessing.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.