locked
Using command builder to update oracle 9i db. RRS feed

  • Question

  • Hi there,

     

    Our product has to work with MSSQL and ORACLE, so we use oledb to avoid the hassel.

    For updating, we are using command builder, for MSSQL, it's not a problem. But, for Oracle, we are getting

    error:

    System.Data Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

    We have primary key on the table, but still having problem.

     

    I read a lot of threads, could not find a solution to it. Do we have a solution with command builder? or we have to build command ourself.

     

    Thanks a lot!

     

    George.

    Thursday, December 20, 2007 8:15 PM

Answers

All replies

  • Update is a very complex operation in RDBMS(relational database management systems) so I don't think you can write one code and run in both Oracle and SQL Server.  Microsoft have covered restrictions for ODBC and OLEDB when using Oracle please remember to read the other relevant links.

     

    http://support.microsoft.com/kb/244661

     

    Thursday, December 20, 2007 8:47 PM
  • Thanks for the comment.

     

    We were very comfortable with oledb. It works very well actually in the past. Those problems mentioned in article are taken care of when using MSSQL or Oracle oledb driver if we switch database.

     

    Thanks again.

    Thursday, December 20, 2007 9:08 PM
  • The known reason for this error in Oracle is missing Primary Key and Updating both tables in a JOIN which is invalid, if the above is applicable to your code you can make changes.

     

    Thursday, December 20, 2007 10:09 PM
  • Yeah, we have primary key, and update one table at a time. It still gets problem.

    Thursday, December 20, 2007 10:26 PM
  • Here are two articles from the person who created the original ADO while at Microsoft why you should avoid the commandbuilder.  It will help you troubleshoot your issue.

     

    http://msdn2.microsoft.com/en-us/library/ms971491.aspx

     

    http://betav.com/blog/billva/2006/08/question_of_the_day_commandbui.html

     

     

    Thursday, December 20, 2007 10:42 PM