Preventing blank row insert into sqlserver from a gridview in asp.net

See more:

VB

ASP.NET

Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString.ToString())
    'Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConStr").ConnectionString.ToString())


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then

            SetInitialRow()
        End If

    End Sub

    Private Sub SetInitialRow()

        Dim dt As New DataTable()
        Dim dr As DataRow = Nothing
        dt.Columns.Add(New DataColumn("RowNumber", GetType(String)))
        dt.Columns.Add(New DataColumn("Column1", GetType(String)))
        dt.Columns.Add(New DataColumn("Column2", GetType(String)))
        dt.Columns.Add(New DataColumn("Column3", GetType(String)))
        dt.Columns.Add(New DataColumn("Column4", GetType(String)))
        dt.Columns.Add(New DataColumn("Column5", GetType(String)))


        dr = dt.NewRow()
        dr("RowNumber") = 1
        dr("Column1") = String.Empty
        dr("Column2") = String.Empty
        dr("Column3") = String.Empty
        dr("Column4") = String.Empty
        dr("Column5") = String.Empty

        dt.Rows.Add(dr)

        'Store the DataTable in ViewState
        ViewState("CurrentTable") = dt

        Gridview1.DataSource = dt
        Gridview1.DataBind()
    End Sub

    Private Sub SetPreviousData()

        Dim rowIndex As Integer = 0
        If ViewState("CurrentTable") IsNot Nothing Then
            Dim dt As DataTable = DirectCast(ViewState("CurrentTable"), DataTable)
            If dt.Rows.Count > 0 Then
                For i As Integer = 1 To dt.Rows.Count - 1
                    Dim box1 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(1).FindControl("txt_ITEMCODE"), TextBox)
                    Dim box2 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(2).FindControl("txt_NAME"), TextBox)
                    Dim box3 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(3).FindControl("txt_QTY"), TextBox)
                    Dim box4 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(4).FindControl("txt_RATE"), TextBox)
                    Dim box5 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(5).FindControl("txt_AMNT"), TextBox)


                    box1.Text = dt.Rows(i)("Column1").ToString()
                    box2.Text = dt.Rows(i)("Column2").ToString()
                    box3.Text = dt.Rows(i)("Column3").ToString()
                    box4.Text = dt.Rows(i)("Column4").ToString()
                    box5.Text = dt.Rows(i)("Column5").ToString()


                    box5.Text = Convert.ToString(Convert.ToInt32(box3.Text) * Convert.ToInt32(box4.Text))


                    rowIndex += 1
                Next
            End If
        End If


    End Sub


    Private Sub AddNewRowToGrid()

        Dim rowIndex As Integer = 0
        If ViewState("CurrentTable") IsNot Nothing Then
            Dim dtCurrentTable As DataTable = DirectCast(ViewState("CurrentTable"), DataTable)
            Dim drCurrentRow As DataRow = Nothing
            If dtCurrentTable.Rows.Count > 0 Then
                For i As Integer = 1 To dtCurrentTable.Rows.Count
                    'extract the TextBox values
                    Dim box1 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(1).FindControl("txt_ITEMCODE"), TextBox)
                    Dim box2 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(2).FindControl("txt_NAME"), TextBox)
                    Dim box3 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(3).FindControl("txt_QTY"), TextBox)
                    Dim box4 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(4).FindControl("txt_RATE"), TextBox)
                    Dim box5 As TextBox = DirectCast(Gridview1.Rows(rowIndex).Cells(5).FindControl("txt_AMNT"), TextBox)



                    drCurrentRow = dtCurrentTable.NewRow()
                    drCurrentRow("RowNumber") = i + 1
                    drCurrentRow("Column1") = box1.Text
                    drCurrentRow("Column2") = box2.Text
                    drCurrentRow("Column3") = box3.Text
                    drCurrentRow("Column4") = box4.Text
                    drCurrentRow("Column5") = box5.Text

                    rowIndex += 1
                Next

                'add new row to DataTable
                dtCurrentTable.Rows.Add(drCurrentRow)
                'Store the current data to ViewState
                ViewState("CurrentTable") = dtCurrentTable

                'Rebind the Grid with the current data
                Gridview1.DataSource = dtCurrentTable
                Gridview1.DataBind()
            End If
        Else
            Response.Write("ViewState is null")
        End If

        'Set Previous Data on Postbacks
        SetPreviousData()

    End Sub


    Protected Sub ButtonAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
        AddNewRowToGrid()
    End Sub

    Protected Sub InsertAll_Click(ByVal sender As Object, ByVal e As EventArgs)
        conn.Open()

        Dim query As String = "INSERT INTO TRN_MAST(TRN_NO,TRN_DT,REMARKS) VALUES (@TRN_NO,@TRN_DT,@REMARKS)"
        Dim cmd As New SqlCommand(query, conn)

        cmd.Parameters.AddWithValue("@TRN_NO", txtTrnNo.Text)
        cmd.Parameters.AddWithValue("@TRN_DT", txtTrnDt.Text)
        cmd.Parameters.AddWithValue("@REMARKS", txtRemarks.Text)


        cmd.ExecuteNonQuery()

        conn.Close()


        Response.Write(Gridview1.Rows.Count)


        conn.Open()

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

            Dim str As String = "INSERT INTO DTL_MAST(TRN_NO,ITEM_CODE,NAME,QTY,RATE,AMNT) VALUES (@TRN_NO,@ITEM_CODE,@NAME,@QTY,@RATE,@AMNT)"
            Dim cmdInsertAll As New SqlCommand(str, conn)

            cmdInsertAll.Parameters.AddWithValue("@TRN_NO", txtTrnNo.Text)
            cmdInsertAll.Parameters.AddWithValue("@ITEM_CODE", DirectCast(Gridview1.Rows(i).FindControl("txt_ITEMCODE"), TextBox).Text)
            cmdInsertAll.Parameters.AddWithValue("@NAME", DirectCast(Gridview1.Rows(i).FindControl("txt_NAME"), TextBox).Text)
            cmdInsertAll.Parameters.AddWithValue("@QTY", DirectCast(Gridview1.Rows(i).FindControl("txt_QTY"), TextBox).Text)
            cmdInsertAll.Parameters.AddWithValue("@RATE", DirectCast(Gridview1.Rows(i).FindControl("txt_RATE"), TextBox).Text)
            cmdInsertAll.Parameters.AddWithValue("@AMNT", DirectCast(Gridview1.Rows(i).FindControl("txt_AMNT"), TextBox).Text)




            cmdInsertAll.ExecuteNonQuery()

        Next

        conn.Close()

        Response.Write("<Script>alert(' Record inserted')</script>")
        'lblMsg.Text = "Data inserted...";

    End Sub

* when i want to insert a blank row(suppose,i created 3 rows but filled 2) into db, error occurred but in other cases data inserted successfully, please give me a solution.