CODEWITHSUNDEEP
vb.net
LeoJb
LeoJb

Reputation: 25

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

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()

Upvotes: 0

Views: 801

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54417

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.

Upvotes: 1

Related Questions