Update Database Table From DataGrideView on Update Button Click Event

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 2266 Views - Last Post: 02 February 2013 - 11:22 AM Rate Topic: -----

#1 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 01:28 AM

Hello Friends,
I Have a Create Application in VB.Net 08, and i have one DataGrideView, and showing Database Data in That DataGrideView, and i want direct update from SataGridView, using Button Click Event, When I Press Update but it will give this Error : "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
Note: Im Using SQL Server.
Code:
Private Sub Add_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
              ada.Fill(tab)
        DataGridView1.DataSource = tab.Tables(0)
    End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ada.Update(tab)
    End Sub



Please Help Me to Solve This Problem...
Thanks In Adv...

Is This A Good Question/Topic? 0
  • +

Replies To: Update Database Table From DataGrideView on Update Button Click Event

#2 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 01:55 AM

it requires proper working.

import these classes at the top of ur class.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataReader
Imports System.IO


1) make connection.
2)create insert statement.
3)create update statement.
4)then work over datagrid.

A)FOR INSERT

 Private Sub insert1()
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

 empcon.Open()
        empcmd.Connection = empcon
        empcmd.ExecuteNonQuery()
        empcon.Close()
End Sub



B)FOR UPDATE

Private Sub update1()

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

 empcon.Open()
        empcmd.Connection = empcon
        empcmd.ExecuteNonQuery()
        empcon.Close()
mygrid()
End Sub




C)FOR DATAGRID

 Dim dt As New DataTable
Private Sub mygrid()
 Dim da As New SqlDataAdapter
 da.SelectCommand = New SqlCommand("Select * from database order by column_name", empcon)
 
 da.Fill(dt)
  mygrid.DataSource = dt

End Sub



NOW CREATE BUTTONS.

1)UPDATE

Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
        update1()
    End Sub


2)SAVE

Private Sub button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button2.Click

        Call insert1()
        Call mygrid()
        update1()
        
    End Sub


if u want to show your database records in datagrid

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


keep moving on and if u got problem let us know.. gud luck

This post has been edited by kai_itz me: 31 January 2013 - 02:07 AM

Was This Post Helpful? 1
  • +
  • -

#3 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 02:38 AM

Dude, Thanks for your Reply, but my mean is not that actually, just see
Before
This is my GridView, now if i edit any of cell, and when am press botton update, at that time update all rows/columns into database,
Posted Image
before
im Editing Like this,
Now save this in database when press botton update,,
Posted Image


Hope you understand,,,
Thanks in Adv..
Was This Post Helpful? 0
  • +
  • -

#4 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 03:43 AM

THIS WILL DO THE JOB FOR U.... SET UR DATABASE NAME ,COLUMN NAME ACCORDING TO UR APPLICATION.

Private Sub modify()
        empcmd.CommandText = "update database.dbo.table1 set id = " & id.Text & " , brand_name ='" & _
         brandname.Text & "' , size =" & size.Text & " , item =" & _
         item.Text & " where id =" & id.text & ""

        empcon.Open()
        empcmd.Connection = empcon
        empcmd.ExecuteNonQuery()
        empcon.Close()
        mygrid()

    End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        modify()
    End Sub


IF THAT WAS NOT HELPFUL SHOW ME UR CODE ...
Was This Post Helpful? 0
  • +
  • -

#5 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 05:30 AM

Posted Image

ABOVE IS SNAP OF MY APPLICATION,,,
NOW YOU CAN SEE THERE MY DATABASE AS WELL AS MY PROJECT,,,
THERE IS ONE BUTTON NAME IS UPDATE, IF I AM EDIT ALL THE ROWS AND COLUMNS CELLS IN ONE TIME, NOW I WANT TO SAVE IT IN ONE CLICK OF UPDATE BUTTON, IS IT POSSIBLE???


'FORM1.VB'S CODE
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
    Dim ada As New SqlClient.SqlDataAdapter("Select * from tblusers", con)
    Dim tab As New DataSet

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        ada.Update(TAB)
    End Sub

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

*************

'MODULE1.VB'S CODE
Imports System.Data
Imports System.Data.SqlClient
Module Module1
    Public con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDataBase.mdf;Integrated Security=True;User Instance=True")
End Module




YOU HAVE'D WRITE CODE ABOVE, BUT IS ONLY FOR ONE ROW, WHICH SAME ID OK, BUT I WANT TO UPDATE FULLY DATABASE TABLE, SEE MY TITLE FRIEND,

