none
how to add new rows in datagridview from textbox after view data from sql server use (connectionString) in datagridview ? RRS feed

  • Question

  • after view data from sql server in datagridview use :

                 Try
                    Dim connectionString As String = "connectionString"
                    Dim connection As New SqlConnection(connectionString)
                    Dim sql1 As String = "  SELECT * from Table1 "
                    Dim dataadapter1 As New SqlDataAdapter(sql1, connection)
                    Dim ds1 As New DataSet()
                    connection.Open()
                    dataadapter1.Fill(ds1, "*")
                    connection.Close()
                    DataGridView1.DataSource = ds1
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

    I want add new rows in datagridview after view data in DataGridView1 as the picture :

    Monday, September 18, 2017 9:29 AM

Answers

  • Here I'm using an existing database and table. Note I changed things a little bit.

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private ConnectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=CustomerDatabase;Integrated Security=True"
        Private SelectStatement As String = "SELECT Identifier,CompanyName,ContactName  FROM CustomerDatabase.dbo.Customer"
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Dim dataadapter1 As New SqlDataAdapter(SelectStatement, cn)
                Dim ds1 As New DataSet()
                cn.Open()
                dataadapter1.Fill(ds1)
                DataGridView1.DataSource = ds1.Tables(0)
            End Using
        End Sub
        Private Sub cmdAdd_Click(sender As Object, e As EventArgs) Handles cmdAdd.Click
            CType(DataGridView1.DataSource, DataTable).Rows.Add(New Object() {Nothing, txtContactName.Text, txtContactName.Text})
        End Sub
    End Class
    

    In the above case, Identifier is a primary key which needs to be save to get the new value.

    For the record you can not do this.


    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

    • Marked as answer by srajmuneer Tuesday, September 19, 2017 12:02 PM
    Monday, September 18, 2017 12:44 PM
    Moderator
  • Sure you can, in my example the Identifier column is auto-incrementing which is why it's not one of the values to input into the new row. For me, I would have a function to add a new row and get the new primary key. 

    If doing this in batch you can use DataTable.GetChanges(DataRowState.Added), loop through them and use code similar to the following which returns the new key which can be updated in a new row passed to it.

    Public Function AddNew(ByVal pName As String, ByVal pContactName As String, ByRef pNewIdentifier As Integer) As Boolean
        Using cn As New SqlConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        INSERT INTO [Customer] (CompanyName, ContactName) 
                        VALUES (@CompanyName, @ContactName); 
                        SELECT CAST(scope_identity() AS int);
                    </SQL>.Value
                cmd.Parameters.AddWithValue("@CompanyName", pName)
                cmd.Parameters.AddWithValue("@ContactName", pContactName)
                cn.Open()
                Try
                    pNewIdentifier = CInt(cmd.ExecuteScalar)
                    Return True
                Catch ex As Exception
                    Return False
                End Try
            End Using
        End Using
    End Function


    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

    • Marked as answer by srajmuneer Tuesday, September 19, 2017 11:17 AM
    Tuesday, September 19, 2017 10:36 AM
    Moderator

