Getting an error while updating the grid view

Question

I have populated a DataGridView from the two tables.

In page load event I have this code:

Dim da As New SqlDataAdapter
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  Using adapter As SqlDataAdapter = New SqlDataAdapter("select c.CompanyName,d.dtName,d.dtPhone,d.dtEmail  from CompanyMaster_tbl c join  DepartmentMaster_tbl d on c.Cid=d.cId", con.connect)
    Dim dt As DataTable = New DataTable()
    adapter.Fill(dt) 
    gv.DataSource = dt
  End Using
End Sub

in update button click i given code like this:

 da = New SqlDataAdapter
        Dim dt1 As DataTable = DirectCast(gv.DataSource, DataTable)
        da.Update(dt1)
        gv.DataSource = dt1

but after editing anything in gridview,,i click the update button,,but i am getting error in this row da.Update(dt1)

Error:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Al-3sli · Answer 1 · 2014-01-06T13:37:52.883

1

try add SqlCommandBuilder to your code I think it will Fix This I did not try it but hopefully it will work :

    dim cd as SqlCommandBuilder = new SqlCommandBuilder(da)
    Dim dt1 As DataTable = DirectCast(gv.DataSource, DataTable)
    da.Update(dt1)
    gv.DataSource = dt1

edited Jan 06 '14 at 13:37

answered Jan 06 '14 at 12:51

Al-3sli

2,161
2
15
19

getting error in this line:da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand(),Error=>The DataAdapter.SelectCommand property needs to be initialized. – user3106114 Jan 06 '14 at 13:03
try not to reinitialize `da` – Al-3sli Jan 06 '14 at 13:34
reinitialize in the sense? – user3106114 Jan 06 '14 at 13:37
getting error in this line::da.Update(dt1) :Dynamic SQL generation is not supported against multiple base tables. – user3106114 Jan 06 '14 at 13:41

score 1 · Answer 2 · answered Jan 06 '14 at 13:01

1

SqlDataAdapter is a class that inherits DbDataAdapter. If you consult MSDN or Visual Studio's Object Brower you would see that every class inherited from DbDataAdapter has several "Command" properties like DeleteCommand, InsertCommand, UpdateCommand and SelectCommand.

Please check SqlDataAdapter (Class) in MSDN to expand your knowledge on the class' attributes.

These properties are used to interact with your database. For example, if you inserted a new Company or Department the InsertCommand will be executed on the database to insert it, but if you updated any field from a particular Company or Department the UpdateCommand will be used.

As I can see in your posted code, you are working with a two-tables related DataGridView, which makes it a little more difficult to build a propper UpdateCommand since you would have to update two tables per each Company or Department the user modifies.

Please check DbDataAdapter.UpdateCommand (Property) in MSDN to see some examples building update commands.

You would need to execute two UpdateCommands separately, one for each table, like this:

UPDATE CompanyMaster_tbl
SET    c.CompanyName = @CompanyName
WHERE    c.Cid= @Cid

UPDATE DepartmentMaster_tbl
SET    d.dtName = @dtName,
       d.dtPhone = @dtPhone,
       d.dtEmail = @dtEmail
WHERE  d.cId = @Cid

Good luck!

answered Jan 06 '14 at 13:01

GoldenLight

199
1
4

sir i am not populating @cid(company id) value to my gridview – user3106114 Jan 06 '14 at 13:08
You should include it as a hidden column, in order to update the corresponding row correctly in the database. – GoldenLight Jan 06 '14 at 13:53
in button click how i can recognize that which column is updated in the gridview – user3106114 Jan 06 '14 at 13:55
The UpdateCommand is executed to update THE ENTIRE row as specified on the CommandText, no only a particular column. – GoldenLight Jan 06 '14 at 16:03
However, you can always use the `OnCellEndEdit` event to capture specific cell edits. Check this out: [DataGridView.OnCellEndEdit (Method)](http://msdn.microsoft.com/es-es/library/system.windows.forms.datagridview.oncellendedit(v=vs.110).aspx) – GoldenLight Jan 06 '14 at 16:10
i didn't get you? still i am confusing how to get value of edited coulmn – user3106114 Jan 06 '14 at 16:11
This would be the general idea: >`private void gv_CellEndEdit(object sender, DataGridViewCellEventArgs e) { if (gv.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null) { MessageBox.Show(gv.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString()); } }` – GoldenLight Jan 06 '14 at 18:39

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

Because the data you are accessing was created with a Join, your data update won't be able to update it. The data update only works on simple tables (no fancy things like Joins). If you want this to work, you need to manually update the table with another query and then reload the table to the datagridview. For more information, see this article and this article.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 06 '14 at 12:44

gnarlybracket

1,691
4
18
37

Getting an error while updating the grid view

3 Answers3