How to not insert the null row in data grid view to sql table vb.net

Question

I have a data grid view and i insert data in it. When i type a row it automatically open a new row, That's good, but it inserts the null row to the database.

how to not insert it in the database.

My code:

con.Open()
    For Each rw As DataGridViewRow In dgv_Deductions.Rows
        cmd = New SqlCommand("insert into SalDeduction (EmpNo, DeductionMonth,DeductionType,Amount,Remarks,tDate) values (" & txt_EmpNo.Text & ",'" & cmb_PaymentMonth.Text & "','" & rw.Cells(0).Value & "','" & rw.Cells(1).Value & "','" & rw.Cells(2).Value & "','" & Now() & "') ", con)
        cmd.ExecuteNonQuery()
    Next
    con.Close()

Sounds like you are executing that code from an event prematurely. Note that you dont need that code. A DGV is meant to work with a DataTable, which in turn will update the database for you using a DataAdapter. — Ňɏssa Pøngjǣrdenlarp, Jan 01 '17 at 21:05
As @Plutonix suggests, you should be using a bound `DataTable`. If you did that then the issue you're currently encountering would not exist. You should be creating a `DataTable` and binding it to the grid, then calling `Update` on a data adapter to save all the changes from that `DataTable` back to the database. You can construct the `DataTable` manually or by calling `FillSchema` on the same data adapter. — jmcilhinney, Jan 02 '17 at 01:09
[Basics to creating a DataTable and DataAdapter](http://stackoverflow.com/a/33702351/1070452) — Ňɏssa Pøngjǣrdenlarp, Jan 02 '17 at 01:24

jmcilhinney · Answer 1 · 2017-01-02T01:24:36.237

To answer your question as asked, you would do this:

For Each row In DataGridView1.Rows.
                              Cast(Of DataGridViewRow)().
                              Where(Function(dgvr) Not dgvr.IsNewRow)
    '...
Next

I would strongly recommend against that though. You would do better to create a DataTable and bind that to the grid, then save all the changes in a batch with a data adapter. You might construct the DataTable manually, e.g.

Private table As DataTable

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    With Me.table.Columns
        .Add("ID", GetType(Integer))
        .Add("Name", GetType(String))
    End With

    Me.DataGridView1.DataSource = Me.table
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Using connection As New SqlConnection("connection string here"),
          command As New SqlCommand("INSERT INTO MyTable (ID, Name) VALUES (@ID, @Name)", connection),
          adapter As New SqlDataAdapter With {.InsertCommand = command}
        '...

        adapter.Update(Me.table)
    End Using
End Sub

Alternatively, you can use the data adapter to construct the table, e.g.

Private table As DataTable
Private adapter As SqlDataAdapter
Private builder As SqlCommandBuilder

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.table = New DataTable
    Me.adapter = New SqlDataAdapter("SELECT * FROM MyTable", "connection string here") With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    Me.builder = New SqlCommandBuilder(Me.adapter)

    Me.adapter.FillSchema(Me.table, SchemaType.Source)
    Me.DataGridView1.DataSource = Me.table
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Me.adapter.Update(Me.table)
End Sub

That example uses a command builder but you can also build the InsertCommand yourself if you want to.

I used the Datatable example but i got this "Object reference not set to an instance of an object." in this area: .Add("ID", GetType(Integer)) .Add("Name", GetType(String)) — LeoJb, Jan 02 '17 at 12:08
I used the data adapter example and it was good. but now i want the field "Name" to be as a combo box and the data inside it like "John", " Micheal", etc. — LeoJb, Jan 02 '17 at 12:32
That has nothing to do with the topic of this question. If this answer addresses the question you asked then you should accept it and if you have a new question then you should post a new question. — jmcilhinney, Jan 02 '17 at 13:25

How to not insert the null row in data grid view to sql table vb.net

1 Answers1