none
saving the data entered in the datagridview to database table

    Question

  •  

    Hi All,

    Please help me to solve this problem

    i have a table named TabDetails containing columns id,FirstName,LastName.

    There is a DataGridView and i have set the data source of the DataGridView to be TadDetails.

    Now the DataGridView contains columns id,FirstName,LastName.

    There is also a button named Save.

    When i click the save button i need the details in the DataGridView to be saved in the table TabDetails.

    How can we do it using the insert statements.?

    or is there any other way to implement the above.

    could me please tell the source code for the above

    Thank You,

    Jasna

    Wednesday, March 14, 2007 12:17 PM

Answers

  • Hi,

    Please follow these steps to update the database directly from DataGridView whether its update or insert or delete

    1. Add DataGridView Control in the form
    2. Select that control and and open DataGridView Tasks clicking on ">" simbol
    3. Open Choose DataSource Combo and click on "Add Project DataSource..."
    4. Select Database > Next > Select/Create Connection > Next > Select Table you want to fill in the control > Finish
    5. This will create a DataSet object will be shown in your form along with auto generated Adapter object too.
    6. Select and right click on DataSet object control in designer and Select Edit in DataSet Designer... > this will open related XSD file
    7. Right click on the opened UI for the dataset and Select Configure...
    8. Select Advance Options button... This window will have all the insert, update, delete and other query's checkboxes selected, keep it as it is and press OK
    9. Then press Next and select "Create methods to send updates directly to the database option" [default, it is selected] > Next and Finish it. This will generate required code for doing operations automatically
    10. Add the following code in your Form class,

    [I have added Users table in database Northwind in sql server 2000, and connected with it and configured my datagridview to show that usertable. It has 3 columns same as yours. After above steps, I have "northwindDataSet", "usersTableAdapter", "usersBindingSource" automatically created with lots of other code generated in form's designer.cs]

    I have added save button in the form so after edit/add/delete rows in Datagridview UI, i will press Save button to save it back to Database directly.

    This will fill up the datagridview

            private void DataGridViewDirectDBUpdate_Load(object sender, EventArgs e)

            {

                // TODO: This line of code loads data into the 'northwindDataSet.Users' table. You can move, or remove it, as needed.

                this.usersTableAdapter.Fill(this.northwindDataSet.Users);

     

            }

     

    This will save the data back to the database

            private void btnSave_Click(object sender, EventArgs e)

            {

                this.usersTableAdapter.Update(northwindDataSet);

            }

    So infact you are writing the only ONE LINE to update the database after configuring your control as explained above.
    How its amazing really no!!

    The generated code has few files and lots of code so not posting it here but above process will help you to do that very easily, i hope so.

    If you still want some more or other information, let me know

    HTH,

    Thursday, March 15, 2007 8:25 AM
  • Hi,

    I have seen your project code.

    That was a problem because you are using .MDF files while the project i have given was  using database directly from sql server.

     

    I have solved your problem this way.

    Just change open app.config

    Change the following line in connection string:

    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=<full path of your .mdf file with filename>;Integrated Security=True;User Instance=True"

     

    Here i have removed "|DataDirectory|" and instead put full path of that file.

     

    e.g

    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\Details\Details\MYDatabase.mdf;Integrated Security=True;User Instance=True"

     

    HTH,

     

    Tuesday, April 03, 2007 10:19 AM
  • Hi Mahesh,

     

    Thanks u so much...................

    Finally you gave me the correct answer.

    Thanksa lot once more.......................

    Jasna

    Saturday, April 07, 2007 7:09 AM

All replies

  • Hi,

    Please follow these steps to update the database directly from DataGridView whether its update or insert or delete

    1. Add DataGridView Control in the form
    2. Select that control and and open DataGridView Tasks clicking on ">" simbol
    3. Open Choose DataSource Combo and click on "Add Project DataSource..."
    4. Select Database > Next > Select/Create Connection > Next > Select Table you want to fill in the control > Finish
    5. This will create a DataSet object will be shown in your form along with auto generated Adapter object too.
    6. Select and right click on DataSet object control in designer and Select Edit in DataSet Designer... > this will open related XSD file
    7. Right click on the opened UI for the dataset and Select Configure...
    8. Select Advance Options button... This window will have all the insert, update, delete and other query's checkboxes selected, keep it as it is and press OK
    9. Then press Next and select "Create methods to send updates directly to the database option" [default, it is selected] > Next and Finish it. This will generate required code for doing operations automatically
    10. Add the following code in your Form class,

    [I have added Users table in database Northwind in sql server 2000, and connected with it and configured my datagridview to show that usertable. It has 3 columns same as yours. After above steps, I have "northwindDataSet", "usersTableAdapter", "usersBindingSource" automatically created with lots of other code generated in form's designer.cs]

    I have added save button in the form so after edit/add/delete rows in Datagridview UI, i will press Save button to save it back to Database directly.

    This will fill up the datagridview

            private void DataGridViewDirectDBUpdate_Load(object sender, EventArgs e)

            {

                // TODO: This line of code loads data into the 'northwindDataSet.Users' table. You can move, or remove it, as needed.

                this.usersTableAdapter.Fill(this.northwindDataSet.Users);

     

            }

     

    This will save the data back to the database

            private void btnSave_Click(object sender, EventArgs e)

            {

                this.usersTableAdapter.Update(northwindDataSet);

            }

    So infact you are writing the only ONE LINE to update the database after configuring your control as explained above.
    How its amazing really no!!

    The generated code has few files and lots of code so not posting it here but above process will help you to do that very easily, i hope so.

    If you still want some more or other information, let me know

    HTH,

    Thursday, March 15, 2007 8:25 AM
  • Hi mahesh,
    i'm Jasna.........
    I have posted a query in MSDN FORUM.
    You have given me the solution for that.
    Thanks a lot.
    I'm using VB.Net.
    i have done as you told.
    but my problem is i'm only able to select the data from the database.
    if i edit/insert new row in the grid it is not reflected in the database.
     
    Below is the code that i have done

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs ) Handles MyBase.Load

    'TODO: This line of code loads data into the 'Database1DataSet.det' table. You can move, or remove it, as needed.

    Me.DetTableAdapter.Fill(Me.Database1DataSet.det)

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs ) Handles btnSave.Click

    Me.DetTableAdapter.Update(Database1DataSet.det)

    End Sub

    End

    Class
    I dont know what is wrong................
    Please do help me
     
    Thursday, March 15, 2007 2:28 PM
  • Hi,

    From my first post, you really dont need to follow steps from 6 to 9. It will work without that also.

    In you code above, change as shown following and it should work fine, i think.

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs ) Handles btnSave.Click

    Me.DetTableAdapter.Update(Me.Database1DataSet)

    End Sub

    Note that i have removed table name after dataset name in update method parameter.

    If you still face problem,

    I have already uploaded working solution for this scenario in VB.NET which you can get it from here.
    [You will need to pass the hotmail/msn passport credentials to access that as its my .net usergroup area]
    On the page you will find DataGridViewDBUpdate.zip.

    HTH

    Saturday, March 17, 2007 8:34 AM
  • Hi Mahesh,

    I'm sorry to say that it is not still working.........

    I registered to the .net user group to get the working solutions

    But that is not accessible till now.( i should get reply from group manager)

    Thank you,

    Jasna

     

    Sunday, March 18, 2007 10:14 AM
  • Hi,

    Try it now please. I have already accepted your request.

    Regards,

    Sunday, March 18, 2007 2:04 PM
  • Have you tried?
    Tuesday, March 20, 2007 7:05 AM
  • Hi Mahesh

    Yes I tried.

    I'm doing in VB.NET 2005 and i use SQL server that comes togeather with that.

    I do not have Seperate SQL Server

    After downloading your program i created a table users and i configured the NorthWindDataSet to that table.

    Still its coming the same way.

    i.e When we execute the program the grid view displays all the items in the table.

    but when we enter new record the database is not updated.

    I tried puting breakpoints in the update command and then executing it.

    while pressing the F8 statements it shows the grid with the new record entered.

    i think its updated only in the data adapter and not in the table.

    I would like to send the project to you.

    Where can i attach the folder.

    Please let me know.

    Thanks,

    Jasna

    Tuesday, March 20, 2007 8:19 AM
  • HI,

    Please download the solution from the same location again. [Download the same file - DataGridViewDBUpdate.zip]
    [I have renamed old solution to DataGridViewDBUpdateSql2000.zip]

    I have changed the solution which is using now SqlExpress.

    From within, VS.NET i have created "MyExpressDB" database in <mymachinename>\sqlexpress. Then I have created table named "Users" in that. [UserID is primary key with no replication and autoincrement 1]

    This sample has one limitation that if 0 records then it will give exception while adding records from the application. If there is(are) existring record(s) then it will work just fine.

    Please give it a try. This should work now.
    [replace "mymachinename" with your machine wherever applicable in whole zip]

    If still not working then please send me your code on the same mail id where you have mailed previously. I will check it out too.

    HTH,

    Thursday, March 22, 2007 10:29 AM
  • Also be certain that the you have not Save the database in the project directory, if you have click on it and change the Copy to Output Directory  to Do Not Copy.
    Thursday, March 22, 2007 11:05 PM
  • Hi,

    I have seen your project code.

    That was a problem because you are using .MDF files while the project i have given was  using database directly from sql server.

     

    I have solved your problem this way.

    Just change open app.config

    Change the following line in connection string:

    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=<full path of your .mdf file with filename>;Integrated Security=True;User Instance=True"

     

    Here i have removed "|DataDirectory|" and instead put full path of that file.

     

    e.g

    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\Details\Details\MYDatabase.mdf;Integrated Security=True;User Instance=True"

     

    HTH,

     

    Tuesday, April 03, 2007 10:19 AM
  • Thank´s "Indian Ocean". This solved the problem for me. I can´t understand why  "|DataDirectory|" did not work. The full search path did the trick. Thank´s again.
    Friday, April 06, 2007 2:10 PM
  • Hi,

    The reason for above problem is simple but good to know.

     

    Please have a look at the following threads for good explanations,

    1. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=69953&SiteID=1
      See answer by "David Sceppa" in this thread

     

    2. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=427407&SiteID=1
      See answer by "Sarah Parra - MSFT" in this thread

     

    HTH,

    Note: Please mark best and acceptable replie(s) as the ANSWER(S) so it can help others easily for such issues how it helped you.

     

     

     

    Friday, April 06, 2007 2:40 PM
  • Hi Mahesh,

     

    Thanks u so much...................

    Finally you gave me the correct answer.

    Thanksa lot once more.......................

    Jasna

    Saturday, April 07, 2007 7:09 AM
  • hello,

     

    I have a windows application (c#) whereby I have a from on which I have dragged and dropped a datasource linked to a table in sqlexpress database.

     

    On running the app, the datagrid allows entry of new rows which, however, are not updated to the database.

     

    I have tried different solutions from the forums without any success.

     

    Grateful if dear contributor 'Indian Ocean' could please provide me steps to follow from start to end to make this work.

     

    Thank you.

     

     

     

    Tuesday, March 25, 2008 11:10 AM
  • Hi..

    I've been facing exactly the same problem for a long time and couldn't solve it..

    I've read what you wrote but I didnt understand quite good what you've last done, so here's my problem:

    I have a database on micrososft  SQL Server 2000 and created in it a table called timesheet

    I have a vb.net application which i've created using vb.net 2008

    In this application I have a datagrid view and a save button

    All I want is that: enter information to the grid and when I press save to save this information in timesheet table 

    Plz help me , I'm runnig out of time !

    Regards ...

    Sunday, August 17, 2008 6:24 PM
  • this really helped me. Thank you indian ocean. 
    Wednesday, January 05, 2011 5:45 PM
  • thank u so much for ur code..this helps us very much ..

    i hv just join group so looking frwd for this..thank u..

    Saturday, December 15, 2012 9:42 AM