Updating FoxPro table using ADO

rjm
10-22-2004, 08:02 AM
I have Foxpro tables that I'm trying to edit/add to but I keep getting an error "Current record does not support updating. This may be a limitation of the provider, or of the selected locktype."
It allows me to select records and read records, but does not allow edits or adds. I've tryed all 4 Locktypes in combination with all 4 CusorTypes available, but keep getting the same error.

I'm using VB6 with and ADO connection to FoxPro 8.0. Any ideas on what I'm doing wrong? Could it be something to do with rights to the database itself?


'-- Code
m_dbObjectDB.Mode = ADODB.ConnectModeEnum.adModeReadWrite
m_dbObjectDB.Open "Provider=VFPOLEDB.1;Data Source=c:\Mydatabase.dbc;SourceType=dbf;BackgroundFetch=Yes;Deleted=Ye s;Mode=ReadWrite|Share Deny None;" _
& "Extended Properties="""";User ID="""";Password="""";Mask Password=False;" _
& "Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE"

m_rsObjectRS.ActiveConnection = m_dbObjectDB
m_rsObjectRS.Open "SELECT * FROM Customer", m_dbObjectDB, adOpenKeyset, adLockOptimistic

VisualBrat
10-22-2004, 08:19 AM
That would really be my first guess. FoxPro tables are funny like that sometimes. I would check the properties of the FP database first. Make sure the tables are not read only. Maybe try creating a second copy of the tables locally that you own just to try to update that copy with your code. If it works, then you know you need to make adjustments to the database properties.

rjm
10-22-2004, 08:33 AM
That would really be my first guess. FoxPro tables are funny like that sometimes. I would check the properties of the FP database first. Make sure the tables are not read only. Maybe try creating a second copy of the tables locally that you own just to try to update that copy with your code. If it works, then you know you need to make adjustments to the database properties.

OK, I changed all my security permissions for the database and tables on all users listed including the Admin, System and Users to have Full control. I also tried creating a second copy and still get the same results. I also checked the copies of the tables to make sure the rights were not reset.

Still getting the same results.

VisualBrat
10-22-2004, 08:46 AM
this is the combonation that I always use when editing tables

"rs1.Open SQLstmt, cn, adOpenDynamic, adLockOptimistic, adCmdText"

does this help?

rjm
10-22-2004, 09:10 AM
this is the combonation that I always use when editing tables

"rs1.Open SQLstmt, cn, adOpenDynamic, adLockOptimistic, adCmdText"

does this help?

I tried that but still get the same error. I also tried it in combination with cursortype adOpenKeyset with same results.

What bugs me is this works just fine with Access db using Jet.

Dude, your Avatar just changed right in front of my eyes and I haven't started drinking! :chuckle:

MKoslof
10-22-2004, 05:16 PM
First off, let's clean up your connection string, most of this you don't need. Look here for a reference:

http://www.connectionstrings.com

And, as another test, create a test .udl file on your computer (just rename a text file as "test.udl"). Double click the UDL and try to establish a connection to the database in question, review the database providers that are present in your list. Once done, hit the "test connection" button..does it succeed? If so, open up the udl in textpad or notepad..what is the connection string provided to you?

rjm
10-25-2004, 08:27 AM
First off, let's clean up your connection string, most of this you don't need. Look here for a reference:

http://www.connectionstrings.com

And, as another test, create a test .udl file on your computer (just rename a text file as "test.udl"). Double click the UDL and try to establish a connection to the database in question, review the database providers that are present in your list. Once done, hit the "test connection" button..does it succeed? If so, open up the udl in textpad or notepad..what is the connection string provided to you?

Thanks for your reply.

I created the test.udl, I tested the connection and it was succesful and I get the following provider when I open up the test.udl in notepad:
VFPOLEDB.1

Here's the connection string I am currently using:

' connection string
m_dbObjectDB.Open "Provider=VFPOLEDB.1;Data Source=" & App.Path & ";SourceType=dbf;Mode=ReadWrite|Share Deny" _
& " None;User ID=;Password=;Collating Sequence=MACHINE"
m_rsObjectRS.ActiveConnection = m_dbObjectDB
m_rsObjectRS.Open "SELECT * FROM customer", m_dbObjectDB, adOpenKeyset, adLockOptimistic


I am able to read and loop through the recordset but it still does not allow me to write or edit the Customer.dbf table.

rjm
10-25-2004, 01:53 PM
Figured out the problem in case anyone else out there is interested. I found an article referencing the same error code and message I was receiving. It is specific to Microsoft Office Access 2003 in conjuction with Microsoft Jet OLE DB Provider.

http://support.microsoft.com/default.aspx?scid=kb;en-us;824256http://support.microsoft.com/default.aspx?scid=kb;en-us;824256

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum