VB.net: How to update database from datagridview
-
I'm trying to read a datagridview and post the changes back to the SQL database. Does anyone have some straightforward code that will work. I'm a newbie in VB.net. :confused:
-
I'm trying to read a datagridview and post the changes back to the SQL database. Does anyone have some straightforward code that will work. I'm a newbie in VB.net. :confused:
Hi Roger, When people ask for "Straightforward code" it's kind of impossible to respond. All code is tailored to an individual need and no one is going to have EXACTLY the code you want. (Nor will they create it for you if you are not willing to put some effort into it yourself.) The best way to start a project like this is to read some articles about what you are doing. You can search codeproject for articles, like this.[^]. Then as you implement code you've found in the article, if you run into problems post your code and error messages and a description of what your program is doing and what you want it to do. Then we can help you. Hope this gives you a good place to start.
-
Hi Roger, When people ask for "Straightforward code" it's kind of impossible to respond. All code is tailored to an individual need and no one is going to have EXACTLY the code you want. (Nor will they create it for you if you are not willing to put some effort into it yourself.) The best way to start a project like this is to read some articles about what you are doing. You can search codeproject for articles, like this.[^]. Then as you implement code you've found in the article, if you run into problems post your code and error messages and a description of what your program is doing and what you want it to do. Then we can help you. Hope this gives you a good place to start.
I think it's a fair question, and one that has recently been much on my mind. In VB6, making an grid you could update was completely trivial, under VB.NET, after several hours, I'm completely baffled. Are we supposed to hand-code an add / update / insert statement, somehow connect it to something? Is a "CommandBuilder" somehow involved? Is there some "do it automatically" switch somewhere I'm not pushing? adapter.update(dataset) doesn't throw an error, and doesn't do anything either. You'd think someone would have posted a sample project, where you put the query in a textbox, click 'execute', and get a datagridview you can edit, like you could do in 5 minutes in VB6. There are DataGrid based examples from VS2005, but I haven't seen any for the DataGridView. At present, my guess is because this is next to impossible with the DataGridView. Near as I can tell, I'm doing what the instructions say, but nothing happens. Maybe NPGSQL doesn't work?
-
I think it's a fair question, and one that has recently been much on my mind. In VB6, making an grid you could update was completely trivial, under VB.NET, after several hours, I'm completely baffled. Are we supposed to hand-code an add / update / insert statement, somehow connect it to something? Is a "CommandBuilder" somehow involved? Is there some "do it automatically" switch somewhere I'm not pushing? adapter.update(dataset) doesn't throw an error, and doesn't do anything either. You'd think someone would have posted a sample project, where you put the query in a textbox, click 'execute', and get a datagridview you can edit, like you could do in 5 minutes in VB6. There are DataGrid based examples from VS2005, but I haven't seen any for the DataGridView. At present, my guess is because this is next to impossible with the DataGridView. Near as I can tell, I'm doing what the instructions say, but nothing happens. Maybe NPGSQL doesn't work?
I agree with you, that the question is valid. I just felt like it was too broad a topic and requires more information to decently answer in the forums as is. I haven't really ever used VB6, so I'm not aware of the differences between it and VS2003 or above. As for how to add the add/update/insert statement easily, it also depends on the data provider you are using. If you use OleDb or SqlClient you should be able to use a CommandBuilder. You just need to write the Select SQL Statement, set it to the DataAdapter, and then put this statement (if using SqlClient provider):
Dim cb As New SqlClient.SqlCommandBuilder(myDataAdapter)before you call the update method of the DataAdapter object. It's also a good idea to clean up after the update with this statement:cb.Dispose()I'm not sure if it works exactly the same way if you are working primarly in design mode, as I mostly code all my database stuff instead of using the visual studio designer to drag and drop DataAdapters, Connections, etc. Since there are so many possible scenarios, it's hard to give more advice than this. Which is why it's so much better to post your code when asking for help. Please post what you have for your update and we can give you some help and advice on what is not working. -
I agree with you, that the question is valid. I just felt like it was too broad a topic and requires more information to decently answer in the forums as is. I haven't really ever used VB6, so I'm not aware of the differences between it and VS2003 or above. As for how to add the add/update/insert statement easily, it also depends on the data provider you are using. If you use OleDb or SqlClient you should be able to use a CommandBuilder. You just need to write the Select SQL Statement, set it to the DataAdapter, and then put this statement (if using SqlClient provider):
Dim cb As New SqlClient.SqlCommandBuilder(myDataAdapter)before you call the update method of the DataAdapter object. It's also a good idea to clean up after the update with this statement:cb.Dispose()I'm not sure if it works exactly the same way if you are working primarly in design mode, as I mostly code all my database stuff instead of using the visual studio designer to drag and drop DataAdapters, Connections, etc. Since there are so many possible scenarios, it's hard to give more advice than this. Which is why it's so much better to post your code when asking for help. Please post what you have for your update and we can give you some help and advice on what is not working.Aha! That did it. I added:
dim cb as new NpgsqlCommandBuilder
cb = New NpgsqlCommandBuilder(m_da)Where: m_da is a NpgsqlDataAdapter control in the design view. m_cmd is a NpgsqlCommand in the design view. m_ds is a System.Data.Dataset in the design view. New code is after putting the SQL Select statement into m_cmd, and before m_da.fill(m_ds) Thanks very much, this is *much* simpler than some sites made it look. These seems more complicated than it was in VB6. Then, you had a datacontrol and a grid. Load the datacontrol's connection string, set the datasource to the query, 'refresh', all done. Now I have something that works like the demo I described, but it's probably not a very good example, don't know which of the things I've pulled from the web actually contribute to it working yet. At the risk of maybe should have clicked "Rant..." I've only recently started using VB.NET. Started with VB in VB3. Stopped in VB6, because the upgrade wizard crashed on my 300 form, 250 modules/classes application. As of VS2008, upgrade wizard didn't crash, but it's looking like an 80% rewrite; what the upgrade wizard left looks like a transporter accident. data binding (in the converted application) is demolished, usercontrols didn't port for beans, deep heavy sigh. My impression at this point is that VB has lost it's direction - it used to be going toward 'Simpler', ever easier to build stuff. Now it's trying to compete (and doing well at it) with Java and C++. I spent a decade programming in each of those, and chose VB6 for my own projects, because I like results more than tinkering with strongly typed stuff. I liked the direction VB was going, but it took an abrupt left turn with VB.NET. Hopefully I'll get used to it. Probably slightly easier than relearning Java.