DataGrid using SQLDataAdapter while updating getting error in vb.net winforms

Question

I have populated a DataGridView from the two tables.i am working on windows forms

In page load event I have this code:

Dim adapter As SqlDataAdapter
Dim bSource As BindingSource
Dim dt1 As DataTable

     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim con As SqlConnection = New SqlConnection("Data Source=SUPPORT2\SUPPORT2;Initial Catalog=Registry;Persist Security Info=True;User ID=sa;password=solutions") 'SET THE CONNECTION STRING
            con.Open()

            Dim cd As SqlCommandBuilder = New SqlCommandBuilder(adapter)
            adapter = 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)
            dt1 = New DataTable
            bSource = New BindingSource
            adapter.Fill(dt1)
            bSource.DataSource = dt1
            gv.DataSource = bSource
        End Sub

in update button click i given code like this:

 Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
    gv.EndEdit()
    bSource.EndEdit()
    adapter.Update(dt1)
End Sub

Whenever I edit something in the dataGridView and click update button i am getting error in this line adapter.Update(dt1) :Error:
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Tim Schmelter · Accepted Answer · 2014-01-06 15:26:58Z

1

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

The error is correct, you need to provide an UpdateCommand, otherwise the dataadapter does not know how to update a record.

adapter.UpdateCommand = New SqlCommand("UPDATE CompanyMaster_tbl SET CompanyName = @CompanyName, dtName = @dtName, dtPhone = @dtPhone, dtEmail = @dtEmail WHERE Cid = @Cid", con)

You should use parameters to prevent sql-injection:

adapter.UpdateCommand.Parameters.AddWithValue("@Cid", cid)
adapter.UpdateCommand.Parameters.AddWithValue("@CompanyName", CompanyName)
adapter.UpdateCommand.Parameters.AddWithValue("@dtPhone", dtPhone)
adapter.UpdateCommand.Parameters.AddWithValue("@dtEmail", dtEmail)

answered Jan 6, 2014 at 15:26

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3106114 Over a year ago

sir..in this line :adapter.UpdateCommand.Parameters.AddWithValue("@Cid", cid) instead of cid i have to write corresponding data grid view column right?

Tim Schmelter Over a year ago

That's the variable that you could provide manually. You could use the Current property of the BindingSource to access the values.

user3106114 Over a year ago

sorry for asking this: how to use current property of BindingSource to access the values

Tim Schmelter Over a year ago

@user3106114: try dim rv = DirectCast(bSource.Current, DataRowView) Dim companyName = rv.Row.Field(String)("CompanyName") and so on in btnupdate_Click.

user3106114 Over a year ago

sir,the same time i want to update more than one column,,so this things i have to write in for loop?

|

Collectives™ on Stack Overflow

DataGrid using SQLDataAdapter while updating getting error in vb.net winforms

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related