In this tutorial, I'm going to show a fairly simple way to bind a .NET DataGridView to a database. The form designer has some support for data binding, but I found doing it without the form designer is a little easier to understand and implement. Also, when I'm developing a desktop database application, my database schemas are rarely 100% defined, and the form designer doesn't easily support changes to the database.
The database I'm going to use for the example code will be an Access database. I know Access databases aren't the preferred database type for developers - because of their speed and scalability. However, for simple database apps, Access is hard to beat - since you don't need to install any outside database engines. In reality, the concepts shown in this tutorial can be used with any number of databases.
The first thing we'll need to do is generate a connection string to connect to our Access database. For simplicity, the database I'm using doesn't require any authentication. If your database has authentication, MSDN has some great documentation on how to accomplish that.
The connection string is broken up into two parts, a provider and a data source. The provider is the engine we're going to be using - in this case, Microsoft's Jet engine. The data source, for Access, is simply the path to the database file.
Now let's use the connection string and get some data from our database.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";
//create the database query
string query = "SELECT * FROM MyTable";
//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
To keep the code simple, I've left out a lot of error handling. You'll definitely want to surround dAdapter.Fill with some exception handling. That call will fail for many different reasons - for instance the database isn't where the connection string says it is, or the query string is invalid SQL code.
So let's go through what this code is actually doing. The first thing to do is to create the connection string as described above. Then we need an SQL statement to execute on our database. This can be any SELECT statement you want. Next we create an
OleDbDataAdapter which serves as a bridge between our DataTable and our database. An
OleDbCommandBuilder comes next. This beautiful object automatically generates SQL insert, update, and delete statements to rectify changes made to our DataTable. Next we need to make a DataTable to hold the information retrieved from the database. And lastly, we call dAdapter.Fill(dTable) which executes our SQL query and fills dTable with the results.
Now that we have a DataTable filled with database information, let's see how to synchronize it with a DataGridView.
DataGridView dgView = new DataGridView();
//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();
//set the BindingSource DataSource
bSource.DataSource = dTable;
//set the DataGridView DataSource
dgView.DataSource = bSource;
The BindingSource object is what will be keeping our DataTable synchronized with the DataGridView. So we set the DataSource of the BindingSource to dTable, then set the DataSource of the DataGridView to the BindingSource. Now when your program runs, the DataGridView should be filled with the results of your SQL query.
At point, any changes made by the user in the DataGridView will automically be made to the DataTable, dTable. Now we need a way to get the changes back into the database. All you have to do is call the Update function of the OleDbDataAdapter with the DataTable as the argument to accomplish this.
This call will use the OleDbCommandBuilder to create all of the necessary SQL code to synchronize your database with the changes made to dTable. But, when should you call this function? There's lots of different answers to that. If you have a save button, call it when the user pushes save. If you want the database updated in real-time, I like to call it on the DataGridView's Validating event.
The last topic to discuss is error handling. What happens when the user types something in the DataGridView that can't be put in the database - like text where a number is supposed to go? Because you're using DataBinding, it would take a lot of work to get the data type at each column and manually make sure the user typed in the correct thing. Fortunately, the DataGridView has an event,
DataError, to handle that for you. Whenever the user enters something incorrectly, this event will be fired. From this event you can get the row and column index of the cell where the incorrect value was placed, and you can also cancel the event so you don't attempt to update the database with bad data.
That's all the code required to perform two-way data binding with an Access database. If you have any questions or concerns about the above code, leave a comment.
10/20/2007 - 02:35
How can i restict to type text in a datagridview cell and alow only numerical data.
10/21/2007 - 21:20
The next to last paragraph explains what will happen when the user inputs invalid data based on the column's data type. The DataError event will be thrown where you can display the error of your choosing.
This event will only be thrown after the text has been entered and the cell loses focus. If you want to limit characters as the user is typing, you'll have to manually check each character on a key event. I'll be discussing it further in an upcoming tutorial.
04/30/2010 - 01:57
this code is running successfully. But it is not deleting the record or any message.
07/27/2010 - 14:00
the code writer used only the Select query, you can edit the code by using Delete whith the WHERE condition..
10/07/2010 - 04:29
use key press event on grid control
write the code in key press event like,
if(e.keychar<0 || e.keychar>9)
e.Handle=false;
else
e.Handle=True;
10/25/2010 - 06:44
check this link
http://www.mindstick.com/Articles/60574323-7e6c-424e-9265-93f7d534a64c/
10/20/2007 - 21:00
Very helpful info.....Thanks...
10/30/2007 - 19:58
Great article; simple to understand and precise. Many thanks.
11/03/2007 - 15:40
Thanks, this was the first article I found on a professional (i.e. non-designer) way to bind the DataGridView.
12/02/2007 - 09:07
man thanks a lot ! i was getting bugged by this problem ! i scanned msdn , this and that forum . documentation sucks till i landed up here ! wow man u did an excellent job . thanks a lot again
12/31/2007 - 16:32
Nice article. Very straight forward. I too have been looking for this answer for awhile, mostly using Google.
01/09/2008 - 19:42
I had to look for this in thousands of pages and here in few lines u show us how to do it nice and simple.
Kudos to u.
01/15/2008 - 16:40
This article is just what I was looking for. Thanks for writing it!
01/17/2008 - 23:54
WOW. . . . .
01/28/2008 - 09:00
Very good information supplied in an easy read and easy understood way, nice work.
Thank you for your effort.
01/31/2008 - 15:13
really nice... thanks friend
02/04/2008 - 16:15
Fantastic. The grid works great with the update() in the validating event. But, I'm having trouble figuring out when validating fires?
I seem to be able to enter many rows before validating fires.
02/04/2008 - 16:31
Curious, I use almost this exact same method for simple grids, but I don't use a bindingSource. I just call the update method of the dataAdapter. What does the bindingSource give you?
02/14/2008 - 14:46
If you use the binding source you can handle its positionchanged event. It's the best place to trigger the update .
02/16/2008 - 03:31
Thank you.
03/04/2008 - 07:49
Been looking for a clean explanation of how all these different objects fit together for a long time. Thanks!!
03/06/2008 - 10:22
Worked the first time.
Remember, if you have a placed a DataGridView in the form designer you don't need to this line that creates a new one.
Replace 'dgView' with the variable you created.
Thanks for the site.
07/10/2010 - 17:14
yes - thank you for that.
06/21/2011 - 02:43
Hello Sir
03/13/2008 - 13:55
Gracias, muy util esta información... y muy bien explicado.
Thank You!
03/28/2008 - 05:38
Supperayittundu nayinte mone!
08/02/2011 - 01:14
edoooo!!!
04/02/2008 - 10:06
So, How to update / insert data?
thanks
04/02/2008 - 19:22
To update and insert data, all you have to do is modify the DataGridView. Adding rows to the DataGridView will add rows to the database when the OleDbDataAdapter is updated. Any cells modified in the DataGridView will also be updated in the database. You just have to remember to call dAdapter.Update(dTable) to update the database with the changes made to the DataGridView.
08/23/2011 - 22:53
Update works when cell value is entered using keyboard but fails when cell value added via program code as shown below
datagridView1["Name", (dataGridView1.RowCount - 1)].Value = Clipboard.GetText();
how to resolve this?
Thank you for posting this great article, really helps.
04/14/2008 - 18:21
Thanks for been so concise. I spent a lot of time reviewing internet articles and all of them are so bulky. This one is so small and quick to understand
;)
04/24/2008 - 01:43
I also appreciate this article very much, but I have a related question: I created a datatable and bindingsource and bound the table to a listbox via the bindingsource at run time. I also specified the DisplayMember (a string) and ValueMember (that one is the record id) properties of the listbox, but I cannot get the record id from the SelectedValue property of the listbox. What I get is a DataSourceView (or stg.like that) that can't be cast to an int. Any opinions?
05/15/2008 - 14:57
When i put the da.update(dTable) in the click event for a button i get a error saying that "da is not recognized".
How to solve this?
09/04/2008 - 13:55
I'm in the same boat.
If you put all the load/binding code in the Form_Load event, and you put the code "dAdapter.Update(dTable)" in a button_click event then the data adapter (dAdapter) and data table (dTable) are out of scope.
And if you put that code in the Form_Load event, then it just updates your freshly fetched data - giving no time for the user to make any changes.
I love simplicity of this article, but it "simply" doesn't work. I know I am missing something totally basic, but I don't know what it is.
05/16/2008 - 07:01
I did like you wrote, but I still can't update database.
I tried to delete row from datagridview and to update row, but changes only performed to data table not to database.
05/16/2008 - 08:07
Changes won't automatically be saved back to the database. You have to call Update on your OleDbDataAdapter. If you're already doing that then there's another problem I sometimes experience that you might be seeing also.
I'm sure yet what causes it, but sometimes the modified flag on a DataRow doesn't get set to true, even though a value has changed. What I've found is that if you suspend the bindings (bSource.SuspendBindings()), the modified flag will be set and the database will update correctly. Make sure you resume the bindings when you're done.
08/20/2009 - 05:14
i am using this piece of code for update the way u r doing to fill a datatable but the grid is updating only not the db ..plz help..... which query i have to use for update..??
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\153.57.224.22\\cmt\\CMT.mdb";
OleDbDataAdapter dAdapter = new OleDbDataAdapter("",connString);
DataTable dTable = new DataTable();
BindingSource bSource = new BindingSource();
bSource.SuspendBinding();
dAdapter.Update(dTable);
dTable.AcceptChanges();
08/20/2009 - 09:47
The query passed into the OleDbDataAdapter doesn't change from whatever you used to initially request the data. It looks like you forgot to create the OleDbCommandBuilder for your OleDbDataAdapter. This is the object that automatically generates update commands based on the changes that have occurred.
05/18/2008 - 21:40
Thank you.
06/02/2008 - 03:11
thanks for this great article!!!
06/11/2008 - 08:43
Very helpful. Thank you!
06/17/2008 - 00:47
very useful information. Thanks
08/07/2008 - 11:00
PLZ i want to do the opposite :
i want to update datagridview from database in real time
plz help!!!!!!!!
NoussaL@live.fr
08/19/2008 - 03:28
Simple and clear..
Nice
08/19/2008 - 09:55
how do i convert the the result into an array?
example:
i have a COLUMN of words and i want it to convert to an array because i will use it in the string compartion..
08/20/2008 - 05:31
When I do 'dAdapter.Update(dTable);' I get 'Syntax error in INSERT INTO statement'. Any help will be appreciated. Regards, Manne
01/21/2010 - 07:42
try:
cBuilder.QuotePrefix = "[";
cBuilder.QuoteSuffix= "]";
Got this from http://www.pcreview.co.uk/forums/thread-1209233.php
08/29/2008 - 15:39
useful tutorial, thanks a lot
09/07/2008 - 11:36
This article is nice....Thanks for this.
Can any body provide some information how can I bind add custom coloums to datagrid like I can add in wen appication...
Developer4you.com
09/08/2008 - 09:13
i'm trying to do the exact same thing using the MySQL data connector but for some reason i can compile with no errors, but my DataGridView shows nothing!
09/09/2008 - 07:21
ok, figured it out. i didn't need the line 'DataGridView dgView = new DataGridView();' because i already had the DataGridView created in Design View. thanks for writing this up, it was very helpful!!
09/09/2008 - 10:37
Don't forget
bSource.EndEdit();
before
dAdapter.Update(dTable);
or your last modifications will be lost.
05/21/2010 - 00:41
I didn't use endedit method but update was working fine and also i left the gridview with the active row being the row where i made my last modification.
How come this was working? Can anyone help.
Also one more thing, if we use datatable instead of the binding source as the data source for the gridview then how to use endedit as datatable doesn't support such method.
10/23/2008 - 09:08
It's just a great article, I am a professional programmer but I didn't see something like that kind of approach, It looks that you are also a professional coder. I also use to bind it like this. I really like your code. Keep it up
GOD Bless you.
10/28/2008 - 03:16
I tried this code, but when i call:
dataAdapter.update(dataTable);
an exception "Update requires a valid UpdateCommand when passed DataRow collection with modified rows". And i do not know how to solve it
Your answer is welocme.
10/28/2008 - 09:03
I sounds like you might not have an OleDbCommandBuilder associated with the OleDbDataAdapter.
OleDbDataAdapter dAdapter =
new OleDbDataAdapter(query, connString);
//create a command builder
OleDbCommandBuilder cBuilder =
new OleDbCommandBuilder(dAdapter);
11/13/2008 - 07:42
Very interesting to read..I appreciate your knowledge and effort.
Taake care,
11/27/2008 - 04:42
this is what i am looking for.
12/06/2008 - 08:26
REALLY GREAT !!!
For anybody who is using MySQL like me:
After added DataAdapter and CommandBuilder add the following lines:
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.InsertCommand = builder.GetInsertCommand();
as you can read on:
http://forums.mysql.com/read.php?38,56936,60594#msg-60594
Cheers,
Marco D.
07/17/2010 - 03:42
i tried it on access database
but it doesn't delete record from databse
12/11/2008 - 19:41
i try the code i get the error
“Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.”
any help is welcome
12/12/2008 - 09:53
It sounds like it doesn’t know how to track changes made to the DataGridView. Does your database table have a primary key?
01/25/2010 - 19:38
I've set a primary key and can successfully change data to fields with a 'number' data type.
However, when I change the data in the fields with 'text' data type, the error 'Syntax error in UPDATE statement' comes up when the 'dAdapter.Update(dTable)'
is executed.
Is there a way to check what query syntax was used by OleDbDataAdapter ?
Thanks in anticipation of your reply.
03/17/2009 - 12:55
Change the query you are using to populate the datatable to include the primary key in your table.
10/22/2010 - 04:33
Hello...
I dont want the primary key column to be visible in my datagrid. is there any other way to exclude from the query. thanks for your effort
01/05/2009 - 22:41
a good job! thanks for your artical.
01/19/2009 - 00:07
I have been banging my head against the wall for days trying to figure out how to do this. I can't believe how poorly documented it is.
Your concise description is perfect.
03/04/2009 - 05:24
When i am trying to use DataGridView control it is not showing the data.Data is comming to the DataSet and i have assigned DataSource if the DataGridView control is DataSet.But it is not displaying Data.I have tried with BindingDataSource also still same problem.Please help me in this regard
04/21/2009 - 07:52
bSource.DataSource = dTable;
dgView.DataSource = bSource;
Make sure you have both those rows included. I just had the same error until i noticed i forgot to add one row :)
07/10/2010 - 17:21
See FishJD's comment from above.
Worked the first time.
Remember, if you have a placed a DataGridView in the form designer you don't need to this line that creates a new one.
DataGridView dgView = new DataGridView();
Replace 'dgView' with the variable you created.
Thanks for the site.
05/21/2009 - 04:36
Hi
I have followed your example without a hitch, however,when doing the update in the gridviews validating event
private void dataGridView1_Validating(object sender, CancelEventArgs e)
{
bSource.EndEdit();
dAdapter.Update(dTable);
}
The following errors are encountered:
Error 1: The name 'bSource' does not exist in the current context
Error 2: The name 'dAdapter' does not exist in the current context
Error 3:The name 'dTable' does not exist in the current context
I know there is something simple to fix here, just don't know what it is.
The article is extremely helpful, thanks
05/21/2009 - 08:52
Where did you declare bSource, dAdapter, and dTable? You'll probably have to make these member variables of the class in order to use them in other functions.
05/22/2009 - 03:25
I placed the code in the click event of a button, as is, how would you declare the bSource, dAdapter, and dTable somewhere else, as the objects are instantiated with parameters ie
OleDbDataAdapter dAdapter = new OleDbDataAdapter(strSql, con);
AND
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
If i can get my head around this it will go along way to helping with other apps that i am coding
Much appreciated
Chris
PS I am coming from a VB6 background and finding the transition to C# a bit challenging
CODE:
private void btnSearch_Click_1(object sender, EventArgs e)
{
txtSearch.BackColor = Color.White;
if (txtSearch.Text.Trim() != "")
{
try
{
OleDbConnection con = new OleDbConnection(clsConnSrt.GetConnectionString("PastelCostingSlip.Properties.Settings.PastelCostingSlipConnectionString").ToString());
string strSql;
strSql = "SELECT * FROM Sheet1 WHERE PlantDescription LIKE '" + txtSearch.Text.Trim() + "%" + "'";
OleDbDataAdapter dAdapter = new OleDbDataAdapter(strSql, con);
//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
con.Open();
//fill the DataTable
dAdapter.Fill(dTable);
//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();
//set the BindingSource DataSource
bSource.DataSource = dTable;
//set the DataGridView DataSource
dataGridView1.DataSource = bSource;
con.Close();
con.Dispose();
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
}
05/22/2009 - 08:33
What's happening is you're declaring the variables inside the search event handler, which means they won't be available anywhere else except inside that function. All you need to is move the declaration to the class. Here's an example.
{
private DataTable dTable;
...
private void btnSearch_Click_1(object sender,
EventArgs e)
{
this.dTable = new DataTable();
...
}
}
Here the DataTable is declared as a private member of the class, then set in the search event handler. Now any function inside MyClass can reference the dTable variable. Of course, you'll have to do this for all the variables you'd want to reuse, not just dTable.
06/01/2009 - 09:10
Any idea how to do this when you want to display the data as a pivot table? Let's say I have a SQL table with normalized data and they are stored into the data table as key's. and then I want to display values not key's in the datagridview and then pivot one of the key's as column header?
thanks for any help! I have been online all day trying to find a solution!
05/12/2010 - 06:48
hi...
its best ... it solved my problem at once....
i was tring hard to insert the records that are searched through query...bt now finally its resolved by your guaidance and a little modification of mine
can any one plz help me ... i want to make my some columns of data grid read only and just one write only...
and then saving all the data back into the access table making sure that no value is missing....
05/28/2010 - 00:52
'bSource' should be the current instants of oledb Adapteroes , hence it's not exist in the current context
06/13/2009 - 15:35
Hi, thanks for the tutorial.
I can't get it to work. It doesn't display anything. I copy everything correctly but when I run the app it doesn't display anything. What could be the problem?
06/19/2009 - 15:28
Hi
This is by far the best Tutorial I've read on DataGridView but one thing is maybe missing.
Using the code above the DataGrid does not appear - I had to create a DataGrid by dragging the control onto my Form. Any ideas why the grid did not display please.
Thanks
David
06/19/2009 - 15:36
This tutorial assumes a DataGridView already exists. It does not cover how to create and place one on a control.
06/25/2009 - 15:34
Hi
I don't think tutorials should assume anything - if the tutorial assumes you know something, then why would you even write tutorial at all. No one can assume anything if its aimed at many different programmers at different levels of knowledge.
But great tutorial all the same.
cheers
David
06/25/2009 - 15:40
David, that isn't quite true, if you assume nothing then you have to start from the ground up everytime you create any tutorial. You have to assume some knowledge or you don't have real starting point for the post.
I would love every tutorial to cover everything from the ground up but that isn't practically possible and we have plenty of other tutorials on the basics of most topics (not all).
04/22/2010 - 12:59
Hey if u r visiting this page....then its not expected to teach u all d things. For ur requirement author should also write how to initialize a new object. Why is he putting private keyword and all crap.....
So please don't comment the way you have done.
By the way great job done by Author.
08/23/2009 - 11:45
Incorrect syntax near the keyword 'where'.
06/22/2009 - 22:03
Very great Article..
very simple to understand...
Thanks thanks and thanks to you for this article
07/22/2009 - 11:25
Very good article...
Can I ask, if you have a DataGridView and add a new record through the bindingsource how do you get the primary key back to display in the grid.
The primary key is an autonumber?
07/23/2009 - 09:20
The primary key can be an autonumber - it depends on your specific database schema. In most cases, however, it is. Unfortunately, as far as I know, it's not extremely easy to get the new id back. You have to either re-fill the DataTable
dAdapter.Fill(dTable)and rebind it, or request the id of the new entry and manually update the binding source. It seams like there should be an easier way, I just don't know what it is.01/09/2012 - 12:22
sir,if i am inserting data into datagridview and after i dont want to save that.then what can i do.
07/31/2009 - 16:40
Hello,
I really do like your article, but I have a question. What would be the best way to customize your columns. For example, when I bind a DataGridView to a DataTable the column headers are the field names in the database that filled the DataTable. How would I change the column header name in the DataGridView?
11/18/2011 - 12:49
Use Aliases, problem solved
08/05/2009 - 19:51
I am very grateful to you guys for your efforts thanx this has been solved my problem
09/28/2009 - 04:16
I created a dataGridView and named it dgView but the grid doesn't show anything(gray square)
11/05/2009 - 14:00
Got error on the following two lines:
bSource.DataSource = dTable;
//set the DataGridView DataSource
dgView.DataSource = bSource;
11/19/2009 - 07:35
Could you, please, help me to solve this:
dAdapter.Update(dTable);
when my dTable depends on user choice. I have one Dataset with several tables, and name of the chosen Tableapears in Label1.Text.
How to save changes from dataGridView to Database?
02/11/2010 - 03:56
Hello there. Im writing an application for an RFID reader to read RFID tags and save their IDs in a database. The program will read the IDs automatically and display it in a datagridview. My problem is my datadapter.update method doesnt work, resulting in my database doesnt get updated. I understand that the method needs an event from the datagridview to be fired before it can be called. From my program, I dont think any event from the datagridview is fired when IDs are read from the reader and displayed on the datagridview since all are automatic (I maybe wrong here). Are there any workarounds to use the datadapter.update method without any previous firing event from the datagridview?
Thanks...
02/11/2010 - 06:27
I think you have to EndEdit() on the binding source before you update so that any changes are stored.
02/11/2010 - 20:09
hey, thanx for the advice. i'll try it out...
02/27/2010 - 18:09
I have tried out the above code. It does display the data from database to datagridview. But it didn't update the changes made in datagrdiview to database.
So, I made a Primary key in my database and my database was successfully updated via datgridview.
My question is:
Is it possible to update database via datagridview without using Primary key?
05/28/2010 - 00:54
We can updata database using dataGrid by using Unique Key, instead of Primary key by using SP
03/08/2010 - 02:34
Hi,
Thank you for this great tutorial. I use .net programming occasionally. I had never understood the purpose of adapter, command and table objects. But you have stated it very clearly with example.
My concern is why do we have to associate command object to the adapter. Shouldn't this be implicitly done by the adapter? As you know... adapter is bridging database and our data table or a data source.
Thank you and regards,
Rashid.
03/11/2010 - 17:22
Hi,
Thanks for the great article.
My question:
What is MyTable? Where and how should I declare it?
Thank you.
Regards
Bernkopf
04/08/2010 - 14:18
hello...i have a problem
..while connecting datagridview with the database...during connection...when i click on new connection ....no new window is opened....plzzz help...
04/30/2010 - 01:57
this code is running successfully. But it is not deleting the record or any message.
05/02/2010 - 06:12
hello i have a function that return a dataset :
public DataSet dataSetReq(string myQuery,string myTab)
{
myCommand = new OleDbCommand(myQuery, myConnection); //ExecuteSEL(myQuery)
dAdapter = new OleDbDataAdapter(myCommand);
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
mydSet = new DataSet(myTab);
dReader = myCommand.ExecuteReader();
dReader.Close();
dAdapter.Fill(mydSet,myTab);
return mydSet;
}
and i have a piece of code in the onclick event of my botton :
BindingSource bSource = new BindingSource();
bSource.DataSource = DecGlob.GstBD.dataSetReq(DecGlob.Req, "Fournisseur").Tables[0];
this.dataGridViewFour.DataSource = bSource;
DecGlob.GstBD.dAdapter.Update(DecGlob.GstBD.dataSetReq(DecGlob.Req, "Fournisseur"), "Fournisseur");
the update deosn't work please help me !!!
05/22/2010 - 16:07
I'm very happy and grateful for this method that programmatically binds a datagridview to a data source. Can anyone tell me what I'd have to do (in terms of the original tutorial) to replace one of the columns, which presently defaults to a DataGridViewTextBoxColumn, with the same data but as a DataGridViewComboBoxColumn? The combobox will be a small fixed set of text values. I am using an Access 2000 database, and my query is "SELECT * FROM Products". The Access text field is named "UnitsType". Two of the valid values for UnitsType are "drop" and "vcap".
06/25/2010 - 07:54
Hi everyone!
I want to save in database from a GridView in C# on WindowForm. Any Help please?
07/17/2010 - 03:28
Here is working class with fewer modifications..
public partial class Form1 : Form
{
private OleDbDataAdapter dAdapter;
private DataTable dTable;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employee.mdb");
conn.Open();
String query = "SELECT * FROM tblEmployee";
//create an OleDbDataAdapter to execute the query
dAdapter = new OleDbDataAdapter(query, conn);
//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
//the DataGridView
//DataGridView dgView = new DataGridView();
//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();
//set the BindingSource DataSource
bSource.DataSource = dTable;
//set the DataGridView DataSource
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
// Update button
private void button1_Click(object sender, EventArgs e)
{
dAdapter.Update(dTable);
}
}
07/17/2010 - 03:29
note: i have used visual Data Grid view so lines related to dataGridView are commented
08/19/2010 - 03:28
wow its really helpful.thannnnnks.
but does it work for other kinds of controls too.f.exa textbox. mikeehips@gmail.com
tnx
08/27/2010 - 00:01
just learning c#
Have the update on a button_click as below but keep getting this error message
System.Data.OleDb.OleDbException was unhandled
Message=Syntax error (missing operator) in query expression '((EID = ?) AND ((? = 1 AND Tag IS NULL) OR (Tag = ?)) AND ((? = 1 AND BID IS NULL) OR (BID = ?)) AND ((? = 1 AND Sex IS NULL) OR (Sex = ?)) AND ((? = 1 AND Size IS NULL) OR (Size = ?)) AND ((? = 1 AND Grp IS NULL) OR (Grp = ?)) AND ((? = 1 AND FeedQty IS '.
{
public partial class Form1 : Form
{
private OleDbDataAdapter dAdapter;
private DataTable dTable;
private OleDbConnection connection;
private OleDbCommandBuilder cBuilder;
private BindingSource bSource;
private string queryString;
String connectionString;
public Form1()
{
InitializeComponent();
}
static private string GetConnectionString()
{
return @"Provider=Microsoft.JET.OLEDB.4.0;"
+ @"data source=D:\Development\C#\TestMDB\TestMDB.mdb;";
}
private void Form1_Load(object sender, EventArgs e)
{
// create the connection string
connectionString = GetConnectionString();
connection = new OleDbConnection(connectionString);
queryString = textBox1.Text;// Select * From tblCalf
// create an OleDbDataAdapter to execute the query
dAdapter = new OleDbDataAdapter(queryString, connection);
// create a command builder
cBuilder = new OleDbCommandBuilder(dAdapter);
// create a datatable to hold query results
dTable = new DataTable();
// fill DataTable
dAdapter.Fill(dTable);
// the DataGridView
//DataGridView dataGridView1 = new DataGridView();
// BindingSource to sync DataTable and DataGridView
bSource = new BindingSource();
// set the BindingSource DataSource
bSource.DataSource = dTable;
// set the DataGridView DataSource
dataGridView1.DataSource = bSource;
// if you don't want to show all columns
//dataGridView1.Columns[0].Visible = false;
//dataGridView1.Columns[2].Visible = false;
}
//update mdb
private void button2_Click(object sender, EventArgs e)
{
dAdapter.Update(dTable);
}
Regards Dave
08/27/2010 - 03:46
Ok I would hate to say how long I've spent on this but I just deleted the 3 date/time columns(that were empty) and it now works. Have added them back in and it appears to be working, but I won't hold my breath.
Dave
09/05/2010 - 22:09
many thanks to the author. Great article.
I used it for a single user and it works great.
Can i also use this solution for a database which is used concurrently. I need the functionality that two users can access the db at the same time. How can i update the database and the other users view so that data keeps consistent?
Thanks for a feedback in advance
09/09/2010 - 16:01
How can I add logic to modify the contents of a cell as the "fill" is occurring? I can't change my query to accomplish this. Shouldn't there be some event I can put some code in? (I've converted my database from Access to Foxpro. Access stores all text fields trimmed, but Foxpro has fixed length fields. I want to trim the blanks off of the text fields so that when the user edits the datagridview, they won't find all those trailing blanks out there.) I've searched for one, but can't find one I can make sense of.
09/09/2010 - 16:35
Check out the CellFormatting event. It looks like it will be fired whenever the cell needs painted, so you can completely control how the value looks when it gets displayed.
DataGridViewCellFormattingEventArgs e)
{
e.Value = e.Value.ToString().Trim();
}
09/09/2010 - 21:33
Works great! Thanks! But had a wrinkle, solved in a couple ways. When this executes against columns that have a date, the Foxpro date gets converted to a C# 'datetime' with the result that it shows a '12:00 am' (or the like) as part of the date. So I must exclude my date fields. 1st way was this:
dgridCust.Columns[e.ColumnIndex].Name.ToLower() == "CDateAdded".ToLower())
return; // do not trim dates (it causes time to appear
e.Value = e.Value.ToString().Trim(); // get rid of foxpro's trailing blanks
return;
09/09/2010 - 21:53
Another quirk I discovered is when my grid allows new records to be added. In that case, my CellFormatting event code (the e.value=) has to be preceded by
return;
09/10/2010 - 11:07
I figured there would be some quirks and corner cases. I'm glad you got it figured out!
09/15/2010 - 09:20
Is it possible to bind, and change in database through a datagridview, joined tables?
For example, table A has a foreign key to ID column of table B. But, while showing table A in DataGridView, I want to show B.Name, associated with that ID instead just that number which is useless to the user. That column would be read only, but I want to be able to change the rest of the DataGridView and update the changes to the database. Is it possible to do with adapters, command builder etc..?
09/15/2010 - 10:48
Have you tried adding the JOIN to the statement passed into the OleDbDataAdapter? I wouldn't be surprised if everything "just worked".
09/16/2010 - 06:30
It didn't, hence the question :)
"Dynamic SQL generation is not supported against multiple base tables."
The thing is, I don't even want to change the other table, just display the name column, so I don't need him to generate sql for multiple tables, just to refresh name column ih one changes foreign key..
Is it possible to use several adapters, one for each table? DataSource property takes only one source, but maybe it is possible to combine several datagridviews, or something..? Any suggestion on what to try is welcome..
I already tried to create two datagridviews, each with its source and adapter, and then combining its columns, but it is not allowed, at least not the way I tried it.. :\
(edit: sorry for not hitting reply)
11/22/2010 - 13:32
Very nice article... But could not run it. Got two type of errors. If i edit an existing row i get : "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." error message. If i attemting to add a row into the datagridview i get : "Syntaxfehler the INSERT INTO-Command" What im doing wrong?
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public DataTable dTable;
public static string connString = String.Format("Provider={0}; Data Source={1}; Jet OLEDB:Database Password={2}", "Microsoft.ACE.OLEDB.12.0", "dbMaterial.mdb", "test");
public static OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Ruckstande", connString);
public static OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
public static BindingSource bSource = new BindingSource();
private void Form1_Load(object sender, EventArgs e)
{
//create the connection string
//create a command builder
//create a DataTable to hold the query results
dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
//the DataGridView
//DataGridView dgView = new DataGridView();
//BindingSource to sync DataTable and DataGridView
//set the BindingSource DataSource
bSource.DataSource = dTable;
//set the DataGridView DataSource
dataGridView1.DataSource = bSource;
}
private void button1_Click(object sender, EventArgs e)
{
dAdapter.Update(dTable);
}
}
11/22/2010 - 15:42
Does the Ruckstande table have a column configured as the primary key?
11/22/2010 - 16:34
Thanx for the advise... Now it has! But the Syntax-Error in the UPDATE and INSERT-Statement still appears.
11/23/2010 - 07:20
Problem solved by allowing null values to the collumns and changing all collumn datatypes to string. I have also deleted all rows with null value cells from the database. It Works great now. As i know the causes of the previous problems, it should not be a great deal to handle with null and/or other type of values. Thank you very very much for the Tutorial!
12/11/2010 - 14:28
is it for binding of data from datagridview 2 database?
01/20/2011 - 10:38
Hi,
I think the article is great but I have one small problem. I'm not actually using a datagrid but have created an adapter, a datatable and a commandbuilder as described in the article but intend to modify the data manually in code as follows (btw - I'm using MS Access DB):
{
MessageBox.Show(tableAllControllers.Rows[controllerID]["Name"].ToString());
tableAllControllers.Rows[controllerID]["Name"] = newName;
tableAllControllers.AcceptChanges();
dAdapter.Update(tableAllControllers);
}
Although the data appears to have changed at runtime, the actual database has not changed.
I read a comment earlier regarding primary keys but wasn't quite sure if this affects me. I can't make changes to the schema of the database anyway as my application is intended only to access it in it's current form.
Any help would be much appreciated!
01/21/2011 - 09:57
Just a quick update on my post. I tried the same method on the Northwind database to exclude my database as the point of failure but the problem still occurs. There must be something extra that I need to do.
Any help would be fantastic!
01/21/2011 - 01:52
Could anyone help me for checking this code? It doesn't work. I try to find the way to change the forecolor for some field, but it's still change for all columns.
DiffLY = Convert.ToDecimal(thisReader["Diff_LastYear"].ToString());
if (DiffLY <= 0)
{
GridView1.Columns[4].ItemStyle.ForeColor = ColorTranslator.FromHtml("#FF0000"); //change into red
}
else
{
GridView1.Columns[4].ItemStyle.ForeColor = ColorTranslator.FromHtml("#000000"); //change into black
}
Any help will be appriciate.
06/27/2011 - 01:06
You are updating the color for the column, not an individual cell. There are multiple ways to refer to a cell, but here is an example that would go through the rows, and then the cells. This makes the assumption you have populated the DataGridView already. So you would populate it, then run this code. Also make sure you have the 'Color' reference in System.Drawing.
using System.Data;
using System.Drawing;
...
decimal diffLY = 0.0;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if(row.IsNewRow)
continue;
diffLY = decimal.Parse(row.Cells["colName1"].Value.ToString());
if(diffLY <= 0)
row.Cells["colName1"].Style.ForeColor = Color.Red;
else
row.Cells["colName1"].Style.ForeColor = Color.Black;
}
05/10/2011 - 01:35
This is exactly what I was looking for. Thank you for making it so easy to understand!
05/14/2011 - 06:19
while updating the DataAdapter, it throw an exception telling:
"Update require a valid UpdateCommand when passed DataRow collection with modifiec rows."
it seems it's very simple but I cant find any solution for thins.
05/19/2011 - 00:44
hi...
how to add paging in datagridview using c#....
06/01/2011 - 11:13
Why if I call the adapter.update(dt); do I get a invalid operation error
06/07/2011 - 06:14
In windows Application I have 4 columns in datagridview ie,
1.Student Name.
2.Present
3.Absent
4.Leave.
For student name i have created just column,for present, absent and leave i used checkbox where they can click if present or absent or leave.. Student name i have to bind from backend, Am not getting how to bind only one column of table from backend to only column of datagridview.. please help me.. if possible please mail me.. shrusharan@gmail.com,.. thanks in advance
06/07/2011 - 14:04
Eddy can't dance!
06/07/2011 - 14:16
Hey me!
06/13/2011 - 15:34
Good article. Saved me a lot of time..
07/20/2011 - 23:01
thx..one of the best code
07/20/2011 - 23:03
nice..hey tell me that is this a only way in c# to connect with the databases?
07/23/2011 - 11:16
It's nice and very helpful..
but it's not working when I include a row selection condition in the query.. like,,
"select * from employee where dept=Sells"..
..
when the attribute type is int or float it works fine..
like..
"select * from employee where salary=10000"..(works fine)
but the problem becomes when attribute value be varchar..
like 'name' or 'dept' etc.
Help me plz...
Thanks in advance..
09/11/2011 - 16:47
Add quotes around the string values:
"select * from employee where dept='Sells'"
07/24/2011 - 12:34
I am devleoping a C# based windows application. I am using Datagridview which retrieves data from table. It has columns like "Item ID","Item Name" and "Unit Price".
When the user changes the Unitprice and ItemName, that has to be updated in the table. The table has two primary keys on two columns ItemGroupID, ItemID.
The Table design is as follows:
ItemGroupID-->Primary Key Column
ItemID-->Primary Key column
ItemName
Weight
UnitPrice
This is my code for the save button
obcon.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "/MsBakery.mdb";
OleDbCommand obcmd = new OleDbCommand();
DataSet ds = new DataSet("ItemMaster");
obcon.Open();
string SQL;
SQL = "";
SQL = "select ItemID,ItemGroupID,UnitPrice from ItemMaster";
OleDbDataAdapter da = new OleDbDataAdapter(SQL, obcon);
DataTable tb=new DataTable("ItemMaster");
//tb = ds.Tables["ItemMaster"];
da.Fill(tb);
foreach(DataGridViewRow row in dataGridView1.Rows )
{
DataRow dr;
dr = null;
if (!row.IsNewRow)
{
// dr[0] = row.Cells[0].Value.ToString();
dr[0] = row.Cells[0].Value.ToString();
dr[1] = cmbItemGroup.SelectedValue;
dr[2] = row.Cells[3].Value;
// tb.Rows.Add(dr);
string SQLt;
SQLt = "update ItemMaster set UnitPrice=@UnitPrice where ItemGroupID=@ItemGroupID and ItemID=@ItemID";
da.UpdateCommand = obcmd;
da.UpdateCommand.Parameters.Add("@Unitprice", OleDbType.Decimal, 0, "Unitprice");
da.UpdateCommand.Parameters.Add("@ItemID", OleDbType.VarChar, 0, "ItemID");
da.UpdateCommand.Parameters.Add("@ItemGroupID", OleDbType.Integer, 0, "ItemGroupID");
da.UpdateCommand.CommandText = SQLt;
da.UpdateCommand.Connection = obcon;
//ds.Tables.Add(tb);
//DataRow[] modRows = ds.Tables[0].Select(null, null, DataViewRowState.CurrentRows);
//da.Update(ds.);
}
obcon.Close();
}
Please help me how to update the values in database. I am not using any binding control in the form. I am manually binding the data to the datagridview using dataset.
Thank you.
With Regards,
ArunV
07/30/2011 - 02:40
how to do this using sql server 2000 database. when i convert the given code to sql its not working. is there any changes to be made.pls answer this
08/12/2011 - 05:31
OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommand, connection);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Create the Insert, Update and Delete commands.
adapter.InsertCommand = new OleDbCommand(
"INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (?, ?)");
adapter.UpdateCommand = new OleDbCommand(
"UPDATE Customers SET CustomerID = ?, CompanyName = ? " +
"WHERE CustomerID = ?");
adapter.DeleteCommand = new OleDbCommand(
"DELETE FROM Customers WHERE CustomerID = ?");
// Create the parameters.
adapter.InsertCommand.Parameters.Add("@CustomerID",
OleDbType.Char, 5, "CustomerID");
adapter.InsertCommand.Parameters.Add("@CompanyName",
OleDbType.VarChar, 40, "CompanyName");
adapter.UpdateCommand.Parameters.Add("@CustomerID",
OleDbType.Char, 5, "CustomerID");
adapter.UpdateCommand.Parameters.Add("@CompanyName",
OleDbType.VarChar, 40, "CompanyName");
adapter.UpdateCommand.Parameters.Add("@oldCustomerID",
OleDbType.Char, 5, "CustomerID").SourceVersion =
DataRowVersion.Original;
adapter.DeleteCommand.Parameters.Add("@CustomerID",
OleDbType.Char, 5, "CustomerID").SourceVersion =
DataRowVersion.Original;
08/14/2011 - 13:38
I am getting this error msg while updating as well as Deleting the data.
"Object reference not set to an instance of an object"
Please help...!
Thanks in advance....
08/29/2011 - 01:12
Thanks for the awesome tutorial!!
09/08/2011 - 04:24
Nice tutorial but I would be happy if anyone explain how to manuplate columns and rows in datagridview as I am new to c#
09/08/2011 - 17:15
Excellent!!! ;-)
09/27/2011 - 05:15
Very cool article, it helped me a lot.
Thanks
10/15/2011 - 08:19
This is nice article. It's help me lot thanks for sharing with us. Here is also a nice article which helped me to complete my task. Check this Url...
http://www.mindstick.com/Articles/30148105-6777-467a-9ecc-82a2118387d0/?Insert%20Update%20Delete%20Records%20in%20CSharp%20.NET
It might be useful for you.
10/18/2011 - 15:20
Im back to programming now since left them out about 5 months ago. Well, Good tutorial, clear explanation.. Thanks...
11/16/2011 - 19:20
Is there a way to have the Datatable automatically pick up any changes that happen to the data on the DB? Or are they static until refreshed?
megastiv@stiv.com
11/29/2011 - 12:36
Hi
Thank You
12/07/2011 - 13:02
Hi,
I get error when I add these two lines.
//the DataGridView
DataGridView dgView = new DataGridView();
//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();
There is gridview not datagridview. Iam doing this on VS2010.
Error
"Name or Typespace name missing" need to add reference.
Do I need to add any reference.
Thanks
12/08/2011 - 09:36
Both types are in the System.Windows.Forms namespace. If you created a Windows Forms applications, you should already have the reference. If you're creating a WPF application, then you should use the WPF DataGrid. This tutorial does not apply to WPF applications.
01/22/2012 - 06:10
i want to apply query on datagrid view acc to user need..
for eg: user enter name in textbox and corresponding to dis textbox value related rows are selected and data is fetch in grid view..
pls help.
its urgent..
12/10/2011 - 04:02
Thanks. Give me a best solution for my problem.
01/18/2012 - 05:13
My problem is how to keep the datagridview updated when the underlying database is changed by another user
01/23/2012 - 11:28
i want to apply query on datagrid view acc to user need..
for eg: user enter name in textbox and corresponding to dis textbox value related rows are selected and data is fetch in grid view..
pls help.
its urgent..
01/29/2012 - 03:33
do there any directives needed to include the report in the window service....im using visual studio 2010...
gettin an error like"Error 1 The dataset ‘DataSet’ refers to the data source “”, which does not exist"
02/05/2012 - 11:53
A good tutorial on Datagridview
http://csharp.net-informations.com/datagridview/csharp-datagridview-tutorial.htm
yang.
02/06/2012 - 12:27
Hey Guys,
I tried a lot but each time when I am executing my da.Update(ds) I am getting error
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
Here is my code
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
namespace GridOperation
{
public partial class Form1 : Form
{
String constr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
private SqlDataAdapter da;
private SqlConnection con;
private BindingSource bs;
DataSet ds;
public Form1()
{
InitializeComponent();
}
private void BtnGetDetail_Click(object sender, EventArgs e)
{
ds = new DataSet();
con = new SqlConnection(constr);
da = new SqlDataAdapter("SELECT * FROM Tbl_EmpDetail", con);
da.Fill(ds, "Tbl_EmpDetail");
SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(da);
bs= new BindingSource();
bs.DataSource = ds.Tables["Tbl_EmpDetail"];
GridEmpDetail.DataSource = bs;
}
private void BtnUpdateDetail_Click(object sender, EventArgs e)
{
try
{
da.Update(ds.Tables[0]);
}
catch (Exception exceptionObj)
{
MessageBox.Show(exceptionObj.Message.ToString());
}
}
}
Please let me know/mail me if I am doing something wrong
02/06/2012 - 13:44
I think your database table, Tbl_EmpDetail, just needs a primary key set. You have to do this through your database administration tools.
02/06/2012 - 15:50
If this is a MS SQL database the primary key is set using the identity property.