• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

Possible to use ADODB Recordset.AddNew function to add new record to database?

Hi Experts:

     I tried to use the following test code to insert records to a database table.
******************************************************************************
con = win32com.client.Dispatch(r'ADODB.Connection')
con.Open("<this is a connection string>")
con.Execute("<dbms_session.set_role command>")
cDataset = win32com.client.Dispatch(r'ADODB.Recordset')
cDataset.Open("SELECT * FROM table_name WHERE 1=0", con, 3, 3, 1)
cDataset.AddNew()
cDataset.Fields.Item("AREA").Value = 1
cDataset.Update()
*************************************************************************************

How ever I got the following error message
*************************************************************************************
  File "<COMObject ADODB.Recordset>", line 3, in AddNew
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'ADODB.Recordset'
, 'Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype.', 'C:\\WINDOWS\\HELP\\ADO270.CHM', 124064
8, -2146825037), None)
*************************************************************************************

I check the spec of the AddNew function at
http://msdn.microsoft.com/en-us/library/ms675544(VS.85).aspx
but could not find the reason.

The values of the last three parameters of Recordset.Open (cursor type, lock type, options) are from a piece of VB code, so I cannot believe they are wrong. I also tried to chaned them around, but still not working.

The ADODB.Connection object should also be correct, because on another version of Python code I used con.Execute("<insert statement>") to insert some record instead of Recordset.AddNew, and it worked.

The python code is following the tutorial http://www.ecp.cc/pyado.html

How can I make it work? Thank you!

Background information: I was originally using con.Execute("<insert statement>")  to process millions of database records, but it was slow, so I want to see whether there is faster alternatives. If there is the third way (faster way) to insert records to a database with password protected role using Python and PythonWinExtension, please let me know... and then I do not need to use ADODB.
0
huangs3
Asked:
huangs3
1 Solution
 
huangs3Author Commented:
Solution found, just need to change Recordset.CursorLocation properly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now