Cannot get data from grid to persist to database

Posted on 2009-04-22
Last Modified: 2012-05-06

I am trying to databind an infragistics datagrid, and I want the changes that the user makes in the grid, to be persisted back to the database. I have to following code (that doesn't work)

When I edit the data in the grid and call adDE.Update(ds) it fails with the following error: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"

How do I make the changes in the grid, happen in the database?

Thanks in advance
'First we create a view, using our connection object

            conn.ExecuteNonQuery("CREATE VIEW v_DataEditor AS  Select D.ItemNo, S.StorageName, SN.ShelfNumber From DataTable D, StoragesTable S, ShelfNumbersTable SN Where S.StorageID = D.StorageID and D.ItemID = S.ItemID and S.ShelfID = SN.ShelfID")

            'The we instantiate our SQLDataAdapter

            adDE = New System.Data.SqlClient.SqlDataAdapter("Select * from v_DataEditor order by DateAndTime", conn.Connection)

            'Then we build command SQL statements

            Dim cb As New SqlClient.SqlCommandBuilder(adDE)

            'Use our adaptor to fill an SQL dataset


            'Set the infragistics datagrid datasource to our dataset

            UGDataEditor.DataSource = ds

Open in new window

Question by:liversen
    LVL 6

    Accepted Solution

    I don't think you can since you are using a view - the datasource needs to have a unique primary key. If it hasn't you can still manually define the update statements (with a sqlcommandbuilder if I'm not wrong). You probably need to rework the datasource of create an update statement manually.
    LVL 1

    Author Comment

    I was using a view because data comes from several tables, and I was hoping that using a view, the commandbuilder would work. But ofcourse it doesn't, as it only works on single tables.
    Do you have a link to examples on manually created update statements, for use with the dataadaptor, that spans several tables?
    LVL 6

    Expert Comment

    That makes sense - but SQL doesn't automatically know how and in which order to update the underlying tables. If you were to use a simple table with primary key the dataadapter has enough info to generate the update/delete statements. don't be intimidated by the amount of code it takes to build them manually - it all makes sense and is quite simple.
    An example that deals with the key and command issue (there must be better ones around):
    LVL 1

    Author Comment

    Thanks, I'll give it a try  :o)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now