none
Insert new row from datagridview

    Question

  • Hi

    How to insert new row from datagrid and delete here is my code

    Imports System.Data
    Imports System.Data.SqlClient
    Public Class Form3
        Private connectionString As String
        Private commandText As String
        Private SqlConn As SqlConnection
        Private SqlCommBuilder As SqlCommandBuilder
        Private SqlDataAdapter As SqlDataAdapter
        Private dtSource As DataTable
        Private bdSource As BindingSource
        Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            FillData()
        End Sub
        Private Sub FillData()
            connectionString = "Data Source=;Initial Catalog=;User Id=;Password=;"
            commandText = "SELECT * FROM cities order by Cy_Name"
            SqlConn = New SqlConnection(connectionString)
            SqlDataAdapter = New SqlDataAdapter(commandText, SqlConn)
            SqlCommBuilder = New SqlCommandBuilder(SqlDataAdapter)
            dtSource = New DataTable()
            SqlDataAdapter.Fill(dtSource)
            bdSource = New BindingSource()
            bdSource.DataSource = dtSource
            ' dtSource is a DataTable which pick up the data from the database.
            'bindingNavigator1.BindingSource = bdSource;
            Me.Dg1.DataSource = bdSource
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            SqlDataAdapter.Update(dtSource)
        End Sub


    adil


    • Edited by adilahmed Saturday, December 29, 2012 5:54 AM
    Wednesday, December 26, 2012 11:50 AM

Answers

  • I create a new project with just a DatGridview and a button.  Then added the code below and did not get an error.  I suspect one fot eh following is occuring

    1) There is something wrong the way you are adding the DataGridView to your project

    2) You have an event that is corrupting the DataGridView

    3) You have some code that is deleting the DataGridview

    I would start commenting out sections of yhour code to determine where the actual problem is located.  Or start a new project and add your code a little bit at a time until you find what is really happening.  If you have events in your code try stepping through the code. 

    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Me.Dg1.DataSource = Nothing
            Me.Dg1.Rows.Clear()
        End Sub
    End Class


    jdweng

    Thursday, December 27, 2012 10:14 AM
  • Adil,

    If you try to create cleaner code then it it has two advantages. Others can quiclier investigate your code, you can do that yourself also and then it is easier to correct that.

    I made some changes in your code, try what I did including creating as few as possible global objects and start naming those with an uppercase and the object in the methods with a lowercase.

    Imports System.Data.SqlClient
    Public Class Form1
        Private SqlDataAdapter As SqlDataAdapter
        Private DtSource As DataTable
        Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            FillData()
        End Sub
        Private Sub FillData()
            Dim connectionString = "Data Source=X;Initial Catalog=s1;User Id=_user;Password=TheUser;"
            Dim commandText = "SELECT * FROM cities order by Cy_Name"
            Dim sqlConn As New SqlConnection(connectionString)
            SqlDataAdapter = New SqlDataAdapter(commandText, sqlConn)
            DtSource = New DataTable() 'this forces your table to be renewed, the old one will be released by the GC
            SqlDataAdapter.Fill(DtSource)
            Dim bdSource As New BindingSource()
            bdSource.DataSource = DtSource 'this forces your bindingsource to be renewed
            ' dtSource is a DataTable which pick up the data from the database.
            'bindingNavigator1.BindingSource = bdSource;
            DataGridView1.DataSource = bdSource
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            DataGridView1.EndEdit() 'this forces a row change. Data is only updated after a row change in a control
            Dim SqlCommBuilder As New SqlCommandBuilder(SqlDataAdapter)
            SqlDataAdapter.Update(dtSource)
        End Sub
    End Class
    Not tested, also avoid putting connectionstrings in a forum.


    Success
    Cor



    • Marked as answer by Shanks ZenModerator Thursday, January 03, 2013 8:55 AM
    • Edited by Cor LigthertMVP Thursday, January 03, 2013 11:27 AM changed initial database and user, password and datasource where already changed
    Thursday, December 27, 2012 11:30 AM

