how to add a blank row to empty grid view and how to delete a row in asp.net

To add a blank row when there is no data in a grid view and at the same time deleting an existing row from a grid view

inline coding for gridview

<asp:GridView ID="gvClassDetails" runat="server" CellPadding="4" AutoGenerateColumns="false"

                        CssClass="GridText" ShowFooter="true" OnRowDeleting="gvClassDetails_RowDeleting">

                        <FooterStyle BackColor="#889FD0" Font-Bold="True" ForeColor="White" />

                        <RowStyle BackColor="#F0F3FA" ForeColor="#333333" />

                        <HeaderStyle BackColor="#889FD0" Font-Bold="True" ForeColor="White" />

                        <AlternatingRowStyle BackColor="White" />

                        <Columns>

                            <asp:TemplateField HeaderText="Course Date (dd/mm/yyyy)">

                                <ItemTemplate>

                                    <asp:TextBox ID="txtCourseDate" runat="server" Width="120px" CssClass="txt_a"></asp:TextBox>

                                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtCourseDate"

                                        ErrorMessage="Course Date is required" Text="*" SetFocusOnError="True"></asp:RequiredFieldValidator>

                                    <asp:RegularExpressionValidator ID="revCourseDate" runat="server" ControlToValidate="txtCourseDate"

                                        ErrorMessage="Invalid Course Date" ValidationExpression="^(((0[1-9]|[12]\d|3[01])\/(0[13578]|1[02])\/((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\/(0[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\/02\/((19|[2-9]\d)\d{2}))|(29\/02\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))$"

                                        SetFocusOnError="true" Text="*"></asp:RegularExpressionValidator>

                                </ItemTemplate>

                            </asp:TemplateField>

                            <asp:TemplateField HeaderText="Course Time">

                                <ItemTemplate>

                                    <asp:DropDownList ID="ddlTime1" runat="server" CssClass="txt_a" Width="50">

                                    </asp:DropDownList>

                                    &nbsp;

                                    <asp:DropDownList ID="ddlTime2" runat="server" CssClass="txt_a" Width="50">

                                    </asp:DropDownList>

                                    &nbsp;

                                    <asp:DropDownList ID="ddlTime3" runat="server" CssClass="txt_a" Width="50">

                                        <asp:ListItem Value="AM"></asp:ListItem>

                                        <asp:ListItem Value="PM"></asp:ListItem>

                                    </asp:DropDownList>

                                    &nbsp;

                                    <asp:DropDownList ID="ddlTime4" runat="server" CssClass="txt_a" Width="50">

                                    </asp:DropDownList>

                                    &nbsp;

                                    <asp:DropDownList ID="ddlTime5" runat="server" CssClass="txt_a" Width="50">

                                    </asp:DropDownList>

                                    &nbsp;

                                    <asp:DropDownList ID="ddlTime6" runat="server" CssClass="txt_a" Width="50">

                                        <asp:ListItem Value="AM"></asp:ListItem>

                                        <asp:ListItem Value="PM" Selected="True"></asp:ListItem>

                                    </asp:DropDownList>

                                </ItemTemplate>

                            </asp:TemplateField>

                            <asp:TemplateField HeaderText="Credit Hours">

                                <ItemTemplate>

                                    <asp:TextBox ID="txtCrHr" runat="server" Width="100px" CssClass="txt_a" MaxLength="5"

                                        onkeypress="return isNumberKey(event)"></asp:TextBox>

                                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtCrHr"

                                        ErrorMessage="Credit Hours is required" SetFocusOnError="True" Text="*"></asp:RequiredFieldValidator>

                                </ItemTemplate>

                                <FooterStyle HorizontalAlign="Right" />

                                <FooterTemplate>

                                    <asp:Button ID="ButtonAdd" runat="server" Text="Add Blank Row" OnClick="ButtonAdd_Click"

                                        CausesValidation="true" Font-Names="Tahoma" />

                                </FooterTemplate>

                            </asp:TemplateField>

                            <asp:CommandField ShowDeleteButton="True" CausesValidation="true" />

                        </Columns>

                    </asp:GridView>

