Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • 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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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