Deleting duplicate rows

A very cool technique to find and delete duplicate rows and just keep one unique row uinsg CTE ( common table expression) in SQL 2008

Initially the table had data

updateId  ptno
1                95689
2                95689
3                95689
4               91458
5               91000

Result desired
1               95689
4               91458
5               91000

(3 unique rows)

SELECT UpdateID,Ptno INTO #TempPtno From  dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;

/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1



**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure  procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Accessing control in a listview from code behind

In this case we are accessing the hyperlink control from the code behind to format the NavigateURL property of the control

 Protected Sub lvTACRlist_ItemDataBound(ByVal sender As Object, ByVal e As  System.Web.UI.WebControls.ListViewItemEventArgs) Handles lvTACRlist.ItemDataBound

        Dim h1 As HyperLink = CType(e.Item.FindControl("hyperLink1"), HyperLink)
        h1.NavigateUrl = h1.NavigateUrl + "&H=" + ddhoslist.SelectedValue.ToString()

 End Sub

Accessing control in a gridview from code behind

Protected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender

For Each gvRow In GridView1.Rows
 If gvRow.RowType = DataControlRowType.DataRow Then
   Dim h1 As HyperLink = CType(gvRow.Cells(0).Controls(0), HyperLink)
   h1.NavigateUrl = h1.NavigateUrl & "&H=" & Request.QueryString("H")
 End If
Next

End Sub

Accessing masterpage objects from child page

I had a project where I had to pass in the hospital code to the link button that was at the top of the each page . The top section of the each page was a masterpage.

I added a property called SetHospitalCode on the masterpage as follows

Public m_HosCode As String
    Public Property SetHospitalCode() As String
        Get
            Return m_HosCode
        End Get

        Set(ByVal value As String)
            m_HosCode = value
        End Set

    End Property

--------
Protected Sub DashBoard_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DashBoard.Click
      
   If m_HosCode Is Nothing Then
            Response.Redirect("DashBoard.aspx")
    Else
           Response.Redirect("DashBoard.aspx?H=" + m_HosCode.ToString())
   End If
End Sub

I set the property from the each child page as follows
Dim getMaster As MasterPage
 getMaster.SetHospitalCode =  hospitalcode 

where hospitalcode is the new value of the hospital from each page.
DashBoard is the ID of the linkbutton