server side code in vb.net

  Private Sub ClassDetailsEmpty()
        Dim dt As New DataTable()
        Dim dr As DataRow = Nothing
        dt.Columns.Add(New DataColumn("RowNumber", GetType(String)))
        dt.Columns.Add(New DataColumn("Col1", GetType(String)))
        dt.Columns.Add(New DataColumn("Col2", GetType(String)))
        dt.Columns.Add(New DataColumn("Col3", GetType(String)))
        dt.Columns.Add(New DataColumn("Col4", GetType(String)))
        dt.Columns.Add(New DataColumn("Col5", GetType(String)))
        dt.Columns.Add(New DataColumn("Col6", GetType(String)))
        dt.Columns.Add(New DataColumn("Col7", GetType(String)))
        dt.Columns.Add(New DataColumn("Col8", GetType(String)))

        dr = dt.NewRow()
        dr("RowNumber") = 1
        dr("Col1") = String.Empty
        dr("Col2") = String.Empty
        dr("Col3") = String.Empty
        dr("Col4") = String.Empty
        dr("Col5") = String.Empty
        dr("Col6") = String.Empty
        dr("Col7") = String.Empty
        dr("Col8") = String.Empty
        dt.Rows.Add(dr)

        ViewState("CurrentTable") = dt

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

    Private Sub AddNewRow()
        Dim AddrowIndex 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
                    Dim txtCdate As TextBox = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(0).FindControl("txtCourseDate"), TextBox)
                    Dim ddlTime1 As DropDownList = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(1).FindControl("ddlTime1"), DropDownList)
                    Dim ddlTime2 As DropDownList = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(1).FindControl("ddlTime2"), DropDownList)
                    Dim ddlTime3 As DropDownList = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(1).FindControl("ddlTime3"), DropDownList)
                    Dim ddlTime4 As DropDownList = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(1).FindControl("ddlTime4"), DropDownList)
                    Dim ddlTime5 As DropDownList = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(1).FindControl("ddlTime5"), DropDownList)
                    Dim ddlTime6 As DropDownList = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(1).FindControl("ddlTime6"), DropDownList)
                    Dim txtCrHr As TextBox = DirectCast(gvClassDetails.Rows(AddrowIndex).Cells(2).FindControl("txtCrHr"), TextBox)

                    drCurrentRow = dtCurrentTable.NewRow()
                    drCurrentRow("RowNumber") = i + 1

                    dtCurrentTable.Rows(i - 1)("Col1") = txtCdate.Text
                    dtCurrentTable.Rows(i - 1)("Col2") = ddlTime1.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col3") = ddlTime2.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col4") = ddlTime3.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col5") = ddlTime4.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col6") = ddlTime5.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col7") = ddlTime6.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col8") = txtCrHr.Text
                    AddrowIndex += 1
                Next
                dtCurrentTable.Rows.Add(drCurrentRow)
                ViewState("CurrentTable") = dtCurrentTable

                gvClassDetails.DataSource = dtCurrentTable
                gvClassDetails.DataBind()
            End If
        Else
            Alert("ViewState is null")
        End If
        SetPreviousData()
    End Sub

    Private Sub SetPreviousData()
        Dim ProwIndex As Integer = 0
        If ViewState("CurrentTable") IsNot Nothing Then
            Dim dtPrevious As DataTable = DirectCast(ViewState("CurrentTable"), DataTable)
            If dtPrevious.Rows.Count > 0 Then
                For i As Integer = 0 To dtPrevious.Rows.Count - 1
                    Dim txtCdate As TextBox = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(0).FindControl("txtCourseDate"), TextBox)
                    Dim ddlTime1 As DropDownList = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(1).FindControl("ddlTime1"), DropDownList)
                    Dim ddlTime2 As DropDownList = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(1).FindControl("ddlTime2"), DropDownList)
                    Dim ddlTime3 As DropDownList = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(1).FindControl("ddlTime3"), DropDownList)
                    Dim ddlTime4 As DropDownList = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(1).FindControl("ddlTime4"), DropDownList)
                    Dim ddlTime5 As DropDownList = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(1).FindControl("ddlTime5"), DropDownList)
                    Dim ddlTime6 As DropDownList = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(1).FindControl("ddlTime6"), DropDownList)
                    Dim txtCrHr As TextBox = DirectCast(gvClassDetails.Rows(ProwIndex).Cells(2).FindControl("txtCrHr"), TextBox)

                    If Not (dtPrevious.Rows(i)("Col1") Is System.DBNull.Value) Then
                        txtCdate.Text = dtPrevious.Rows(i)("Col1").ToString().Trim
                    End If

                    ddlTime1.SelectedValue = dtPrevious.Rows(i)("Col2").ToString()
                    ddlTime2.SelectedValue = dtPrevious.Rows(i)("Col3").ToString()
                    ddlTime3.SelectedValue = dtPrevious.Rows(i)("Col4").ToString()
                    ddlTime4.SelectedValue = dtPrevious.Rows(i)("Col5").ToString()
                    ddlTime5.SelectedValue = dtPrevious.Rows(i)("Col6").ToString()
                    ddlTime6.SelectedValue = dtPrevious.Rows(i)("Col7").ToString()
                    txtCrHr.Text = dtPrevious.Rows(i)("Col8").ToString()
                    ProwIndex += 1
                Next
            End If
        End If
    End Sub

    Private Sub SetRowData()
        Dim rowIndex1 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
                    Dim txtCdate As TextBox = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(0).FindControl("txtCourseDate"), TextBox)
                    Dim ddlTime1 As DropDownList = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(1).FindControl("ddlTime1"), DropDownList)
                    Dim ddlTime2 As DropDownList = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(1).FindControl("ddlTime2"), DropDownList)
                    Dim ddlTime3 As DropDownList = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(1).FindControl("ddlTime3"), DropDownList)
                    Dim ddlTime4 As DropDownList = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(1).FindControl("ddlTime4"), DropDownList)
                    Dim ddlTime5 As DropDownList = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(1).FindControl("ddlTime5"), DropDownList)
                    Dim ddlTime6 As DropDownList = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(1).FindControl("ddlTime6"), DropDownList)
                    Dim txtCrHr As TextBox = DirectCast(gvClassDetails.Rows(rowIndex1).Cells(2).FindControl("txtCrHr"), TextBox)
                    drCurrentRow = dtCurrentTable.NewRow()
                    drCurrentRow("RowNumber") = i + 1

                    dtCurrentTable.Rows(i - 1)("Col1") = txtCdate.Text
                    dtCurrentTable.Rows(i - 1)("Col2") = ddlTime1.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col3") = ddlTime2.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col4") = ddlTime3.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col5") = ddlTime4.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col6") = ddlTime5.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col7") = ddlTime6.SelectedValue
                    dtCurrentTable.Rows(i - 1)("Col8") = txtCrHr.Text

                    rowIndex1 += 1
                Next
                ViewState("CurrentTable") = dtCurrentTable
            End If
        Else
            Response.Write("ViewState is null")
        End If
    End Sub

    Private Sub SaveClassDetails(ByVal ClassId As Integer)
        Try
            Dim Result As Long
            SetRowData()
            Dim dtTable As DataTable = TryCast(ViewState("CurrentTable"), DataTable)

            If dtTable IsNot Nothing Then
                For Each row As DataRow In dtTable.Rows
                    Dim Coursedt As New DateTime()
                    Coursedt = objCommLib.dateformatmmddyyyy(row.ItemArray(1))
                    Dim CrHrs As String = TryCast(row.ItemArray(8), String)
                    Dim CourseTimeFrom As String = TryCast(row.ItemArray(2), String) & ":" & TryCast(row.ItemArray(3), String) & ":" & TryCast(row.ItemArray(4), String)
                    Dim CourseTimeTo As String = TryCast(row.ItemArray(5), String) & ":" & TryCast(row.ItemArray(6), String) & ":" & TryCast(row.ItemArray(7), String)
                    Result = objConnLib.SetClassDetails(ClassId, Coursedt, CourseTimeFrom, CourseTimeTo, "ADD", CrHrs)
                Next
            End If
        Catch ex As Exception
            Alert("Error while saving class details")
        End Try
    End Sub

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

    Protected Sub gvClassDetails_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
        SetRowData()
        If ViewState("CurrentTable") IsNot Nothing Then
            Dim dt As DataTable = DirectCast(ViewState("CurrentTable"), DataTable)
            Dim drCurrentRow As DataRow = Nothing
            Dim rowIndex As Integer = Convert.ToInt32(e.RowIndex)
            If dt.Rows.Count > 1 Then
                dt.Rows.Remove(dt.Rows(rowIndex))
                drCurrentRow = dt.NewRow()
                ViewState("CurrentTable") = dt
                gvClassDetails.DataSource = dt
                gvClassDetails.DataBind()

                SetPreviousData()
            End If
        End If
    End Sub

    Protected Sub gvClassDetails_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvClassDetails.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Or e.Row.RowType = ListItemType.AlternatingItem Or e.Row.RowType = ListItemType.Item Then
            Dim ddl1 As DropDownList = e.Row.FindControl("ddlTime1")
            Dim ddl2 As DropDownList = e.Row.FindControl("ddlTime2")
            Dim ddl4 As DropDownList = e.Row.FindControl("ddlTime4")
            Dim ddl5 As DropDownList = e.Row.FindControl("ddlTime5")
            objCommLib.BindHr(ddl1)
            objCommLib.BindMinute(ddl2)
            objCommLib.BindHr(ddl4)
            objCommLib.BindMinute(ddl5)
        End If
    End Sub

    Public Sub Alert(ByVal msg As String)
        Page.ClientScript.RegisterStartupScript([GetType], "alert", "<script language='javascript'>alert('" & msg & "');</script>")
    End Sub

