CODEWITHSUNDEEP
vb.netado.net
user765942
user765942

Reputation: 147

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.

Upvotes: 0

Views: 998

Answers (2)

P. nganga
P. nganga

Reputation: 13

Add a check inside the loop See sample code below

For Each rw As DataGridViewRow In Form1.DataGridView1.Rows

            If rw.Cells("Column2").Value <> Nothing Then

                ITM_ID = rw.Cells("Column1").Value
                DSPL_RCT = rw.Cells("Column2").Value
                SLS_QTY = rw.Cells("Column3").Value
                SLS_PRC = rw.Cells("Column4").Value
                SLS_AMT = rw.Cells("Column5").Value
                TAX_CODE = rw.Cells("Column6").Value

                'INSERT A RECORD INTO THE DATABASE

                Dim cmd As New SqlCommand("INSERT INTO DAY_PLUSALES (DT,ITM_ID,DSPL_RCT,SLS_QTY,SLS_PRC,SLS_AMT,TAX_CODE) values ('" & Form1.TextBox6.Text & "','" & ITM_ID & "','" & DSPL_RCT & "','" & SLS_QTY & "','" & SLS_PRC & "','" & SLS_AMT & "','" & TAX_CODE & "')", con)

                 cmd.ExecuteNonQuery()

End If

        Next

Upvotes: 0

Simon
Simon

Reputation: 6152

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.

Upvotes: 1

Related Questions