We help IT Professionals succeed at work.

Update sql table from datagridview

sandya_116
sandya_116 asked
on
537 Views
Last Modified: 2011-10-03
I have a table which has three columns(column1, column2, column3). Column1 and column2 together are technically primary keys but they are not set as primary keys in the database. I need to load data from the sql into the datagridview and then update or insert (not delete) into the sql database from the datagridview when user clicks on a button. I need help urgent. Please help. Thanks.
Comment
Watch Question

The easiest way is probably to use a CommandBuilder.  This example uses the Northwind Customers table:

Imports System.Data.SqlClient

Public Class Form1

    Private da As New SqlDataAdapter("SELECT * FROM Customers", _
        "Data Source=.\SQLExpress;Initial Catalog=Northwind;Integrated Security=True")
    Private ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles MyBase.Load
        da.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles Button1.Click
        Dim cb As New SqlCommandBuilder(da)
        da.Update(ds.Tables(0))
    End Sub
End Class
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
itkmanoj:

You seem to have left out the most important (and complicated) part.  You'd have to manually write an update statement (not a trivial task) and an insert statement (ditto), create a Command object, and execute the query, which would require another open connection.

The logic to build the update statement would look something like this:

"UPDATE myTable SET Column3=" & dtRow("Column3").ToString() & " WHERE Column1=" & _
    dtRow("Column1").ToString() & " AND Column2=" & dtRow("Column2").ToString()

Why go to all that trouble when a CommandBuilder will do it for you?

Author

Commented:
cheddar73:
when I use your code, the table is not being updated but being inserted. So I have extra rows now. Why is it inserting when it needs to update? Thanks.
It will update rows that have been modified.  If you have inserted new rows, it will insert them into your data source as well.  To be clear, are you saying that rows which have been updated in the DataGridView are being inserted as new rows in the data source?

Author

Commented:
I am udpating the rows in the datagridview that already exist. So to be clear, rows which have been updated in the DataGridView are being inserted as new rows in the table.

Author

Commented:
I am sorry I did something wrong in my code and now I fixed it. If i insert new rows in the datagridview, it works fine. But if I modify existing items, it is giving me an error message: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." Just so that you know, my table does not have any primary keys. But I would say techincally that column1 and column2 are primary keys together.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
itkmanoj's solution worked great. How would I use commandbuilder for the same solution if it makes things faster to run and hopefully less connections?
Unfortunately you can't use the CommandBuilder if your table doesn't have a primary key defined on the database side.  I completely glossed over that part of it when I read your question, so it really doesn't apply to your situation.

Also, a CommandBuilder is not faster, it's actually a bit slower because it needs to query the database for metadata in order to generate the updating logic based on your select command (which is why it only works if your table has a primary key defined and that key is included in your select statement).  It is, however, much easier to code than having to write updating logic manually.

Author

Commented:
Alright sounds good to me. I will stick to what I have.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.