user765942 user765942 - 1 month ago 19
Vb.net Question

Insert adding an extra row in database

When I insert data from my datagridview to my database an extra row is inserted at the end which contains no data. How can I overcome this issue. Each time I click on a new line in my datagridview and start the enter data, a new line is enabled below. Is there any way I can stop this blank line being inserted into my database? Thanks, here's my code...

Dim ds As New DataSet
ds.Tables.Add("Main")

Dim col As New DataColumn

Dim dgvCol As New DataGridViewColumn
For Each dgvCol In DataGridView1.Columns
col = New DataColumn(dgvCol.Name)
ds.Tables("Main").Columns.Add(col)
Next
Dim row As DataRow
Dim colcount As Integer = DataGridView1.Columns.Count - 1

For i As Integer = 0 To DataGridView1.Rows.Count - 1
row = ds.Tables("Main").Rows.Add

For Each column As DataGridViewColumn In DataGridView1.Columns
row.Item(column.Index) = DataGridView1.Rows.Item(i).Cells(column.Index).Value
Next

Next


Dim con As New SqlClient.SqlConnection
Dim myCommand As New SqlClient.SqlCommand

Dim dt As New DataTable()
dt = ds.Tables("Main")

For i As Integer = 0 To dt.Rows.Count - 1

Dim myAdapter As New SqlClient.SqlDataAdapter
Dim sql As String = "Insert into details (company, division, date, supplier, material_group, cost, dsc, email, userID, fullName, marketingCode, hccNumber, wbs, qty, currency, timestamp) VALUES ('" & DataGridView1.Rows(i).Cells(company.Name).Value & "', '" & DataGridView1.Rows(i).Cells(division.Name).Value & "','" & calendar & "', '" & DataGridView1.Rows(i).Cells(supplier.Name).Value & "', '" & DataGridView1.Rows(i).Cells(materialGroup.Name).Value & "', '" & DataGridView1.Rows(i).Cells(netprice.Name).Value & "', '" & DataGridView1.Rows(i).Cells(description.Name).Value & "', '" & cmbEmail.SelectedValue & "', '" & id & "', '" & newName & "', '" & DataGridView1.Rows(0).Cells(markCodes.Name).Value & "', '" & DataGridView1.Rows(0).Cells(hccNumber.Name).Value & "', '" & DataGridView1.Rows(i).Cells(wba.Name).Value & "', '" & DataGridView1.Rows(i).Cells(quantity.Name).Value & "', '" & DataGridView1.Rows(i).Cells(currency.Name).Value & "', '" & nowDate & "')"
myCommand.Connection = con
myCommand.CommandText = sql
myAdapter.InsertCommand = myCommand
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Next.

Answer

You'll need your check your row for values in your loop try checking each cell (or a specific cell) for DBNull.Value before saving it. Alternatively you can set AllowUsersToAddRows to false to prevent the blank row from showing.