All replies

  • Working from a DataTable 

    CType(DataGridView1.DataSource,DataTable).Rows.Add(New Object() {TextBox1.Text,TextBox2.Text})

    From a DataSet

    CType(DataGridView1.DataSoucre,DataSet).Tables(0).Rows.Add(New Object() {TextBox1.Text,TextBox2.Text})
    Did the above in notepad.


    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

    Monday, September 18, 2017 11:02 AM
    Moderator
  • Hi srajmuneer , 

    have a look at the documentation of the datagridview.rows 

    https://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.rows(v=vs.110).aspx

    here is a sampel code 

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            DataGridView1.ColumnCount = 3
            DataGridView1.Columns(0).Name = "Product ID"
            DataGridView1.Columns(1).Name = "Product Name"
            DataGridView1.Columns(2).Name = "Product_Price"
    
            Dim row As String() = New String() {"1", "Product 1", "1000"}
            DataGridView1.Rows.Add(row)
            row = New String() {"2", "Product 2", "2000"}
            DataGridView1.Rows.Add(row)
            row = New String() {"3", "Product 3", "3000"}
            DataGridView1.Rows.Add(row)
            row = New String() {"4", "Product 4", "4000"}
            DataGridView1.Rows.Add(row)
        End Sub

    kind regards


    Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; 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.


    • Edited by laurens vdb Monday, September 18, 2017 11:11 AM
    Monday, September 18, 2017 11:08 AM
  • Working from a DataTable 

    CType(DataGridView1.DataSource,DataTable).Rows.Add(New Object() {TextBox1.Text,TextBox2.Text})

    From a DataSet

    CType(DataGridView1.DataSoucre,DataSet).Tables(0).Rows.Add(New Object() {TextBox1.Text,TextBox2.Text})
    thanks Kareninstructor , can use your code into button , What is the final form of the code?
    Monday, September 18, 2017 11:58 AM
  • Here I'm using an existing database and table. Note I changed things a little bit.

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private ConnectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=CustomerDatabase;Integrated Security=True"
        Private SelectStatement As String = "SELECT Identifier,CompanyName,ContactName  FROM CustomerDatabase.dbo.Customer"
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Dim dataadapter1 As New SqlDataAdapter(SelectStatement, cn)
                Dim ds1 As New DataSet()
                cn.Open()
                dataadapter1.Fill(ds1)
                DataGridView1.DataSource = ds1.Tables(0)
            End Using
        End Sub
        Private Sub cmdAdd_Click(sender As Object, e As EventArgs) Handles cmdAdd.Click
            CType(DataGridView1.DataSource, DataTable).Rows.Add(New Object() {Nothing, txtContactName.Text, txtContactName.Text})
        End Sub
    End Class
    

    In the above case, Identifier is a primary key which needs to be save to get the new value.

    For the record you can not do this.


    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

    • Marked as answer by srajmuneer Tuesday, September 19, 2017 12:02 PM
    Monday, September 18, 2017 12:44 PM
    Moderator
  • Hi srajmuneer , 

    have a look at the documentation of the datagridview.rows 

    https://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.rows(v=vs.110).aspx

    here is a sampel code 

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            DataGridView1.ColumnCount = 3
            DataGridView1.Columns(0).Name = "Product ID"
            DataGridView1.Columns(1).Name = "Product Name"
            DataGridView1.Columns(2).Name = "Product_Price"
    
            Dim row As String() = New String() {"1", "Product 1", "1000"}
            DataGridView1.Rows.Add(row)
            row = New String() {"2", "Product 2", "2000"}
            DataGridView1.Rows.Add(row)
            row = New String() {"3", "Product 3", "3000"}
            DataGridView1.Rows.Add(row)
            row = New String() {"4", "Product 4", "4000"}
            DataGridView1.Rows.Add(row)
        End Sub

    kind regards


    Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; 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.



    The OP is loading from a backend database into a DataSet/DataTable so what you have presented will not fit in to how they are coding.

    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

    Monday, September 18, 2017 12:45 PM
    Moderator
  • Hi srajmunner,

    Based on your description, you want to add new record from Textbox into DataGridView, since you have bound with the data source, so you could not use DataGridView.rows.add or DataGridView.rows.insert method, you can consider to insert new record into sql database firstly, then you can reload data into datagridview, like this:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim query As String = "insert into Test7 (Id, Column1,Column2) values (@Id, @Column1, @Column2)"
            Using command As New SqlCommand(query, conn)
                command.Parameters.AddWithValue("@Id", Convert.ToInt32(TextBox1.Text))
                command.Parameters.AddWithValue("@Column1", TextBox2.Text)
                command.Parameters.AddWithValue("@Column2", TextBox3.Text)
                conn.Open()
                command.ExecuteNonQuery()
                conn.Close()
            End Using
            loaddate()
        End Sub
    
        Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loaddate()
        End Sub
        Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Sample\Data1.mdf;Integrated Security=True"
        Dim conn As New SqlConnection(str)
        Private Sub loaddate()
            Dim dt As New DataTable
            Dim sql As String = "select * from Test7"
            Using cmd As New SqlCommand(sql, conn)
                conn.Open()
                Using adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
                conn.Close()
            End Using
        End Sub

    Best Regards,

    Cherry


    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.

    Tuesday, September 19, 2017 3:08 AM
    Moderator
  • Maybe I am not understanding, but this seems like a job for bindingsource.

    Here is a complete class example which you would need a bunch of buttons a dgv and a couple tbs

    Imports System.Data.SqlClient
    
    Public Class Form00_Main
        Dim Dset As New DataSet("Dset")
        Private WithEvents AccountsBS As New BindingSource
        Dim Conn As SqlConnection
        Dim AccountsTableRowCountChange As Boolean = False
    
        Private Sub Form00_Main_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Width = Screen.PrimaryScreen.Bounds.Width - 100
            Height = Screen.PrimaryScreen.Bounds.Height - 100
    
            Dset.Tables.Add(New DataTable("AccountsTable"))
    
            Using Conn As New SqlConnection(My.Settings.dataConnectionString)
    
                Using AccountsDA As New SqlDataAdapter("SELECT * FROM Accounts", Conn)
                    With AccountsDA
                        .MissingSchemaAction = MissingSchemaAction.AddWithKey
                        .FillSchema(Dset.Tables("AccountsTable"), SchemaType.Source)
                        .Fill(Dset.Tables("AccountsTable"))
                    End With
                End Using
                AccountsBS.DataSource = Dset.Tables("AccountsTable")
                BindNav_Accounts.BindingSource = AccountsBS
    
                'TEXTBoxbinding'
                TextBox1.DataBindings.Add("text", AccountsBS, "ID")
                TextBox2.DataBindings.Add("text", AccountsBS, "SaveAs")
    
                Dim ColWidth As Integer = 0
    
                With DGV_Accounts
                    .DataSource = AccountsBS
                    .Columns("id").Visible = False
                    For Each DGVCol As DataGridViewColumn In .Columns
                        If DGVCol.Visible = True Then                       
                             
                                DGVCol.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                                ColWidth += DGVCol.Width + DGVCol.DividerWidth                     
                        End If
                    Next
                End With
    
            End Using
        End Sub
    
        Private Sub AccountsBSChanged() Handles AccountsBS.CurrentChanged
            AccountsTableRowCountChange = True
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button_UpdateAccounts.Click
            Using Conn As New SqlConnection(My.Settings.dataConnectionString)
                Using AccountsDA As New SqlDataAdapter("SELECT * FROM Accounts", Conn)
                    Dim AccountsCB As New SqlCommandBuilder(AccountsDA)
                    AccountsBS.EndEdit()
                    AccountsDA.Update(Dset.Tables("AccountsTable"))
                    If AccountsTableRowCountChange = True Then
                        Dset.Tables("AccountsTable").Rows.Clear()
                        AccountsDA.Fill(Dset.Tables("AccountsTable"))
                        AccountsTableRowCountChange = False
                    End If
                End Using
            End Using
            Button_RevertAccounts.Hide()
            Button_UpdateAccounts.Hide()
        End Sub
    
        Private Sub Button_RevertAccounts_Click(sender As Object, e As EventArgs) Handles Button_RevertAccounts.Click
            Dset.Tables("AccountsTable").RejectChanges()
            Button_RevertAccounts.Hide()
            Button_UpdateAccounts.Hide()
        End Sub
    
        Private Sub DGV_Accounts_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles DGV_Accounts.CurrentCellDirtyStateChanged
            If DGV_Accounts.IsCurrentCellDirty Then
                DGV_Accounts.CommitEdit(DataGridViewDataErrorContexts.Commit)
            End If
        End Sub
    
        Private Sub DGV_Accounts_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DGV_Accounts.CellValueChanged
            Button_RevertAccounts.Show()
            Button_UpdateAccounts.Show()
        End Sub
    
        Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
            Button_RevertAccounts.Show()
            Button_UpdateAccounts.Show()
        End Sub
    
        Private Sub BindingNavigatorAddNewItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorAddNewItem.Click
            Button_RevertAccounts.Show()
            Button_UpdateAccounts.Show()
        End Sub
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi



    • Edited by Gtripodi Tuesday, September 19, 2017 3:48 AM
    Tuesday, September 19, 2017 3:46 AM
  • thanks Kareninstructor , I have had a problem as in the following picture:

    I want to display data temporarily within the datagridview .


    • Edited by srajmuneer Tuesday, September 19, 2017 5:42 AM
    Tuesday, September 19, 2017 5:41 AM
  • Note in my code sample I indicated there were changes e.g. the following.

    DataGridView1.DataSource = ds1.Tables(0)


    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

    Tuesday, September 19, 2017 8:38 AM
    Moderator
  • thank you very much Kareninstructor, can add (Identifier) number temporarily within the column Identifier ? who so?
    Tuesday, September 19, 2017 10:26 AM
  • Sure you can, in my example the Identifier column is auto-incrementing which is why it's not one of the values to input into the new row. For me, I would have a function to add a new row and get the new primary key. 

    If doing this in batch you can use DataTable.GetChanges(DataRowState.Added), loop through them and use code similar to the following which returns the new key which can be updated in a new row passed to it.

    Public Function AddNew(ByVal pName As String, ByVal pContactName As String, ByRef pNewIdentifier As Integer) As Boolean
        Using cn As New SqlConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        INSERT INTO [Customer] (CompanyName, ContactName) 
                        VALUES (@CompanyName, @ContactName); 
                        SELECT CAST(scope_identity() AS int);
                    </SQL>.Value
                cmd.Parameters.AddWithValue("@CompanyName", pName)
                cmd.Parameters.AddWithValue("@ContactName", pContactName)
                cn.Open()
                Try
                    pNewIdentifier = CInt(cmd.ExecuteScalar)
                    Return True
                Catch ex As Exception
                    Return False
                End Try
            End Using
        End Using
    End Function


    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

    • Marked as answer by srajmuneer Tuesday, September 19, 2017 11:17 AM
    Tuesday, September 19, 2017 10:36 AM
    Moderator