none
SQLCommandBuilder Base Table Information? RRS feed

  • Question

  • We have two programs with virtually the same code reading the same database with the same SQL query.

    The commandtext for select command in both programs is:
    Select BatNbr, EmpID, EndDate, Hours, LineNbr, ProjectID, TaskID From XDCPrjTimeEntry Where BatNbr = @BatNbr"

    The ds.fill for the command works in both programs but the SQLCommandBuilder.GetInsertCommand in one program throws an error message saying: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

    What does this error mean? And how can I fix it?


    Price Brattin, SQLServer MCP, Microsoft Dynamics SL Consultant
    • Moved by Aland Li Tuesday, February 16, 2010 10:00 AM Incorrect forum (From:Windows Forms Data Controls and Databinding)
    Friday, February 12, 2010 11:49 PM

All replies

  • Instead of ds.Fill, I meant to say DataAdapter.Fill works in both programs.


    Price Brattin, SQLServer MCP, Microsoft Dynamics SL Consultant
    Friday, February 12, 2010 11:53 PM
  • Price,

    are you returning the primary key column in your select?

    the builder will not generate without a primary key from what i understand.
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Sunday, February 14, 2010 4:11 PM
  • Yes, the elements of the primary key were being returned in the SQL commandtext. I found the problem. In one program, the SQLCommand was prepared and in the other it was not. The program having the Prepared SQLCommand is the one throwing the error message.

     

    I took out the Cmd.Prepare() stmt and the program works.

     

    Is this a bug or is it supposed to operate that way?

     

    If it is not a bug, is there a way to specify the base table information when setting up a SQLCommand with the Prepare method?


    Price Brattin, SQLServer MCP, Microsoft Dynamics SL Consultant
    Sunday, February 14, 2010 6:13 PM
  • Hi

    I am moving this thread from the "Windows Forms Data Controls and Databinding " forum to the "ADO.NET DataSet " forum, since the issue is related to "ADO.NET DataSet ". There are more "ADO.NET DataSet " experts in the "ADO.NET DataSet " forum.

    Aland Li


    Please mark the replies as answers if they help and unmark if they don't. This can be beneficial to other community members reading the thread.
    Tuesday, February 16, 2010 9:57 AM
  • I guess you can move to whereever you want. But there is nothing about ADO.Net datasets in this thread nor does it have anything to do with datasets. Is there a forum for for plain ADO.Net?
    Price Brattin, SQLServer MCP, Microsoft Dynamics SL Consultant
    Tuesday, February 16, 2010 12:44 PM
  • Hi Price Brattin,

    As far as I know, this is no plain ADO.Net forum, this is the most similar one.

    Regards,
    Aland Li
    Please mark the replies as answers if they help and unmark if they don't. This can be beneficial to other community members reading the thread.
    Wednesday, February 17, 2010 2:06 AM
  • Most pro developers have moved away from using the SqlCommand<don't use>Builder. As I discuss in my books on ADO.NET, it's nowhere near as sophisticated as the ADOc (COM-based ADO) CommandBuilder as it lacks property support and has other issues that won't be fixed (haven't been fixed in a decade). Most folks have moved to strongly typed TableAdapters that deal with most of the issues in other ways and create a much more robust and more easily managed set of objects that perform better.

    hth
    William (Bill) Vaughn -- Mentor, Author, Dad and MVP
    Saturday, February 20, 2010 3:34 AM
    Moderator
  • As an aside, is there anything in ADO.NEt that is as powerful or sophisticated as the same object in Classic ADO?

    Regardless, how does one use strongly typed TableAdapters when the program is run against a table in which the columns are not the same as the table in the developement environment? How would the insert and update commands know to add the columns in their syntax?
    Price Brattin, SQLServer MCP, Microsoft Dynamics SL Consultant
    Thursday, February 25, 2010 12:41 AM
  • Of course. If the schema changes the strongly typed TableAdapter is (by definition) pooched. In this case you need to create untyped DataSets. There are several ways to help their performance (like using ordinal binding) and writing intelligent queries.


    William (Bill) Vaughn -- Mentor, Author, Dad and MVP
    Thursday, February 25, 2010 4:30 PM
    Moderator