how to get total no of days in a month using sql server

To get the total no of days in a month in sql

Query

DECLARE @sample datetime
SET @sample=GETDATE()  -- Sample date
select datediff(dd,dateadd(dd, 1-day(@sample),@sample), dateadd(m,1,dateadd(dd, 1-day(@sample),@sample)))

how to filter a datatable or dataset in asp.net

To filter the records returned by a dataset or datatble to meet the requirements, use the following code

VB.Net code

Dim dtView As DataView = ds.Tables(0).DefaultView
dtView.RowFilter = "TableColumnName NOT IN ('blabla')"
gv.DataSource = dtView
gv.DataBind()

Gridview Link button click in asp.net

To open a new window on click of gridview link button in asp.net

VB.Net code

<asp:TemplateField HeaderText="View">
                                    <ItemTemplate>
                                        <asp:LinkButton ID="linkButton" runat="server" CommandName='<%#dataBinder.Eval(Container.DataItem,"ID").ToString()%>'
                                            Text='View Attendance' OnClick="linkButton_Click" CausesValidation="false"></asp:LinkButton>
                                    </ItemTemplate>
                                </asp:TemplateField>

Protected Sub linkButton_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim Str As String = sender.CommandName.ToString()
        Dim Url As String = "Sample.aspx" & "?id=" & Str
        Dim UrlScript As String = ""
        UrlScript = "<script language='javascript'>window.open('" & Url & "', 'Export', 'location=0,status=0,scrollbars=1,toolbar=0,menubar=0,directories=0'); </script>"
        Page.ClientScript.RegisterStartupScript([GetType], "JavaScript", UrlScript)
    End Sub

To loop through the each record in a gridview in asp.net

VB.Net Code

If gv.Rows.Count > 0 Then
            For Each row As GridViewRow In gv.Rows
                Dim linkButton As LinkButton = row.FindControl("lnkDel")
                linkButton.Enabled = False
                linkButton.Attributes.Add("readonly", "readonly")
            Next
 End If

link button in gridview to delete a record in asp.net

To delete a record when the user clicks on a link button of a grid view in asp.net, use the following code block

VB.Net Code

<asp:TemplateField HeaderText="Delete">
                                <ItemTemplate>
                                    <span onclick="return confirm('Are you sure to Delete the user?')">
                                        <asp:LinkButton runat="server" ID="lnkDel" Text="Delete" CommandArgument='<%# Eval("Id") %>'
                                            CommandName="Delete" CausesValidation="False"></asp:LinkButton>
                                </ItemTemplate>
                                <HeaderStyle Width="60px"></HeaderStyle>
                                <ItemStyle HorizontalAlign="Center"></ItemStyle>
                            </asp:TemplateField>

    Protected Sub gv_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles gvUsers.RowDeleting
        Dim _Result As String
        _Result = gv.Rows(e.RowIndex).Cells(0).Text)
    End Sub

how to append an item to a drop down list in asp.net

some times we may need to append an item a drop down list in asp.net for user to understand easily

VB.Net Code

ddl.Items.Insert(0, New ListItem("-- Please Select --", "-"))

To hide a column in gridview

<style type="text/css">
        .style1
        {
            display: none;
        }
</style>
usage
-------------
<asp:BoundField DataField="rowid" HeaderText="SNO" >
     <HeaderStyle CssClass="style1" />
     <ItemStyle CssClass="style1" />
 </asp:BoundField>

List box style in asp.net

.ListBox
{
    border-color: #476AB4;
    border-top-style: solid;
    border-right-style: solid;
    border-left-style: solid;
    border-bottom-style: solid;
    border-width: 1px;
    font-family: Tahoma, Arial, Helvetica, sans-serif;
    font-size: 11px;
}

how to bind a list box in asp.net

to bind a listbox in asp.net

write the following code

lbxLeft.DataSource = SqlDS.Tables(0)
lbxLeft.DataTextField = "Id"
lbxLeft.DataValueField = "Id"
lbxLeft.DataBind()

how to move items from left to right and right to left list box

While moving the items from left listbox to right listbox

For Each item As ListItem In lbxLeft.Items
                If item.Selected And Not lbxRight.Items.Contains(item) Then
                    Dim newItem As New ListItem(item.Text, item.Value)
                    lbxRight.Items.Add(newItem)
                    itemsToRemove.Add(item)
                End If
            Next
            For Each item As ListItem In itemsToRemove
                lbxLeft.Items.Remove(item)
            Next


