Delete Duplicate records in sql server

WITH CTE (SecondCol,ThirdCol, DuplicateCount)
AS
(
SELECT SecondCol,ThirdCol,
ROW_NUMBER()
OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)
AS DuplicateCount
FROM testtable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

how to remove the LOGFILE and perform some operations from cmd prompt

SELECT name,recovery_model_desc FROM sys.databases
and check whether the database is 'FULL' mode or 'SIMPLE'..if it s FULL we have to remove the LOGFILE of database.
go to database ...> detach.. next goto logfile and rename it..of that database
again we have to attach the only .mdf file (instead of .mdf and .ldf files to database)
=====================
sqlcmd -?
we can change the sqlserver sa password from cmd prompt very easily
EXEC SP_PASSWORD NULL,'venki','sa'
GO

Bind the Help To Project and Binding datasource to report viewver

Dim hlppath As String = Application.StartupPath & "\Ayuda\Espiga.chm"
System.Windows.Forms.Help.ShowHelp(Me, hlppath)


Private Sub InitReports(ByVal ReportName As String, ByVal DS As DataSet, ByVal ParameterName() As String, ByVal ParameterValue() As String)
Try
Dim ParamList As New Generic.List(Of Microsoft.Reporting.WinForms.ReportParameter)
Dim reportDatasource As New Microsoft.Reporting.WinForms.ReportDataSource
ReportViewer1.Clear()
ReportViewer1.Reset()
'Add Tables to LocalReport's DataSource
For Each table As DataTable In DS.Tables
Me.ReportViewer1.LocalReport.DataSources.Clear()
Me.ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource(DS.DataSetName & "_" & table.TableName, table))
Next
'Load parameters
For i As Integer = 0 To ParameterName.Length - 1
ParamList.Add(New Microsoft.Reporting.WinForms.ReportParameter(ParameterName(i).Trim, ParameterValue(i).Trim))
Next i
'Embed the LocalReport to the ReportViewer
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = Me.GetType().Namespace & "." & ReportName
'Set the parameters to the LocalReport
Me.ReportViewer1.LocalReport.SetParameters(ParamList)
Me.ReportViewer1.RefreshReport()
Catch ex As Exception
End Try
End Sub

Export the Grid data to Excel

Public Sub Export2Excel(ByVal grdView As DataGridView, ByVal fileName As String)
Dim myFile As String = fileName
Dim subtractBy As Integer, cellText As String
Dim fs As IO.StreamWriter = Nothing
Try
' Open the file and write the headers
fs = New IO.StreamWriter(myFile, False)
fs.WriteLine("")
fs.WriteLine("")
fs.WriteLine("")
' Create the styles for the worksheet
fs.WriteLine(" ")
' Style for the column headers
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" "ss:WrapText=""1""/>")
fs.WriteLine(" ")
fs.WriteLine(" ")
' Style for the column information
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" ")
' Write the worksheet contents
fs.WriteLine("")
fs.WriteLine(" ")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" ", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine(" ")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" " & _
"{0}", _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine(" ")
' Check for an empty row at the end due to Adding allowed on the DataGridView
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format(" ", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = ""
If grdView.Item(intCol, i).Value IsNot DBNull.Value Then cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" " & _
"{0}", _
cellText.ToString))
Next
fs.WriteLine(" ")
Next
' Close up the document
fs.WriteLine(" ")
fs.WriteLine("")
fs.WriteLine("")
Catch ex As Exception
Throw ex
Finally
If fs IsNot Nothing Then fs.Close()
End Try
End Sub

Date Time patters converting in datagridview

Private Sub UcdgvAuxiliary_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles UcdgvAuxiliary.DataError
Try
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).ValueType.Name = "DateTime" Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim myDT As New System.Globalization.DateTimeFormatInfo
myDT.ShortDatePattern = "ddMMyyyy"
Dim dt As DateTime = Date.Parse(UcdgvAuxiliary.EditingControl.Text, myDT)
UcdgvAuxiliary.EditingControl.Text = dt
UcdgvAuxiliary.EndEdit()
End If
End If
End If
Catch ex As Exception

Date Time patters converting in datagridview

Private Sub UcdgvAuxiliary_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles UcdgvAuxiliary.DataError
Try
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).ValueType.Name = "DateTime" Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim myDT As New System.Globalization.DateTimeFormatInfo
myDT.ShortDatePattern = "ddMMyyyy"
Dim dt As DateTime = Date.Parse(UcdgvAuxiliary.EditingControl.Text, myDT)
UcdgvAuxiliary.EditingControl.Text = dt
UcdgvAuxiliary.EndEdit()
End If
End If
End If
Catch ex As Exception

