• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Cannot get data from grid to persist to database

Hi

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
            adDE.Fill(ds)
 
            'Set the infragistics datagrid datasource to our dataset
            UGDataEditor.DataSource = ds

Open in new window

0
liversen
Asked:
liversen
  • 2
  • 2
1 Solution
 
mvgeertruyenCommented:
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.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx
0
 
liversenAuthor Commented:
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?
Thanks
0
 
mvgeertruyenCommented:
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):
http://blogs.msdn.com/codemouse/archive/2004/12/27/332972.aspx
0
 
liversenAuthor Commented:
Thanks, I'll give it a try  :o)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now