HOPE YOU NOW UNDERSTAND WHAT I WANT,,,

THANKS IN ADV...

@NILESH VARCHAND
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 06:16 AM

Nilesh,
It would appear that all you would need to do is create a commandbuilder and update your datasource.

Try adding this to your Update button

Dim cb as New SqlCommandBuilder(ada)
ada.update(tab)


This is off the top of my head

This post has been edited by CharlieMay: 31 January 2013 - 06:25 AM

Was This Post Helpful? 0
  • +
  • -

#7 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 06:40 AM

CharlieMay Sir,

It will give error : "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

@Nilesh
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 06:57 AM

Well, you're selecting * in your table so that tells me that your table has no primary key defined.

For what you're doing here, I would suggest you set a primary key in your table.

A suggestion would be an autoincrement field. Then to keep it from appearing in your Datagridview, hide that column. This way, the commandbuilder can use it to ensure that it is updating the proper data.
Was This Post Helpful? 1
  • +
  • -

#9 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 07:48 AM

CharlieMay Sir,

Now its run preoper, no any error occur when load the project, but when i edit in DataGrid then click on Update button at that time no error occur and update the all cell which i have'd edited oky, but in database it is not update,
whats doing wrong?

@Nilesh
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 08:36 AM

It appears from you connection string that you have a Local database (probably an MDF file)

If you were to look in your Solution Explorer, there should be a database in that list. Click on that database and then in the properties window you should have a property called: Copy to Output Directory

It is probably set to Copy Always

You need to set that to either "Do Not Copy" or "Copy If Newer"

This will keep from overwriting the database that is created in your \debug folder (which is what you're adding records to) with a copy of the template that was created in your solution folder (which is used for installing a fresh copy of the database to the user during install). These are two separate entities.

This post has been edited by CharlieMay: 31 January 2013 - 08:47 AM

Was This Post Helpful? 0
  • +
  • -

#11 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 09:03 AM

yes sir,
if i select 'Do Not Copy' then it will give error like this:
"An attempt to attach an auto-named database for file D:\My Project\Practice Projects\DataGridViewUpdate\DataGridViewUpdate\bin\Debug\MyDataBase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
Posted Image

and if i select "Copy If Newer" then nothing doing, no update in database, no error, still not update in database.

this is my file code:
Code of - Form1.vb:
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1

    Dim ada As New SqlClient.SqlDataAdapter("Select * from tblusers", con)
    Dim tab As New DataSet

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New SqlCommandBuilder(ada)
        ada.Update(tab)
    End Sub

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


Code of - Module1.vb:
Imports System.Data
Imports System.Data.SqlClient
Module Module1
    Public con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDataBase.mdf;Integrated Security=True;User Instance=True")
End Module


Database View:
Posted Image

Please Help Me,,,
Thanks A lot for Trying...
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 10:09 AM

I've never had this problem, but then I don't use a local database but it appears that it's conflicting with the database that is open in your SERVER explorer.

Right-click on the database in SERVER explorer and select disconnect and let me know if the problem is still there.
Was This Post Helpful? 0
  • +
  • -

#13 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 10:22 AM

View PostCharlieMay, on 31 January 2013 - 10:09 AM, said:

I've never had this problem, but then I don't use a local database but it appears that it's conflicting with the database that is open in your SERVER explorer.

Right-click on the database in SERVER explorer and select disconnect and let me know if the problem is still there.


can you give me the application, which you have created???

@Nilesh
Was This Post Helpful? 0
  • +
  • -

#14 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 10:29 AM

View PostCharlieMay, on 31 January 2013 - 10:09 AM, said:

I've never had this problem, but then I don't use a local database but it appears that it's conflicting with the database that is open in your SERVER explorer.

Right-click on the database in SERVER explorer and select disconnect and let me know if the problem is still there.


if yes then send me on : nilesh7136@gmail.com

Thanks
Was This Post Helpful? 0
  • +
  • -

#15 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update Database Table From DataGrideView on Update Button Click Event

Posted 31 January 2013 - 10:31 AM

Here, go through these, it will help tremendously.

Your problem is not a code problem it is that you are trying to overwrite something that you have open in the server explorer.

This post has been edited by CharlieMay: 31 January 2013 - 10:31 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2