how to select all check boxes in a grid view in asp.net?

to select all check boxes in a gridview using javascript

<script type="text/javascript" language="javascript">
        function SelectAll(id) {
            var grid = document.getElementById("<%= gv.ClientID %>");
            var cell;

            if (grid.rows.length > 0) {
                for (i = 1; i < gv.rows.length; i++) {
                    cell = grid.rows[i].cells[0];
                    for (j = 0; j < cell.childNodes.length; j++) {
                        if (cell.childNodes[j].type == "checkbox") {
                            cell.childNodes[j].checked = id.checked;
                        }
                    }
                }
            }
        }
    </script>

Usage :
<asp:CheckBox ID="ChkAll" runat="server" onclick="javascript:SelectAll(this);" />

how to get auto increment number in a grid view?

use the following template field in asp.net gridview to get the auto increment number

<asp:TemplateField HeaderText="No">
    <ItemTemplate>
        <%# Container.DataItemIndex + 1 %>
    </ItemTemplate>
</asp:TemplateField>

how to find a particular table used in stored procedure

- Some times we may change the structure of a table or add new columns or delete some unused columns
- In this case, we need to find that particular atble used in our stored procedures
-write the following query to find

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so on
sc.id=so.id WHERE sc.text LIKE '%TableName%'

set count ON in stored procedures in sql server

