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
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 1, 2017 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
– jmcilhinney, Commented Jan 2, 2017 at 1:09

jmcilhinney · Accepted Answer · 2017-01-02 01:24:36Z

1

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.

edited Jan 2, 2017 at 1:24

answered Jan 2, 2017 at 1:13

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LeoJb Over a year ago

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 Over a year ago

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.

jmcilhinney Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related