Problem:
Resolution:
You can bind a SQL table to a dataGridView control using a SqlDataAdapter object and this will allow you to invoke the Update method of the SqlDataAdapter in order to save the changed data back to the original database.
See the sample "bindadapter.zip" file attached to this article.
Code is shown below:
*--------------------------------------------------------------------------------*
* BINDADAPTER Example *
* *
* This example program demonstrates how a SqlDataAdapter can be used to bind a *
* data table to a dataGridView control so that the data can be edited directly *
* in the dataGridview and then the updated contents of the table will be written *
* to the datasource in the SQL Server database using the Update command. *
* *
* Change the value of the connect string to one that points to a valid SQL Server*
* installation on your computer. *
* This example allows for updates and insertion of new rows but not row deletions*
* as the Customers table is also dependant on Orders and Order Details tables. *
* After making changes, click on Submit button to update data in database. *
*--------------------------------------------------------------------------------*
$set ilusing"System.Data"
$set ilusing"System.Data.SqlClient"
class-id bindadapter.Form1 is partial
inherits type System.Windows.Forms.Form.
working-storage section.
01 dataAdapter type SqlDataAdapter.
method-id NEW.
procedure division.
invoke self::InitializeComponent
goback.
end method.
method-id reloadButton_Click final private.
procedure division using by value sender as object e as type System.EventArgs.
*> Reload the data from the database.
invoke self::GetData(dataAdapter::SelectCommand::CommandText)
goback
end method.
method-id submitButton_Click final private.
procedure division using by value sender as object e as type System.EventArgs.
*> Update the database with the user's changes.
invoke dataAdapter::Update(bindingSource1::DataSource as type DataTable)
goback
end method.
method-id Form1_Load final private.
procedure division using by value sender as object e as type System.EventArgs.
*> Bind the DataGridView to the BindingSource
*> and load the data from the database.
set dataGridView1::DataSource to bindingSource1
invoke self::GetData("select * from Customers")
goback
end method.
method-id GetData public.
local-storage section.
*> Modify connectString below to point to your own Data Source for SQL Server.
01 connectString string
value "Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=MYCOMPUTER".
01 commandBuilder type SqlCommandBuilder.
01 mytable type DataTable.
procedure division using selectCommand as string.
try
*> Create a new data adapter based on the specified query.
set dataAdapter to new SqlDataAdapter(selectCommand, connectString)
*> Create a command builder to generate SQL update, insert, and
*> delete commands based on selectCommand. These are used to
*> update the database.
set commandBuilder to new SqlCommandBuilder(dataAdapter)
*> Populate a new data table and bind it to the BindingSource.
set mytable to new DataTable
set mytable::Locale to type System.Globalization.CultureInfo::InvariantCulture
invoke dataAdapter::Fill(mytable)
set bindingSource1::DataSource to mytable
*> Resize the DataGridView columns to fit the newly loaded content.
invoke dataGridView1::AutoResizeColumns(type DataGridViewAutoSizeColumnsMode::AllCellsExceptHeader)
catch ex as type SqlException
invoke type MessageBox::Show("To run this example, replace the value of the " &
"connectionString variable with a connection string that is " &
"valid for your system.")
end-try
goback.
end method.
end class.