0

I am new to windows Forms applications...

I am working in VB.NET windows forms and dealing with DataGridView...

I have populated that DataGridView from two Table

Please tell me How to Add , Delete and Edit/Update the records of the DATAGridview back to the DataBase.

I am using SQLSERVER 2008 DataBase

I have two tables 1->CompanyMaster_tbl in this having Two fields . Cid and CompanyName,

Cid is the primary key of this table

2->DepartmentMaster_tbl in this having 4 fields. dtid,dtname,dtphon,dtmail,Cid.

dtid is the primary key,and Cid is the foreign key.. My data gridview look like this:enter image description here

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

  'NOTE: I removed the Using statements to ease your tests/understanding, but you shouldn't let the connections opened (or, at least, set connection-release part somewhere)
  Dim con As SqlConnection = New SqlConnection() 'SET THE CONNECTION STRING
  con.Open()

  adapter = New SqlDataAdapter("select c.CompanyName,d.dtName,d.dtPhone,d.dtEmail  from CompanyMaster_tbl c join  DepartmentMaster_tbl d on c.Cid=d.cId", con)

  Dim dt As DataTable = New DataTable()
  adapter.Fill(dt) 'Filling dt with the information from the DB
  gv.DataSource = dt 'Populating gv with the values in dt

End Sub

in update button i wrote code like this:

Dim dt1 As DataTable = DirectCast(gv.DataSource, DataTable)
adapter.Update(dt1)

but after editing anything in gridview,,i click the update button,,but i am getting error in this row

da.Update(dt1)

Error: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Thanks in advance

varocarbas
  • 12,354
  • 4
  • 26
  • 37
user3106114
  • 183
  • 2
  • 11
  • 31

1 Answers1

0

If you want to keep some synchronisation between the DataGridView and the DB, you shouldn't add columns/rows manually, but rely on the DataSource property. Sample code adapted to your case:

Using con As SqlConnection = New SqlConnection() 'You have to set the CONNECTION STRING
    con.Open()

    Using adapter As SqlDataAdapter = New SqlDataAdapter("select c.CompanyName,d.dtName,d.dtPhone,d.dtEmail  from CompanyMaster_tbl c join  DepartmentMaster_tbl d on c.Cid=d.cId", con)

        Dim dt As DataTable = New DataTable()
        adapter.Fill(dt) 'Filling dt with the information from the DB
        gv.DataSource = dt 'Populating gv with the values in dt

    End Using
End Using

The code above extracts all the information you want from the DB, puts it into a DataTable (dt) and then feeds it to the DataGridView as DataSource. gv has now all the values from dt; also any change in dt is reflected in gv and vice versa (this coupling is almost perfect, at least, when updating values; there might be some problems while deleting rows/columns or changing their basic configuration). You might even keep adapter as a global variable (outside the Using statement) and rely on it to update the DB regularly (via adapter.Update(dt), for example).

Quite a few alternatives; but, in any case, relying on a DataSource is certainly better under your conditions.