While moving items from right to left

 Dim itemsToRemove As New List(Of ListItem)
            For Each item As ListItem In lbxRight.Items
                If item.Selected And Not lbxLeft.Items.Contains(item) Then
                    Dim newItem As New ListItem(item.Text, item.Value)
                    lbxLeft.Items.Add(newItem)
                    itemsToRemove.Add(item)
                End If
            Next
            For Each item As ListItem In itemsToRemove
                lbxRight.Items.Remove(item)
            Next

On Submit click

For Each item As ListItem In lbxRight.Items
         'do something
Next

how to delay a command in sql server

Some times we need to delay a command before execution until the preferred time in sql

Query

To delay a command until 10 AM

WAITFOR TIME '10:00:00'
SELECT GETDATE()

how to use waitfor command in sql

Some times we need to halt a command before executing it.

To achieve this, use the following query

Query

WAITFOR DELAY '000:00:10'
SELECT GETDATE()

this command waits for 10 seconds before executing

how to refresh SSMS Intellisense cache

To refresh the sql server management studio when you have done some changes to a schema

Solution

press CTRL + SHIFT + R to clear the cache of the Intellisense and it removes the underline from the word when you made some changes to schema

Excel bulk copy in asp.net with appending a column to excel data

export excel sheet data using bulk copy method in asp.net and at the same time appending a column to excel data

VB.Net code

 Public Sub ExcelToDbBulkCopy()
        Try
            Dim ExcelConn As OleDbConnection
            Dim ExcelCmd As OleDbCommand
            Dim Excelda As OleDbDataAdapter
            Dim Excelds As DataSet
            Dim Excelquery As String
            Dim ExcelconnString As String = String.Empty
            Dim strConString As String = String.Empty
            Dim strFileName As String = DateTime.Now.ToString("ddMMyyyy-HHmmss.fff")
            Dim strFileType As String = System.IO.Path.GetExtension(FileUpl.FileName).ToString().ToLower()
            If FileUpl.HasFile Then
                If strFileType.Trim = ".xlsx" Then
                    FileUpl.SaveAs(Server.MapPath("Data/" & strFileName & strFileType))
                Else
                    WebMessage("Only excel files allowed with '.xlsx'")
                    Exit Sub
                End If
            Else
                WebMessage("Please select excel file to upload")
                Exit Sub
            End If
            Dim strNewPath As String = Server.MapPath("Data/" & strFileName & strFileType)
            ExcelconnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strNewPath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
            ExcelConn = New OleDbConnection(ExcelconnString)
            If ExcelConn.State = ConnectionState.Closed Then ExcelConn.Open()
            Dim dt As DataTable = Nothing
            Dim SheetName As String = String.Empty
            dt = ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            For Each row As DataRow In dt.Rows
                SheetName = row("TABLE_NAME").ToString
                Exit For
            Next
            Excelquery = "SELECT * FROM [" & SheetName & "]"
            ExcelCmd = New OleDbCommand(Excelquery, ExcelConn)
            Excelda = New OleDbDataAdapter(ExcelCmd)
            Excelds = New DataSet()
            Excelda.Fill(Excelds)
            'append column to excel datatble
            Excelds.Tables(0).Columns.Add("CreatedBy")
            Dim rownum As Integer = 0
            For Each row As DataRow In Excelds.Tables(0).Rows
                Dim dtView As DataView = Excelds.Tables(0).DefaultView
                dtView.Item(rownum).Row.Item("CreatedBy") = "SYSTEM"
                Excelds.Tables(0).AcceptChanges()
                rownum += 1
            Next
            'Bulk copy function
            strConString = System.Configuration.ConfigurationManager.ConnectionStrings("ConMaturity").ConnectionString
            Dim bcp As SqlBulkCopy = New SqlBulkCopy(strConString)
            bcp.DestinationTableName = "dbo.TblMaturityListing"
            'mapping required if source and destination column names are different
            bcp.ColumnMappings.Add("CERTIFICATE SEQN NO", "CertSeqNo")
            bcp.ColumnMappings.Add("CERTIFICATENO", "CertNo")
            bcp.ColumnMappings.Add("PRODUCTCODE", "PrdCode")
            bcp.ColumnMappings.Add("PRODUCTNAME", "PrdName")
            bcp.ColumnMappings.Add("CreatedBy", "CreatedBy")
            bcp.WriteToServer(Excelds.Tables(0))
            Excelda.Dispose()
            ExcelConn.Close()
            ExcelConn.Dispose()
            System.IO.File.Delete(strNewPath)
            WebMessage("Data Uploaded Successfully.")
        Catch ex As Exception
            WebMessage(ex.ToString)
        End Try
    End Sub

