C# Tutorial - Binding a DataGridView to a Database

How can i restict to type text in a datagridview cell and alow only numerical data.

Very helpful info.....Thanks...

Great article; simple to understand and precise. Many thanks.

Thanks, this was the first article I found on a professional (i.e. non-designer) way to bind the DataGridView.

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

Nice article. Very straight forward. I too have been looking for this answer for awhile, mostly using Google.

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.

This article is just what I was looking for. Thanks for writing it!

WOW. . . . .

Very good information supplied in an easy read and easy understood way, nice work. Thank you for your effort.

really nice... thanks friend

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.

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?

Thank you.

Been looking for a clean explanation of how all these different objects fit together for a long time. Thanks!!

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.

Gracias, muy util esta información... y muy bien explicado.

Thank You!

Supperayittundu nayinte mone!

So, How to update / insert data?

thanks

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

;)

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?

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?

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.

Thank you.

thanks for this great article!!!

Very helpful. Thank you!

very useful information. Thanks

PLZ i want to do the opposite : i want to update datagridview from database in real time plz help!!!!!!!!

NoussaL@live.fr

Simple and clear.. Nice

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..

When I do 'dAdapter.Update(dTable);' I get 'Syntax error in INSERT INTO statement'. Any help will be appreciated. Regards, Manne

useful tutorial, thanks a lot

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

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!

Don't forget bSource.EndEdit();

before dAdapter.Update(dTable);

or your last modifications will be lost.

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.

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.

Very interesting to read..I appreciate your knowledge and effort. Taake care,

this is what i am looking for.

REALLY GREAT !!!

For anybody who is using MySQL like me:

After added DataAdapter and CommandBuilder add the following lines:

adapter.UpdateCommand = builder.GetUpdateCommand();
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.

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

a good job! thanks for your artical.

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.

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

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

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?

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

Very great Article.. very simple to understand... Thanks thanks and thanks to you for this article

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?

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?

I am very grateful to you guys for your efforts thanx this has been solved my problem

I created a dataGridView and named it dgView but the grid doesn't show anything(gray square)

Got error on the following two lines:

        //set the BindingSource DataSource
        bSource.DataSource = dTable;

        //set the DataGridView DataSource
        dgView.DataSource = bSource;

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?

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...

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?

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.

Hi, Thanks for the great article. My question: What is MyTable? Where and how should I declare it? Thank you. Regards Bernkopf

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...

this code is running successfully. But it is not deleting the record or any message.

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 !!!

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".

Hi everyone! I want to save in database from a GridView in C# on WindowForm. Any Help please?

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); }

}

wow its really helpful.thannnnnks. but does it work for other kinds of controls too.f.exa textbox. mikeehips@gmail.com tnx

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 '.

namespace TestMDB
{
    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

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

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.

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..?

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)

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?

namespace WindowsFormsApplication1
{
    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);
        }
    }

is it for binding of data from datagridview 2 database?

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):

public void modifyControllerName(int controllerID, string newName)
        {
            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!

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.

  decimal DiffLY;

  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.

This is exactly what I was looking for. Thank you for making it so easy to understand!

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.

hi... how to add paging in datagridview using c#....

Why if I call the adapter.update(dt); do I get a invalid operation error

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

Eddy can't dance!

Hey me!

Good article. Saved me a lot of time..

thx..one of the best code

nice..hey tell me that is this a only way in c# to connect with the databases?

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..

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

OleDbConnection obcon = new OleDbConnection();
            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

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

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....

Thanks for the awesome tutorial!!

Nice tutorial but I would be happy if anyone explain how to manuplate columns and rows in datagridview as I am new to c#

Excellent!!! ;-)

Very cool article, it helped me a lot. Thanks

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.

Im back to programming now since left them out about 5 months ago. Well, Good tutorial, clear explanation.. Thanks...

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

Hi Thank You

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

Thanks. Give me a best solution for my problem.

My problem is how to keep the datagridview updated when the underlying database is changed by another user

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..

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"

A good tutorial on Datagridview

http://csharp.net-informations.com/datagridview/csharp-datagridview-tutorial.htm

yang.

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;
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

can any one let me know

how to update and delete records from ms access database in windows application in c sharp