varocarbas
  • 12,354
  • 4
  • 26
  • 37
  • @user3106114 Feel free to ask if you have doubts. – varocarbas Jan 06 '14 at 10:04
  • this code i can write in load event,,how i can write code in my Update button – user3106114 Jan 06 '14 at 10:08
  • @user3106114 The whole point of the codes I write in SO is helping you to understand the idea; from your comment, it seems that you are not understanding the idea. Let me do a new try: now you are populating your gv row by row (and column by column), presumably at the start. My suggestion is, instead adding all the rows/columns to the gv, add them to a DataTable (dt) and then set this datatable as the datasource of gv. In this way, you would be able to access dt from anywhere (example: Dim dt as DataTable = Directcast(gv.DataSource, DataTable)) and then you can use this dt to update... – varocarbas Jan 06 '14 at 10:11
  • @user3106114 your DB. I wrote also an example about that: adapter.Update(dt) -> if you call this code (and the adapter was started as shown in my code), your DB would be updated with all the changes in dt (which is the datasource of gv and thus would include all the changes of the gv). If you have problems to understand all these ideas, please post the code you tried and I might give you some indications (last time; as said, I am not here to deliver custom solutions). – varocarbas Jan 06 '14 at 10:13
  • sir i understood what you did ..but i am confusing while updating particular column in grid view,,how that will update in sql server – user3106114 Jan 06 '14 at 10:17
  • @user3106114 when using a DataSource, anything you change in the gridview is changed in the DataSource (and you are precisely using this datasource to update the DB). Example: dt gets the values 1,2,3 from the DB; you set it as DataSource of the DGV and thus it also get these values; then you modify the values in the DGV and change them to 4,5,6 and thus the DataSource (dt) is also automatically change. If you do adapter.Update(dt) (being the adapter the same variable which you used to communicate with the DB the first time), it would look for changes (in this case, everything changed) and – varocarbas Jan 06 '14 at 10:20
  • @user3106114 perform the corresponding modifications. It does not seem that you have understood the ideas. Or, at least, you have to learn quite a lot regarding various VB.NET issues. – varocarbas Jan 06 '14 at 10:21
  • @user3106114 please, read above to understand it better. Or, ideally, test the code I have written (which, properly used, performs exactly these actions). – varocarbas Jan 06 '14 at 10:22
  • in my datagridview image i have to update DepartmentName Service to Serviceoperation under the company Name Emaar,,so how i can write the code,,can u pls gv me code for this,,then i can understand – user3106114 Jan 06 '14 at 10:24
  • while clicking update button how i can recognize which column is edited in gridview? – user3106114 Jan 06 '14 at 10:27
  • @user3106114 Give you code? But what is this thing over there? The one with gray background? This small code replaces all your code – varocarbas Jan 06 '14 at 10:28
  • @user3106114 OK. Stop! I have given you a solution (and supporting code) of how you have to face the problem. That is, relying on a DataSource. If you don't know what is even a datasource, this discussion will go forever. So... Do you want to do some research/test to understand what I am proposing or shall I just delete this answer and you can wait for the next person willing to spend one hour with you explaining everything or writing a custom code for you? – varocarbas Jan 06 '14 at 10:30
  • No sir...i am trying ,,,in update button i given code like this:Using ad As SqlDataAdapter = New SqlDataAdapter() Dim dt As DataTable = New DataTable() ad.Update(dt) gv.DataSource = dt End Using – user3106114 Jan 06 '14 at 10:32
  • @user3106114 OK. If you want try, please do that: write my code at form load and nothing else (no row/column populate). But remove the internal using; instead of "Using adapter As SqlDataAdapter = New SqlDataAdapter(..." write adapter = new SqlDataAdapter(... and declare Dim adapter As SqlDataAdapter as a global variable. In your button write: Dim dt As DataTable = DirectCast(dv.DataSource, DataTable) and adapter.Update(dt) -> this will update the DB with any change in the DataGridiview. Confirm that it works, understand how it works, etc. – varocarbas Jan 06 '14 at 10:36
  • @user3106114 various things. Please, test my update. – varocarbas Jan 06 '14 at 12:40
  • @user3106114 ?! the same error? This is impossible! In your previous version, you weren't even using the DataAdapter you populated. Please tell me the error you are getting. – varocarbas Jan 06 '14 at 12:43
  • @user3106114 well... perhaps you have to remove the other using too (sorry cannot test). – varocarbas Jan 06 '14 at 12:44
  • @user3106114 try it now (and tell me the error in case of getting any). – varocarbas Jan 06 '14 at 12:45
  • @user3106114 Also... bear in mind that I haven't configured the connection. You have to add the parameters to make it connect to your DB as previously. – varocarbas Jan 06 '14 at 12:46
  • while clicking update button getting Error:Update requires a valid UpdateCommand when passed DataRow collection with modified rows. – user3106114 Jan 06 '14 at 12:49
  • @user3106114 Please, take a look at my last update and mainly read the comments: this code requires you to introduce the connection string to your DB. If it connects rightly, it shouldn't output any error. But you have to connect it properly. The error you are referring is related not to having a proper connection to the DB. Please, take your take and make the tests/research you need to understand this code properly as far as it should work (by assuming that you previous one was working). – varocarbas Jan 06 '14 at 12:51
  • how to configure the connection,,? – user3106114 Jan 06 '14 at 12:52
  • sir,,while loading the page i am getting data in grid view,,that means connection is proper right? – user3106114 Jan 06 '14 at 12:54
  • @user3106114 ?! how was your previous code working? The connection part of my code was as yours (the name of the connection variable is the same: con); the only change of my code is the way in which the Data is retrieved from the DB (it stores it into a DataAdapter). – varocarbas Jan 06 '14 at 12:55
  • @user3106114 You are just writing my code in the Form Load event (and you are not adding any DataSource from the design view) and it populates info into the DataGridView? Then it should work. But where are you setting the connection string?! See... this is a bit weird and keep talking don't think that will be helpful to you. The ideas are very simple: store the data into a DataAdapter, declare this DataAdapter globally (as shown in my last update) and access this DataAdapter to update the DB. Best thing: start a new project to do all these tests and understand things properly – varocarbas Jan 06 '14 at 12:58
  • sir dont have any problem to load data into gridview,,problem in updation part..while clicking update button only error is coming – user3106114 Jan 06 '14 at 12:58
  • @user3106114 The problem is that I am not sure that you are populating the DGV with my code (without connection string, it cannot work); it might be the case that you have set other datasource (from the design view, for example). Please, start a new project and confirm that the DGV is populated with the code on the form load and that you are using just the code in my last update. – varocarbas Jan 06 '14 at 13:00
  • @user3106114 See... I don't think that all this will be helpful to any of us (I am getting tired and you are not learning). The code I posted should work fine; but mainly I have intended to transmit you a different way to face the problem. As far as your ideas do not seem to be too clear on this front; please spend some time learning/doing research on connecting to the DB, dealing with DataSource, etc. Start a project from scratch and use my code there. Once your ideas are clearer you can ask me again; but keep asking without the required knowledge does not seeem good. Please, take your time. – varocarbas Jan 06 '14 at 13:04
  • sir,,i wrote the connection string in class page,,so i just connecting that here like con.connect – user3106114 Jan 06 '14 at 13:05
  • @user3106114 yes, but the connection variable in code is declared LOCALLY and thus you have to set the connection in the Form Load event (otherwise you might be using other con variable you have declared somewhere else). Please, start a new project, add a DataGridView called dg and my code (with the connection string) to test this properly. – varocarbas Jan 06 '14 at 13:07
  • @user3106114 OK. But now we have reduced sources of problems. So, you are using EXACTLY the same code in my last update (without using statements and with the DataAdapter global declaration right); and the datagridview populates rightly? Then do a step-by-step execution and confirm that each line is fine. – varocarbas Jan 06 '14 at 13:22
  • @user3106114 or you can do some quick research: http://stackoverflow.com/questions/1460221/datagrid-using-sqldataadapter-update-problem (as said, not able to test now) – varocarbas Jan 06 '14 at 13:25
  • i am using EXACTLY the Same Code.sir – user3106114 Jan 06 '14 at 13:27
  • @user3106114 (then the link above might be helpful) in any case, bear in mind that my answer consisted in suggesting you relying on a DataSource. You have many options to update the DB from the datasource (you might reconnect again to the DB; what you are trying is maintaining the connection open but your DB might restrict this somehow). My intention wasn't dealing with DB related issues as far as these tend to depend strongly upon the OP's configuration and, in this case, I am not even able to test. I guess that now your ideas regarding how to deal with DGV/DB are much clearer... – varocarbas Jan 06 '14 at 13:30
  • in that link what is bSource ..instead of that what i have to declare – user3106114 Jan 06 '14 at 13:30
  • @user3106114... I have been pretty patient and understanding (even wrote some custom code for you; what I said that didn't want to do) and now it is time for you to deal with the DB-related problems. – varocarbas Jan 06 '14 at 13:31
  • @user3106114 what you expect? Me to explain everything? Your question was how I can update the values from my DGV and my answer: use a DataSource. I have been nice and help you to introduce yourself into the DataAdapter/DataSource world but all this seems pretty outside of what my contribution here should consist in (remember: minimum understanding, no custom development, etc.)... You didn't do anything wrong; just understand that I cannot help more from this position. Now you have plently of options: explore them and, in case of finding problems, ask here about DB, not about DGV (-> solved). – varocarbas Jan 06 '14 at 13:34
  • @user3106114 I am rarely involved in these long discussions because do not drive anywhere (I told you a while ago): do something by your own; understand everything and ask specific, easily-reproduceable problems. But, as said, all this is DB-related and you would have to include specific information about your conditions and, eventually, ask someone currently able to test. Don't you think? – varocarbas Jan 06 '14 at 13:36
  • what ever you said..i did exactly that ,,,but still getting error, – user3106114 Jan 06 '14 at 13:44
  • @user3106114 BTW I saw your other question and the error you got and perhaps you find this useful: http://stackoverflow.com/questions/588361/update-requires-a-valid-updatecommand-when-passed-datarow-collection-with-modifi – varocarbas Jan 06 '14 at 13:45
  • @user3106114 I insist: my answer consisted in giving you an alternative to the DVG/DB communication; the DB-related issues have to be fixed by you. If you find problems with, what I thought that was an easy and immediate alternative, just try a different one: RECONNECT TO THE DB... now you have all the data in DataTable, do some research about the best way to update the DB with the changes in the DataTable. But bear in mind that this is completely outside this question/answer and, even though, I have tried to help you a bit out. Let's close it here, please. – varocarbas Jan 06 '14 at 13:47
  • @user3106114 Thanks. And, yes, there is a solution: affect the adapter.UpdateCommand property – varocarbas Jan 06 '14 at 13:50