none
C# Error "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information" when calling the OracleDataAdapter Update Method RRS feed

  • Question

  •  

    Hello,


    I've got an InvalidOperationException when I call the the OracleDataAdapter Update method : "adapter.Update(userTable);".

    I've got an OracleDataAdapter named adapter, a DataSet named ds, a  DataTable named userTable

    I use 2 related tables in my queryString :

    select i.num_int_int as "Numéro interne",
           i.nom_log  as "Login",
           i.psw_web  as "Password",
           i.lib_int  as "Libellé User",
           u.cod_usp as "Profil",
           i.cod_soc as "Société",
           i.flg_out  as "OUT"
    from tab_inte i, tab_usty u
    where i.nom_log is not null and
          i.psw_web is not null and
         (i.flg_out <> 'O' or i.flg_out is null or i.flg_out = 'N')
          and i.num_int_int = u.num_int_int
          and i.cod_soc = u.cod_soc
    order by i.num_int_int



    I've got the following error "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information" after

    adapter.Fill(ds);
    userTable = ds.Tables[0];
    adapter.Update(userTable);

    If I Change my queryString to a single table select, I've got no more error:
    queryString :  

    select num_int_int as "Numéro interne",
           nom_log  as "Login",
           psw_web  as "Password",
           lib_int  as "Libellé User",
           cod_soc as "Société",
           flg_out  as "OUT"  
    from tab_inte
    where nom_log is not null and
          psw_web is not null and
         (flg_out <> 'O' or flg_out is null or flg_out = 'N' )
    order by num_int_int


    Any idea on how to resolve this issue?

    PS : This not an SQL issue, because the query works fine in SQL+ and Toad for Oracle.

    PS2: table tab_inte (alias i) has a simple primary key : i.num_int_int
         table tab_usty (alias u) has a composite primary key : (u.num_int_int,u.cod_usp)

    PS3: I'm using VS2010/2012 and .Net Framework 4


    Best regards, IT45200




    • Edited by it45200 Tuesday, September 9, 2014 2:52 PM
    • Moved by Kristin Xie Wednesday, September 10, 2014 2:27 AM
    Tuesday, September 9, 2014 2:10 PM

Answers

  • Hello it45200,

    Query statement like your first one is treated as a view and is read only. The DataSet would not generate the correspondent update command, you need to write yourself update command as:

    String updateCommand=”…”;
    
    da.Adapter.UpdateCommand = new System.Data.SqlClient.SqlCommand(updateCommand, da.Connection);
    

    Bets Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 10, 2014 8:13 AM
    Moderator

All replies

  • Hi it45200,

    I mvoed your thread to ADO.NET DataSet forum for better support.

    Have a nice day!

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 10, 2014 2:28 AM
  • Hello it45200,

    Query statement like your first one is treated as a view and is read only. The DataSet would not generate the correspondent update command, you need to write yourself update command as:

    String updateCommand=”…”;
    
    da.Adapter.UpdateCommand = new System.Data.SqlClient.SqlCommand(updateCommand, da.Connection);
    

    Bets Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 10, 2014 8:13 AM
    Moderator