Binding Source- and Binding navigator

Public Sub New(ByVal NotesSrc As DataSet)
' This call is required by the Windows Form Designer.
InitializeComponent()

Try
dsNotesSrc = NotesSrc
BS = New BindingSource(dsNotesSrc, dsNotesSrc.Tables(0).TableName)
txtNote.DataBindings.Add("Text", BS, dsNotesSrc.Tables(0).Columns("NoteText").ColumnName)

DefaultView = dsNotesSrc.Tables(0).DefaultView

Catch ex As Exception
ex.Message.ToString()
End Try
End Sub

Binding Navigator

Private Sub FrmViewNotes_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

lblfrm.Text = "Administrador del sistema"
dsUsers = conFacade.GetUsers()
lblDte.Text = DateTime.Now.ToString()
Me.MdiParent = FrmMDI
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(DefaultView).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub

Private Sub BindingNavigatorMoveNextItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveNextItem.Click
BS.MoveNext()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub

Private Sub BindingNavigatorMoveLastItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveLastItem.Click
BS.MoveLast()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub

Private Sub BindingNavigatorMoveFirstItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveFirstItem.Click
BS.MoveFirst()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub

Private Sub BindingNavigatorMovePreviousItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMovePreviousItem.Click
BS.MovePrevious()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub

Private Sub ShowUserTo()
If txtNote.Text <> "" Then
Dim drNotesSrc() As DataRow = dsNotesSrc.Tables(0).Select("NoteText='" + txtNote.Text + "'")
Dim drUsers() As DataRow = dsUsers.Tables(0).Select("UserId='" + drNotesSrc(0).Item("UserTo").ToString() + "'")
lblTo3.Text = drUsers(0).Item("Name").ToString
Else
lblTo3.Text = String.Empty
End If

End Sub

Private Sub btnDeleteNote_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteNote.Click

Dim drNotesSrc() As DataRow = dsNotesSrc.Tables(0).Select("NoteText='" + txtNote.Text + "'")
ParentClass.DeleteNote(drNotesSrc(0).Item("Note").ToString())
dsNotesSrc = BS.DataSource
dsNotesSrc.Tables(0).Rows.RemoveAt(txtNote.BindingContext(BS).Position)
txtNote.DataBindings.Clear()
txtNote.DataBindings.Add("Text", BS, dsNotesSrc.Tables(0).Columns("NoteText").ColumnName)
' BindingNavigatorMovePreviousItem_Click(sender, e)
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count


If dsNotesSrc.Tables(0).Rows.Count = 0 Then
ParentClass.MyParent.ViewNoteItemEnabled = False
ParentClass.ImgNotes.Visible = False
Me.Close()

End If--
End Sub

Private Sub btnDeleteall_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteall.Click
If dsNotesSrc.Tables(0).Rows.Count <> 0 Then

For i As Integer = 1 To dsNotesSrc.Tables(0).Rows.Count
btnDeleteNote_Click(sender, e)
Next

End If
End Sub

Standard code structure DataAcces, Common and Business Rules

http://www.zamzar.com/getFiles.php?uid=7063163453490001244193193&targetID=diNtAEGEb41M5oVCnYaB8ExLbnNTDWh6

Standard code for Getting Data- VB.Net- Raides

Public Function GetData(Optional ByVal Filter As String = "") As DataSet
Try
Me.OpenConnection()
Dim TableParts() As String = Nothing
If Regex.IsMatch(Table, "\w+(?:\.\w+)+", RegexOptions.Singleline) Then
TableParts = Table.Split(".")
Else
ReDim TableParts(0)
TableParts(0) = Table
End If
Dim sqlstr As String = "SELECT * FROM "
For i As Integer = 0 To TableParts.Length - 1
sqlstr &= "[" & TableParts(i) & "]."
Next
sqlstr = sqlstr.TrimEnd(".") & IIf(Filter <> "", " WHERE " & Filter, "")
Dim ds As New DataSet
Dim cmd As New SqlCommand(sqlstr, Me.connection)
Dim Da As New SqlDataAdapter(cmd)
Da.Fill(ds, Table)
Return ds
Catch ex As Exception
'Throw New Exception(ex.Message)
Return Nothing
Finally
Me.CloseConnection()
End Try
End Function