Inserting,deleting,updating and editing records to a MS Access database using select checkbox

My WinForms application uses the below structure to populate a DataGridView. This all works well and follows the same structure described in this excellent tutorial. I have two design goals that I am struggling with however.

?1) I want to separate Form code from the Database code. (n-Tier concept). This is my primary question currently.

2)I want to make DataGridView and Database handling generic so it can be driven by meta-data, rather than hardcoding to specific tables or form views.

My current question is; what are best practices to persist the database objects (DataAdapter) so I can write back to the database without creating all of the structure over again for each write. Once the call to Getdata to create the dataset is complete, I suspect my data adaptor and command builder (which will get added once I figure out how to structure the code) get hauled off with garbage collection. I am guessing the answer will include creating an instance of some data object but would like to get some expert opinions on how to best handle this.

Class frmMain • Creates instances of DataGridView and BindingSource

Btn_Click events • Calls BuildDataGrid if DataGridView is empty • Moves current DataGridView to top of form

BuildDataGrid(DataGridView, bindingSource) • Gathers external meta-data, adds columns and sets properties to a DataGridView • Sets DataGridView.DataSource = bindingSource; • Calls GetData (sql string) which returns a dataset • Sets bindingSoruce.DataSource = ds; • Sets bindingSource.DataMember = "Table";

Class appData

Getdata(sql) • Creates OleDbConnection object • Creates OleDbDataAdapter • Creates DataSet • Opens dbConnection • Uses DataAdapter to populate dataset • Closes dbConnection • Returns DataSet

Thank you.

i cant see new record in datagridview.my code is: SqlConnection con = new SqlConnection("server=pc1\\sqlexpress;attachdbfilename=d:\\Data\\Data1\\data1\\yahya\\Projects\\database_employ\\database_employ\\employ.mdf;integrated security=true"); con.Open(); SqlCommand cm = new SqlCommand(); cm.Connection = con; cm.CommandText = "insert into tb1(id,fname,lname) values(@id,@fname,@lname)"; cm.Parameters.AddWithValue("@id", textBox1.Text); cm.Parameters.AddWithValue("@fname", textBox2.Text); cm.Parameters.AddWithValue("@lname", textBox3.Text); cm.ExecuteNonQuery(); tb1TableAdapter.Fill(employDataSet.tb1); dataGridView1.Invalidate(); dataGridView1.Refresh(); con.Close(); please help me

Hi guys It was so nice I have a problem I have 4 columns that one of them is for another table but I want to display it in grid view too I mean I have one column as id and another as name in another table how can I show both of them and update the table Any help would be appreciated

Thank you Sir .. Its a very nice and simple explanation.

Hi,

I have a problem. When I click my save button my access database does not update. What am I doing wrong?

[language] using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb;

namespace NVFinancialTracker { public partial class FormCategory : Form { string connectString; OleDbDataAdapter da; DataTable dt; BindingSource bs; OleDbCommandBuilder cBuilder;

public FormCategory() { InitializeComponent(); connectString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\NVMoneyTracker.accdb"; da = new OleDbDataAdapter("SELECT * FROM CATEGORY", connectString); cBuilder = new OleDbCommandBuilder(da); dt = new DataTable(); da.Fill(dt); bs = new BindingSource(); bs.DataSource = dt; dataGridView1.DataSource = bs; }

private void button1_Click(object sender, EventArgs e) { da.Update(dt); }

} } [/language]

Does anyone know how to create connectionstring for sdf database?

[c#] how can I select a row at DataGridView and connect this row at Table in Sql?plz help me ,very thank [/C#]

very nice post , thank you very much , i have an issue , the .update method works when the user enter the data manualy but it didn't when the data add programticaly , need hlep , sorry for the mistake spelling ,

Hi Wonderful Article. I am having some problem while using this code. here's my code."VS 2010"

string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(local);database=BookSelling;user=sa;password=**"; string query = "select *from book"; OleDbDataAdapter adp = new OleDbDataAdapter(query, conString); OleDbCommandBuilder cmd = new OleDbCommandBuilder(adp);

DataTable dtable = new DataTable(); adp.Fill(dtable); dataGridView1.DataSource = dtable;

it gives this error. "Can not start your Application. Workgroup file is missing".