Data Environment Not Updatable

Heya.

My issue is yet another one of those issues that causes the error:

"Insufficient Key Column Information to Update or Refresh"

Fortunately, I have been able to reproduce this issue on a very basic level. It may just be happening on my machine, or it could be a problem that is more prevalent. It is an issue only related to the Data Environment useage. If I attempt the same things in pure code I have no issues.

I have a Linux machine running MySQL. I am connecting to this via MyODBC.

My tables/views are defined in MySQL as

Code:

---

test1 (table)
-------
id (integer/autoincrement)
name1 (varchar(45))
test2(integer/Foreign Key to test2 table)

test2 (table)
-------
id (integer/autoincrement)
name2 (varchar(45))

vw_test (view)
------
Select * from test1 left join test2 on test1.test2 = test2.id

---

If I execute the following code, it updates the first record with no problem.

Code:

---

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset

Set conn = new ADODB.Connection
Set rs = new ADODB.Recordset

conn.connectionString = "Driver={MySQL ODBC 3.51 Driver};Server=111.111.111.111;Port=3306;Database=myDatabaseName;Uid=myUserName;Pwd=myPassword;Option=3;"
conn.open

rs.open "Select * from vw_test order by name1, name2",conn,adOpenStatic,adLockOptimistic

rs("Name1") = rs("Name1") & "-Edited"
rs.update

rs.close
conn.close

---

Now, if I do what SHOULD be the same thing using a Data Environment, I run into trouble.

Code:

---

I define the Data Environment connection w/ connection string:
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="Driver={MySQL ODBC 3.51 Driver};Server=111.111.111.111;Port=3306;Database=myDatabaseName;Uid=myUserName;Pwd=myPassword;Option=3;"

I define the command within the Data Environment as:
"Select * from vw_test order by name1, name2"

---

If I go into "design" and run the query. The data comes up, but if I try to change any data, I get the error mentioned above.

I Found that if I only order by fields from test1, I am able to update all that I want. Unfortunately, if I order by test2 fields, I cannot update anything.

This issue stems from a much larger program that I am working on. I was able to order a view by any of the main table (the table that the other tables were left joined to) with no problem, but as soon as I ordered by a field from another table in the view, it became non-updatable.

What is it that is different between the Data Environment and the regular code method that is causing this error?

Any thoughts are greatly appreciated.

Thanks,
Enigma

I cannot not really tell the difference but perhaps there are default settings in the data environement that causes such error. May I ask, if you can do it with pure code then what's your use of the Data Environment?

Thanks for the reply!

Unfortunately, I have tried every setting available on the properties page for the DataEnvironment, and I still see no difference. I have noticed that if I set the cursor in the code method to client side, it creates the same error that I am encountering with DataEnvironments. On the other hand both client and server side cursors give the error for the DataEnvironment.

In an ordinary situation, I would do this all through pure code. In this case, however, I am changing a program already written with a ton of DataEnvironments to use a MySQL connection versus a SQL Server connection.

The Data Environments DID work with the SQL Server database, so this issue is quite confusing. The SQL Server DE Commands were able to use the dbo. stuff that I had to strip out for MySQL. So, it could have something to do with this.

I am really out of ideas. :confused:

Thanks,
Enigma