filling the empty fields in datagridview and saving it to SQL data table

[outcast1881]

Hi everyone,
maybe this question was asked before but I checked the forum and and google,I found some results but they were either too complicated or telling nothing about how I can do it...

I am filling my datagridview from my dataset and one of the fields shown in the datagridview is empty(the field is also in my data table) and the user has to fill that field and save it to data table...

but I can't figure it out...I can write into that field in datagridview but how to save it in the data table....I need some advice about how to do it...

Thank you in advance!!!

 

[djjeavons]

Hi

If you are only interested in updating the underlying DataTable with the changes then you can use the AcceptChanges method of your DataTable:

DataTable.AcceptChanges()

although, to be honest, the DataTable should already contain the changes made in the DataGridView.

However, if you are asking how to update the underlying database then you can use an DataAdapter and a CommandBuilder:

[COLOR=#008000]'Create your Connection and DataAdapter objects[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] connection [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]New[/COLOR] SqlClient.SqlConnection([COLOR=#800000]"Your Connection String"[/COLOR])
[COLOR=#0000ff]Dim[/COLOR] adapter [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]New[/COLOR] SqlClient.SqlDataAdapter([COLOR=#800000]"Your SELECT statement that includes the same fields used to retrieve the original data"[/COLOR], connection)
 
[COLOR=#008000]'Create a CommandBuilder based on the DataAdapter[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] commandBuilder [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]New[/COLOR] SqlClient.SqlCommandBuilder(adapter)
 
[COLOR=#008000]'Open the connection and call the Update method of the Adapter[/COLOR]
[COLOR=#008000]'passing the DataTable as the source for the update[/COLOR]
connection.Open()
adapter.Update(dt)
connection.Close()

You must ensure that the SELECT statement used above specifies the field that is used as the Primary Key.

There are of course other ways to achieve the same thing but this is probably the simplest. Although it is not always advisable to use the CommandBuilder as it is really a tool used by Visual Studio to aid when building DataBound objects using the Designer.


HTH

 

[outcast1881]

Thank you for your reply!!!
But the only thing I didn't understand from the code is,is datatable the data table that I want to update is the one in my SQL databank?or I think I have to create a datatable from dataset or datagridview,modify it and save it back to my sql database....If I have to create it from dataset,or datagridview I don't know how to do it...there must be something like "fill" command i guess but...I am new to sql and vb so sorry if I am asking too easy questions,and thank you very much for having replied all my questions.

Regards,

Can

 

[jmcilhinney]

If you bind a DataSet to a DataGridView then when you edit data in the grid the changes are propagated to the table automatically. That's the whole point of data-binding. Saving the changes in the DataTable back to your database is simply a case of calling the Update method of the same DataAdapter or TableAdapter whose Fill method you used to retrieve the data in the first place. You should look up fellow member TechGnome and follow the links in his signature to his ADO.NET tutorials.

 

[cjard]

Here's a brief up/down of how it works:

You have a database on a SERVER
It has a table, with data
There may be 10 columns of data
You only want to work with 8 (or you may wish to work with all 10)
You create a DataTable and TableAdapter pair on the CLIENT
The DataTable has columns for all the data you wish to work with (hence there is no need that your DataTable schema is identical to the database)
The TableAdapter is responsible for bringing data down from the database and sending it back
The database is the permanent storage of the data

To work with data you download it from the server to your client DataTable using a FILL or GETDATA method of the TableAdapter
You edit the data using CONTROLS such as textboxes or datagrids.
At all times the data stays in the DataTable, the controls just view it and allow you to edit it
Connection of the DataTable to the controls is usually managed by a BindingSource
When you add new data or edit existing data, the bindingsource caches a copy of what you are editing within itself.
Upon calling BindingSource.EndEdit the changes are sent back to the DataTable (other things may call this method too, like moving to another record)
Once edited, a DataRow in a DataTable changes state. Rows that have had no edits have a state of Unchanged, when edited the staet becomes Modified. If it is a new addition, it is Added etc
When sending data back to a database, the TableAdapter uses the state information to determine which rows to send
Calling AcceptChanges on the DataTable causes all rows state information to become Unchanged. (If you call AcceptChanges before sending the changes to the database, there will be no changes to send)



You must always bear in mind that your datatables are local caches of database data; you are responsible for filling editing and returning them yourself. They are not in any way magically or automatically linked to the database data.

When asking questions, ensure that you use the correct terminology; if you mean the back end database, avoid referring to it as a DataTable etc.

 

[outcast1881]

Thank you all for the useful information.
regards,

Can