asp.net - how to export gridview values to excel in asp.net

to export a gridview to excel using asp.net,
following code will helpout

Protected Sub ExportToExcel()
        Try
            Dim stringWrite As StringWriter
            Dim htmlWrite As HtmlTextWriter
            Dim frm As HtmlForm = New HtmlForm()
            Dim style As String = "<style>td{ mso-number-format: \@; }</style>"
            Response.ClearHeaders()
            Response.AppendHeader("content-disposition", "attachment;filename=TEST.xls")
            Response.Charset = String.Empty '""
            Response.ContentType = "application/vnd.ms-xlsx"
            stringWrite = New StringWriter
            htmlWrite = New HtmlTextWriter(stringWrite)
            Controls.Add(frm)
            frm.Controls.Add(gvReport)
            frm.RenderControl(htmlWrite)
            Response.Write(style)
            Response.Write(stringWrite.ToString())
            Response.End()
        Catch ex As Exception
            WebMessage(ex.ToString)
        End Try
    End Sub

asp.net / vb.net - how to get current page name in asp.net

to get current page name in asp.net

Path.GetFileName(Request.PhysicalPath)

asp.net - Vb.Net - how to get host name in asp.net

to get the host name, use the following line of code

System.Net.Dns.GetHostName()

asp.net - Vb.Net - how to get IP address using asp.net

to get the client pc IP, write the following code

System.Net.Dns.GetHostAddresses(System.Net.Dns.GetHostName()).GetValue(1).ToString()

asp.net / vb.net - To get the domain name and user name from windows logon in asp.net

To get the domain name and user name from windows logon in asp.net

Dim arrName() As String = Page.User.Identity.Name.Split("\")
txtUsername.Text = arrName(1)
txtDomain.Text = Page.User.Identity.Name

Asp.Net/Vb.Net - gridview paging in asp.net

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
        BindData()
        gv.PageIndex = e.NewPageIndex
        gv.DataBind()
    End Sub

asp.Net / vb.Net - how to get file list in a folder in asp.net

to get the file list in a folder using asp.net,
use the following code block

Dim dir As New DirectoryInfo("D:\foldername")
Dim dirinfo As FileInfo() = dir.GetFiles("*.pdf")
Dim FileName As FileInfo
Dim path As String
If dir.Exists = True Then
 For Each FileName In dirinfo
  path = dir.FullName & "\" & FileName.ToString
   'do something
 Next
End If

Asp.net / vb.net - how to open a file in asp.net

To open a file in asp.net

Dim filename As String = "D:\\foldername\\testbinary.pdf"
Response.AddHeader("Content-Disposition", "attachment; filename=" & Path.GetFileName(filename))
Response.WriteFile(filename)
Response.Flush()
Response.Close()

Javascript - how to select one checkbox from a checkbox list in asp.net using javascript

To select one checkbox from a checkbox list

function SelectOne(event, cname) {
            if (!event)
                event = window.event;
            var listItem = event.target || event.srcElement;
            var cbl = document.getElementById(cname);
            var listItems = cbl.getElementsByTagName('input');
            for (var i = 0; i < listItems.length; i++) {
                if (listItems[i] != listItem) {
                    listItems[i].checked = false;
                }
            }
        }

JavaScript - disable ie back button using java script in asp.net

<script type="text/javascript">
        function noBack() {
            window.history.forward();
        }
        noBack();
        window.onload = noBack;
        window.onpageshow = function (evt) {
            if (evt.persisted) noBack();
        }
        window.onunload = function () {
            void (0);
        }
    </script>

JavaScript - validating a text box using javascript in asp.net with master page

function CheckEmptyText() {
            if (document.getElementById("<%=txtUserid.ClientID %>").value == "") {
                alert("Please Key-In User Id.");
                document.getElementById("<%=txtUserid.ClientID %>").focus();
                return false;
            }
            else {
                return true;
            }
        }

Java script - javascript alert and return url in asp.net

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

Java Script - javascript alert box in asp.net

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