Running Query on/with Binded Controls

Public Const ConnectionString As String = _

"integrated security=sspi;initial catalog=pubs;data source=localhost"

Protected Const GetAllAuthorsSqlString As String = "select au_id,au_lname,au_fname,phone," + _

"address,city,state,zip,contract from authors order by au_lname,au_fname"

Public Sub SaveChanges()

Dim table As DataTable = CType(datagridAuthors.DataSource, DataTable)

Dim changedRows As New ArrayList

For Each row As DataRow In table.Rows

If row.RowState <> DataRowState.Unchanged Then

changedRows.Add(row)

End If

Next

If changedRows.Count = 0 Then

Return

End If

Dim connection As New SqlConnection(ConnectionString)

connection.Open()

Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)

Dim builder As New SqlCommandBuilder(adapter)

Dim rows() As DataRow = CType(changedRows.ToArray(GetType(DataRow)), DataRow())

adapter.Update(rows)

adapter.Dispose()

connection.Close()

menuSaveChanges.Enabled = False

End Sub

Public Sub RefreshData()

Dim connection As New SqlConnection(ConnectionString)

connection.Open()

Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)

Dim dataset As New DataSet

adapter.Fill(dataset)

adapter.Dispose()

connection.Close()

Dim table As DataTable = dataset.Tables(0)

AddHandler table.ColumnChanged, New DataColumnChangeEventHandler(AddressOf ColumnChanged)

datagridAuthors.DataSource = table

End Sub