I need to run an update query in my code, to update the visible rows. At the moment, my code is trying to update all rows, which is causing errors due to primary keys etc, an issue which would not arise if only visible rows were being updated, due to the selection method that is being used.
How can I modify the following code to update only the visible rows, rather than all of them in the datasource?
For Each ugr As UltraGridRow In ugModules.Rows Try modID = ugr.Cells("ModuleID").Text mName = ugr.Cells("ModuleName").Text numUsers = ugr.Cells("NumberUsers").Text Catch numUsers = 0 End Try
sql = "UPDATE dbo.tblModules SET ModuleName = ?, NumberUsers = ? WHERE ContractID = ? AND ModuleID = ?" cmd = New OleDbCommand(sql, con) cmd.Parameters.Add("@mname", OleDbType.VarChar).Value = mName cmd.Parameters.Add("@numusers", OleDbType.VarChar).Value = numUsers cmd.Parameters.Add("@conID", OleDbType.Integer).Value = CID cmd.Parameters.Add("@mid", OleDbType.Integer).Value = modID
If cmd.ExecuteNonQuery() = 0 Then sql = "INSERT INTO dbo.tblModules(ModuleID, ModuleName, NumberUsers, ContractID) VALUES(?, ?, ?, ?)" cmd = New OleDbCommand(sql, con) cmd.Parameters.Add("@mid", OleDbType.Integer).Value = modID cmd.Parameters.Add("@mname", OleDbType.VarChar).Value = mName cmd.Parameters.Add("@numusers", OleDbType.VarChar).Value = numUsers cmd.Parameters.Add("@conID", OleDbType.Integer).Value = CID
cmd.ExecuteNonQuery() End If Next
Hello David,
It depends on what you mean by visible rows. My guess is that you've filtered out rows, and you want the rows which passed the filter. If that's the case, you could use GetFilteredInNonGroupByRows off of your layout Rows collection to get a collection of the filtered in rows.
If you've set Hidden on some rows to true and only want to apply your query to the non-hidden rows, you could simply check the Hidden property on each row before continuing onto the query.
Finally, you may mean only the rows scrolled into view. In that case, the RowScrollRegions[0].VisibleRows would be appropriate to use.
Please let me know if you didn't mean any of the above scenarios.