none
a way to save all data stored in the database in datagridview VB.NET

    Question

  • Good Morning

    how all the data in datagridview stored everything in the database. I am using vb.net 2012 and sql server 2012

    Thanks

    Wednesday, April 5, 2017 2:09 AM

All replies

  • Hi FahmiZR,

    According your description, please refer to code below.

    Private Sub loaddata()
            Dim strcon As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Demo(VB.NET)\Demo(VB.NET)\Database1.mdf;Integrated Security=True"
            Dim strquery As String
            Using conn As New SqlConnection(strcon)
                Using cmd As New SqlCommand()
                    cmd.Connection = conn
                    conn.Open()
                    For i As Integer = 0 To DataGridView1.Rows.Count - 2
                        strquery = "INSERT INTO test VALUES (" + DataGridView1.Rows(i).Cells("ID").Value + ",'" + Convert.ToString(DataGridView1.Rows(i).Cells("Column1").Value) + "' , '" + Convert.ToString(DataGridView1.Rows(i).Cells("Column2").Value) + "');"
                        cmd.CommandText = strquery
                        cmd.ExecuteNonQuery()
                    Next
                    conn.Close()
                    MessageBox.Show("OK!")
                End Using
            End Using
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            loaddata()
    End Sub
    
    

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 6, 2017 2:54 AM
    Moderator
  • It depends on how you got the data into the DataGridView in the first place.  Presumably you would have used a TableAdapter instance to Fill a DataTable, so you would then call the Update method on that same TableAdapter instance to propagate any changes back to the database server.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, April 6, 2017 12:39 PM
    Moderator
  • You are going from SQL Server to a DataGridView, right.  Run the code below.

    Imports System.Data.SqlClient
    Public Class Form1
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim cmdBuilder As SqlCommandBuilder
        Dim ds As New DataSet
        Dim changes As DataSet
        Dim sql As String
        Dim i As Int32

        Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            connetionString = "Data Source=EXCEL-PC\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True;"
            connection = New SqlConnection(connetionString)
            sql = "Select * from OrderStatusCode"
            Try
                connection.Open()
                adapter = New SqlDataAdapter(Sql, connection)
                adapter.Fill(ds)
                DataGridView1.DataSource = ds.Tables(0)
                connection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Sub

        Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            'NOTE:  for this code to work, there must be a PK on the Table
            Try
                cmdBuilder = New SqlCommandBuilder(adapter)
                changes = ds.GetChanges()
                If changes IsNot Nothing Then
                    adapter.Update(changes)
                End If
                MsgBox("Changes Done")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Sub

        Private Sub DataGridView1_Click(sender As Object, e As EventArgs) Handles DataGridView1.Click
            DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Orange
        End Sub
    End Class




    MY BOOK


    • Edited by ryguy72 Saturday, April 8, 2017 4:21 AM
    Saturday, April 8, 2017 4:11 AM
  • Hello,

    The most simplistic method to load and save data in a DataGridView is as follows. Create strong typed classes using Visual Studio’s data wizards. Step by step instructions. Myself, I can't recommend this unless all requirements are known outside the save operation as usually showing, add, removal and edit operations are usually combined with filtering and sorting which dependent on your needs using the data wizards could become problematic.

    The next idea is using the managed data provider for SQL-Server.

    Simple code sample for add/remove/save. This sample shows saving one row but when the code is placed into a for/next or for/each all data can be saved. Better than looping through rows in the DataSource of the DataGridView (when data is loaded into the DataSource which is best practice you iterate the DataSource and not the DataGridView rows) is to uses the loaded DataTable's GetChanges method e.g. 

    Dim AddedTable As DataTable = CType(DataGridView1.DataSource,DataTable).GetChanges(DataRowState.Added)

    Then if the resulting DataTable is not Nothing (it's Nothing if there are no added rows) we iterate those rows. So if you loaded 100 rows and changed three rows the GetChanges method iterates three rows while without GetChanges you iterate all 100 rows. 

    Next is using Entity Framework. This is my recommended approach yet may be intimidating and is more work in the beginning yet overall less code is needed. See this code example (it is written with VS2015 but you can browse the code to get an idea how to use Entity Framework). In this code sample I don't show how to do a GetChanges as in the prior method for DataTable but it's easy e.g. I have loaded Customers into a DataGridView and want to know which rows here added (we can do the same for edit and deleted too),

    Using entity As New DataEntities
        Dim AddedCustomers = entity _
    .ChangeTracker.Entries(Of Customer) _
    .Where(Function(cust) cust.State = EntityState.Added)
    End Using

    The following shows the model in Entity Framework (and we see the same for data wizards).

    Sample interface from the Entity Framework example. Now Entity Framework data loaded into a DataGridView does not support sorting but I use a special class included in the code sample to allow sorting.

    In closing, the choose depends on your business requirements, not simply saving data as this is one small part of the overall picture of an application.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 8, 2017 10:41 AM
    Moderator
  • Hi FahmiZR,

    Please remember to close your thread by marking helpful post as answer, it will be beneficial to other communities who have the same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 10, 2017 1:42 AM
    Moderator