-while writing a stored procedure, use SET NOCOUNT ON  to stop showing the messages like  'number of rows affected'
-It may reduce the network traffice and the overall performance of a database would increase

important tips for writing sql queries

some tips to remember while writing sql queries

- avoid using * to select columns from a table, instead use column names like select col1,col2 etc., it will improve the performance of sql query
- use [ ] for column names to avoid conflict with pre-defined key words
- use upper case letters for all predefined types, like SELECT [col1] FROM [TableName]
- use COUNT(ColName) insteaed of using SELECT COUNT(*) from a table
- avoid/Minimize the  use of set of sub queries, because it may affect on performance.

how to validate a text box in asp.net using javascript

<script language="javascript" type="text/javascript">
        function EmptyText() {
            if (document.getElementById("txtStaffNo").value == "") {
                alert("Please provide Staff ID");
                document.getElementById("txtStaffNo").focus();
                return false;
            }
        }
    </script>

Usgae:

<asp:Button ID="btnOk" runat="server" Text="OK" OnClientClick="return EmptyText();"/>

how to call .ascx page / user control in aspx page

user controls can be called inside an aspx page by registering it first

write this tag inside aspx page

<%@ Register Src="~/Test.ascx" TagName="TestAscx" TagPrefix="uc1" %>

Usage:
<uc1:TestAscx ID="asd" runat="server" />

how to refresh parent page from child page or popup window

To refresh the parent page on child page closing,
write this code in aspx page body tag of child window

<body onunload="window.opener.location.reload();">

close the child page once operation is done.

Page.ClientScript.RegisterStartupScript([GetType], "Close", "javascript:window.close();", True)