Well, that clears things up on my end. I was confused as to how a SELECT statement produces "updatable results" because SELECT just gives output. Go into MySQL on the commandline and see if
Select * from vwMyView and Select * from vwMyView ORDER BY Name produce the same results, just in a different order. If they do, as expected, then the problem is not in MySQL. Since you can update it in the application with the former select query and not the latter, I don't think it's permissions or anything; rather something in the application. It sounds like Visual Basic, as ODBC just passes connections along to MySQL. -Sheeri On 5/12/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote:
Hey Sheeri. Thanks again for the response. I do agree that this is a known problem with SQL Server. My problem is that I am using a Windows Visual Basic application to access a Linux based MySQL Server via MyODBC/Connector. There is no longer SQL Server in the loop. I believe this is a Microsoft Cursor Engine error that is being issued, and it just happens to be the same one that is associated with the SQL Server issue. I have been able to reproduce my problem on many levels. I created 2 basic tables, created a view where "table1 left join table2". If, in Visual Basic, I call the view and order by a table1 field, I can update. If I order by a table2 field, I can no longer update and get the "Invalid Key Column for Updating or Refreshing" error. While the issue stems from the view definition in MySQL, the actual problem could be caused by Visual Basic, MyODBC or MySQL itself. I don't know what to try next. As always, any help is greatly appreciated. Thanks again, Travis Eland -----Original Message----- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Fri 5/12/2006 10:01 AM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable That's a problem with SQL Server -- google search on your error and you'll see that that's associated with SQL server, not MySQL. -Sheeri On 5/11/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: > Thanks for the response! Unfortunately, it is that simple. > > A basic "Select * from vwMyView" yields an updatable recordset. > > Adding "Order by Name" to the end does not allow an update. > > I should mention that the error associated with the lack of update is: > "Insufficient Key Column Information for Updating or Refreshing" > > I have since futhered my troubleshooting and determined that I actually AM able to update the recordset when the order by is applied in some situations. Apparently, I can order by any field that is in the view's main table (the table that all of the other tables left join off of) and still be able to update. It is when I order by a field that is not from this main table that I get the above error and inability to update. > > I am still at a loss as to how to fix this so that I can order by any field I wish. > > Any input is greatly appreciated. > > Thanks, > Travis Eland > > > > ________________________________ > > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Thu 5/4/2006 4:15 PM > To: Eland, Travis M. > Cc: mysql@lists.mysql.com > Subject: Re: ORDER BY making recordset non-updatable > > > > Maybe I'm thick.... > > You have a view, called vwMyView. > > You SELECT rows from it, and you're able to update the view? > > Yet when you SELECT with an ORDER BY clause, you're not allowed to > update the view? > > I just do not understand how a read statement affects DML. I think > you're going to have to post the query you're using, as it's more > complex than a SELECT. Perhaps you're using a REPLACE INTO .... > SELECT statement? Or UPDATE .... WHERE .... IN (SELECT....)? > > -Sheeri > > On 4/19/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: > > Heya. > > > > I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): > > > > Select * from vwMyView where id = ? > > > > If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). > > > > My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. > > > > I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. > > > > Is this a known issue? Is there something that I could possibly be missing? > > > > I apologize for the lack of actual code, but I appreciate any insight! > > > > Thanks! > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