All replies

  • Try clearing the datagridview before adding new items

    Me.Dg1.Rows.Clear()     <------- add this line of code

    Me.Dg1.DataSource = bdSource


    jdweng


    Wednesday, December 26, 2012 1:14 PM
  • Hi i am getting the error

    Me.Dg1.Rows.Clear()  ;Here

    When i did like this in AddButton Datagridview showin no rows

    and when i want to create new rows just i want to add two columns only when datagrid loads

    its showin 5 columns from cities table . but i want to add only cityid and cityname

        Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Me.Dg1.DataSource = Nothing
            Me.Dg1.Rows.Clear()
            Me.Dg1.DataSource = bdSource
        End Sub


    adil

    Thursday, December 27, 2012 8:33 AM
  • I create a new project with just a DatGridview and a button.  Then added the code below and did not get an error.  I suspect one fot eh following is occuring

    1) There is something wrong the way you are adding the DataGridView to your project

    2) You have an event that is corrupting the DataGridView

    3) You have some code that is deleting the DataGridview

    I would start commenting out sections of yhour code to determine where the actual problem is located.  Or start a new project and add your code a little bit at a time until you find what is really happening.  If you have events in your code try stepping through the code. 

    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Me.Dg1.DataSource = Nothing
            Me.Dg1.Rows.Clear()
        End Sub
    End Class


    jdweng

    Thursday, December 27, 2012 10:14 AM
  • Adil,

    If you try to create cleaner code then it it has two advantages. Others can quiclier investigate your code, you can do that yourself also and then it is easier to correct that.

    I made some changes in your code, try what I did including creating as few as possible global objects and start naming those with an uppercase and the object in the methods with a lowercase.

    Imports System.Data.SqlClient
    Public Class Form1
        Private SqlDataAdapter As SqlDataAdapter
        Private DtSource As DataTable
        Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            FillData()
        End Sub
        Private Sub FillData()
            Dim connectionString = "Data Source=X;Initial Catalog=s1;User Id=_user;Password=TheUser;"
            Dim commandText = "SELECT * FROM cities order by Cy_Name"
            Dim sqlConn As New SqlConnection(connectionString)
            SqlDataAdapter = New SqlDataAdapter(commandText, sqlConn)
            DtSource = New DataTable() 'this forces your table to be renewed, the old one will be released by the GC
            SqlDataAdapter.Fill(DtSource)
            Dim bdSource As New BindingSource()
            bdSource.DataSource = DtSource 'this forces your bindingsource to be renewed
            ' dtSource is a DataTable which pick up the data from the database.
            'bindingNavigator1.BindingSource = bdSource;
            DataGridView1.DataSource = bdSource
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            DataGridView1.EndEdit() 'this forces a row change. Data is only updated after a row change in a control
            Dim SqlCommBuilder As New SqlCommandBuilder(SqlDataAdapter)
            SqlDataAdapter.Update(dtSource)
        End Sub
    End Class
    Not tested, also avoid putting connectionstrings in a forum.


    Success
    Cor



    • Marked as answer by Shanks ZenModerator Thursday, January 03, 2013 8:55 AM
    • Edited by Cor LigthertMVP Thursday, January 03, 2013 11:27 AM changed initial database and user, password and datasource where already changed
    Thursday, December 27, 2012 11:30 AM
  • Hi can u clear connection string from  ur post

    and  i am doing here

    when i create new city just i need to enter new citycode and cityname in datagridview

    for this when i click Add button i cleared the datagridview

     Private Sub ClearDatagrid()
            Me.DataGridView1.DataSource = Nothing
            Datagridview1.DataBindings.Clear()
            Me.Datagridview1.Rows.Clear()
        End Sub

    and added columns

        Me.Datagridview1.ColumnCount = 2
            Me.Datagridview1.Columns(0).HeaderText = "City Code1"
            Me.Datagridview1.Columns(1).HeaderText = "City Name2"
            Me.Datagridview1.AllowUserToAddRows = True
            Me.Datagridview1.Rows(0).Selected = True

    and now datagridview shows two columns  and save button and cancel button enabled

    and here i need when user cancel the i need to clear newly added columns and

    filldatagridview back with data.




    adil


    • Edited by adilahmed Saturday, December 29, 2012 6:18 AM
    Saturday, December 29, 2012 6:09 AM
  • If you want to start with an empty object simply do this

     DataGridView1 = New DataGridView

     

    Do you have 1 or 2 DataGridview object on your form?  You original code had Me.Dg1 and your latest code has Me.DataGridview1.


    jdweng

    Saturday, December 29, 2012 12:02 PM