SSRS Report Serial No

Normal
=RowNumber(Nothing)

Group wise
=Runningvalue(Fields!GroupColumn1Name.Value,countdistinct,"MatrixName")

View

conn = New SqlConnection(ConnectionStr)
conn.Open()

        If CboDesc.Text <> "" Then
            DescId = CInt(Mid(CboDesc.Text, (InStrRev(CboDesc.Text, "|") + 1), Len(CboDesc.Text)))
        Else
            DescId = 0
        End If

        sql = " Select a.Type,a.Description,b.DescId,b.Rate,b.EffTo,b.EffFrom from DescriptionMaster a " _
            & " Inner join (Select DescId,Rate,EffFrom,EffTo,LocationId," _
            & " ROW_NUMBER() OVER (PARTITION BY DescId ORDER BY EffFrom DESC) rn " _
            & " from RateMaster where Locationid = " & LocationId & ") b On a.TranId = b.DescId And rn = 1 " _
            & " Where  b.LocationId = " & LocationId & " "
        '& " Group By a.Type,a.Description,b.DescId,b.Rate,b.EffTo,b.EffFrom "

        If CboType.Text <> "" Then
            sql = sql + "And a.Type = '" & CboType.Text & "'"
        End If

        If DescId <> 0 Then
            sql = sql + "and "
        End If

        If DescId <> 0 Then
            sql = sql + "b.DescId = " & DescId & ""
        End If

        sql = sql + "Group By a.Type,a.Description,b.DescId,b.Rate,b.EffTo,b.EffFrom "

        Cmd = New SqlCommand(sql, conn)
        DR = Cmd.ExecuteReader

        DGVDesc.Rows.Clear()
        If DR.HasRows Then
            While DR.Read
                DGVDesc.Rows.Add()
                n = DGVDesc.RowCount - 1
                DGVDesc.Rows.Item(n).Cells("Type").Value = DR.Item("Type")
                DGVDesc.Rows.Item(n).Cells("Description").Value = DR.Item("Description") & "|" & DR.Item("DescId")
                DGVDesc.Rows.Item(n).Cells("Rate").Value = DR.Item("Rate")
                DGVDesc.Rows.Item(n).Cells("EffFrom").Value = DR.Item("EffFrom")
                DGVDesc.Rows.Item(n).Cells("Effto").Value = DR.Item("EffTo")
                End While
        Else
            MsgBox("No Records Found. Please Check...")
            End If
        DR.Close()

Text Box - Numeric Value

Private Sub TbCastingWt_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TbCastingWt.KeyPress
        If Asc(e.KeyChar) <> 13 And Asc(e.KeyChar) <> 8 And Asc(e.KeyChar) <> 46 And Not IsNumeric(e.KeyChar) Then
            e.Handled = True
        End If
    End Sub

Mail() with Attachment

 Public Sub Mail()
        Dim Sql1 As String
        Dim Conn1 As SqlConnection
        Dim Cmd1 As SqlCommand
        Dim DR1 As SqlDataReader

        Conn1 = New SqlConnection(ConnectionStr)
        Conn1.Open()

        Sql1 = "Select * from FeasChecklistMaster a   " _
        & " Inner Join FeasChecklistDetail b on a.tranMasId = b.TranMasId  and b.approved = 'A' and b.FeasReviewapp = 'A' " _
        & " Where EnquiryNo = '" & CboEnquiryNoApp.Text & "'  " _
        & " and a.Locationid = " & LocationId & " "

        Cmd1 = New SqlCommand(Sql1, Conn1)
        DR1 = Cmd1.ExecuteReader

        If DR1.HasRows Then
            While DR1.Read

                Using Conn As SqlConnection = New SqlConnection(ConnectionStr)

                    Conn.Open()

                    Dim transaction As SqlTransaction = Conn.BeginTransaction(IsolationLevel.ReadCommitted)

                    Dim command As SqlCommand = Conn.CreateCommand()

                    command.Transaction = transaction

                    Try
                        Dim FromAddress As String = Nothing
                        Dim ToAddress As String = Nothing
                        Dim CCAddress As String = Nothing
                        Dim BCCAddress As String = Nothing
                        Dim PartyToAddress As String = Nothing
                        Dim PartyCCAddress As String = Nothing

                        '  SelEnqNo = DR.Item("EnquiryNo")

                        EnquiryNo = DR1.Item("EnquiryNo")
                        ProdName = DR1.Item("ComponentName")
                        CustomerType = DR1.Item("CustomerType")

                        FrmEnggEstimationCostingReport.Sp_EnggEstimationMasTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationMas, LocationId, EnquiryNo, ProdName, CustomerType)
                        FrmEnggEstimationCostingReport.Sp_CompanyDetailTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_CompanyDetail, LocationId)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationCoreBoxTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationCoreBox, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationCoreTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationCore, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationCoreAssemplyTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationCoreAssemply, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationSleeveTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationSleeve, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationDevelopmentCostTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationDevelopmentCost, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationDevelopmentTimeTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationDevelopmentTime, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationTestingTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationTesting, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_EnggEstimationRemarksTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_EnggEstimationRemarks, LocationId, EnquiryNo, ProdName)
                        FrmEnggEstimationCostingReport.Sp_GetUnitTableAdapter.Fill(FrmEnggEstimationCostingReport.DS_Main.Sp_GetUnit)
                        FrmEnggEstimationCostingReport.ReportViewer1.RefreshReport()

                        Dim mimeType As String = String.Empty
                        Dim encoding As String = String.Empty
                        Dim extension As String = "xls"

                        Dim bytes As Byte() = FrmEnggEstimationCostingReport.ReportViewer1.LocalReport.Render("Excel", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

                        Dim oFileStream As System.IO.FileStream
                        Dim strFilePath As String = String.Empty
                        strFilePath = "C:\Temp\EnggEstimationCostingReport.xls"

                        oFileStream = New System.IO.FileStream(strFilePath, System.IO.FileMode.Create)
                        oFileStream.Write(bytes, 0, bytes.Length)
                        oFileStream.Close()

                        MailSend = 0

                        Dim Subject As String
                        Dim BodyText As String

                        Subject = "Engineering Estimation Costing Report For Your Enquiry No.:" + DR1.Item("EnquiryNo") + ""

                        BodyText = "Dear Sir/Madam, " + vbCrLf + vbCrLf _
                                 + "For your reference with this mail, we have attached the Engineering Estimation Costing Report For Your Enquiry No.:" + DR1.Item("EnquiryNo") + ". " + vbCrLf + vbCrLf _
                                 + "Please check and revert back us if any." + vbCrLf + vbCrLf _
                                 + "Note:  ***** This is a auto generated mail. Please reply to concerned person(s) ****" + vbCrLf + vbCrLf

                            FromAddress = "pppp@kkkk.com"
                            Dim SendFrom As MailAddress = New MailAddress(FromAddress)
                            ToAddress = " 1@kkkk.com,2@kkkk.com "
                            Dim SendTo As MailAddress = New MailAddress(ToAddress)
                       

                            CCAddress = "cc@kkkk.com"
                            Dim SendCC As MailAddress = New MailAddress(CCAddress)
                     

                        Dim mailInstance As MailMessage

                        Dim mailSenderInstance As SmtpClient = New SmtpClient("192.168.100.7", 25) '25 is the port of the SMTP host

                        mailSenderInstance.Credentials = New System.Net.NetworkCredential("systemadmin", "systemadmin")

                        mailInstance = New MailMessage(FromAddress, ToAddress, Subject, BodyText)

                        If CCAddress <> "" Then
                            mailInstance.CC.Add(CCAddress)
                        End If

                        Dim attachment As System.Net.Mail.Attachment
                        attachment = New System.Net.Mail.Attachment(strFilePath)
                        mailInstance.Attachments.Add(attachment)

                        mailInstance.Priority = MailPriority.High

                        mailSenderInstance.Send(mailInstance)

                        mailInstance.Attachments.Dispose()
                        mailInstance.Dispose() 'Please remember to dispose this object

                        MailSend = 1

                        '--------------------------------------------------------------------

                        If MailSend = 1 Then
                            transaction.Commit()
                            MsgBox("Mail Has Been Send Successfully For EnquiryNo No : " + DR1.Item("EnquiryNo") + "")
                            Me.Enabled = True
                        Else
                            transaction.Rollback()
                            DR1.Close()
                            Conn1.Close()
                            MsgBox(Err.Description)
                            MsgBox("Error while processing. Previous execution was rollback")
                            Me.Enabled = True
                            Exit Sub
                        End If

                    Catch ex As Exception
                        transaction.Rollback()
                        DR1.Close()
                        Conn1.Close()
                        MsgBox(Err.Description)
                        MsgBox("Error while processing. Previous execution was rollback")
                        Exit Sub
                    End Try

                End Using
            End While
        End If
        DR1.Close()
    End Sub

MSCOMCT2.OCX

Save

Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click

        Using Conn As SqlConnection = New SqlConnection(ConnectionStr)

            Conn.Open()

            Dim transaction As SqlTransaction = Conn.BeginTransaction(IsolationLevel.ReadCommitted)
            Dim command As SqlCommand = Conn.CreateCommand()
            command.Transaction = transaction

            If CboDepartment.Text = "" Then
                MsgBox("Field Must Be Select - Department")
                CboDepartment.Focus()
                Exit Sub
            End If

            If CboRawmaterial.Text = "" Then
                MsgBox("Field Must Be Select - Rawmaterial ")
                CboRawmaterial.Focus()
                Exit Sub
            End If

            If CboCategory.Text = "" Then
                MsgBox("Field Must Be Select - Category ")
                CboCategory.Focus()
                Exit Sub
            End If

            If CboSubCategory.Text = "" Then
                MsgBox("Field Must Be Select - SubCategory ")
                CboSubCategory.Focus()
                Exit Sub
            End If

            DeptId = CInt(Mid(CboDepartment.Text, (InStrRev(CboDepartment.Text, "|") + 1), Len(CboDepartment.Text)))
            MatlId = CInt(Mid(CboRawmaterial.Text, (InStrRev(CboRawmaterial.Text, "|") + 1), Len(CboRawmaterial.Text)))
            CatId = CInt(Mid(CboCategory.Text, (InStrRev(CboCategory.Text, "|") + 1), Len(CboCategory.Text)))
            SubCatId = CInt(Mid(CboSubCategory.Text, (InStrRev(CboSubCategory.Text, "|") + 1), Len(CboSubCategory.Text)))

            Try
                command.CommandText = "Insert into RawmaterialMappingMaster (TranId,LocationId,DeptId,MatlId,CatId,SubCatId,CreatedBy,CreatedDate,CreatedSystem) " _
                & " values  ('" & DeptId & "','" & MatlId & "','" & CatId & "','" & SubCatId & "','" & UserId & "', '" & Now & "', '" & UserComp & "')"
                command.ExecuteNonQuery()

                DGRmMappingMaster.Rows.Add()
                n = DGRmMappingMaster.RowCount - 1

                DGRmMappingMaster.Rows.Item(n).Cells("Department").Value = CboDepartment.Text
                DGRmMappingMaster.Rows.Item(n).Cells("Rawmaterial").Value = CboRawmaterial.Text
                DGRmMappingMaster.Rows.Item(n).Cells("Category").Value = CboCategory.Text
                DGRmMappingMaster.Rows.Item(n).Cells("SubCategory").Value = CboSubCategory.Text

                MsgBox("Saved Successfuly")
                transaction.Commit()

            Catch ex As Exception
                transaction.Rollback()
                MsgBox(Err.Description)
                MsgBox("Error while processing. Previous execution was rollback")
            End Try
        End Using
        Exit Sub

    End Sub

Update or Remove all special charactor

UPDATE VehicleTripEntry
SET Vehicleno =
(
CASE WHEN Vehicleno LIKE '%[^a-zA-Z0-9]%' THEN Replace(REPLACE( Vehicleno, SUBSTRING( Vehicleno, PATINDEX('%[~,@,#,$,%,&,*,^,&,%,*,(,), ,`]%', Vehicleno), 1 ),''),'-','')
ELSE Vehicleno
END                    
)
where VehicleType = 'H'

Simple Trigger

--Create Table
Create table TestTriger
(
Tranid int ,
Locationid int
)

--Create Trigger
Create TRIGGER [dbo].TestTriger_LocationChk
ON  [dbo].TestTriger
AFTER Insert
AS

DECLARE @LocId Numeric(15,6)

select @LocId = Locationid from Inserted


if @LocId = 0
BEGIN
    RAISERROR('LocCheck Zero Inserting. Please Contact Administrator...', 16, 1);
    ROLLBACK TRANSACTION
END

--Insert query to check trigger
Insert into TestTriger values(1,0)

SMS

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim TextMessage As String = ""
        Dim Mno As String = ""

        TextMessage = "Test Message"
        Mno = "99658xxxxx"

        url = "http://transaction.lionsms.com/sendsms.jsp?user=sxxxxs&password=18xxxx&mobiles=" & Mno & "&sms=" & TextMessage & ""

        Dim objResponse As System.Net.WebResponse = System.Net.WebRequest.Create(url).GetResponse
        Dim stream As System.IO.Stream = objResponse.GetResponseStream()
        Dim reader As New System.IO.StreamReader(stream)

    End Sub

Email

 Public Sub mail()
        Dim FromAddress As String = Nothing
        Dim ToAddress As String = Nothing
        Dim CCAddress As String = Nothing
        Dim BCCAddress As String = Nothing
        Dim PartyToAddress As String = Nothing
        Dim PartyCCAddress As String = Nothing

        'MailSend = 0

        Dim Subject As String = ""
        Dim BodyText As String

        Subject = "Test Mail"

        BodyText = ""
        BodyText = " Dear Sir,  " + vbCrLf + vbCrLf _
        + " Test" + vbCrLf + vbCrLf

        FromAddress = "pppp@kkkk.com"
        Dim SendFrom As MailAddress = New MailAddress(FromAddress)
        ToAddress = "pppp@kkkk.com"
        Dim SendTo As MailAddress = New MailAddress(ToAddress)

        Dim mailInstance As MailMessage

        Dim mailSenderInstance As SmtpClient = New SmtpClient("192.168.100.7", 25) '25 is the port of the SMTP host

        mailSenderInstance.Credentials = New System.Net.NetworkCredential("hh", "hh")

        mailInstance = New MailMessage(FromAddress, ToAddress, Subject, BodyText)
        ' mailInstance.CC.Add(CCAddress)

        mailInstance.Priority = MailPriority.High

        mailSenderInstance.Send(mailInstance)

        mailInstance.Attachments.Dispose()
        mailInstance.Dispose() 'Please remember to dispose this object

        MsgBox("Mail Has Been Send Successfully ")
    End Sub

Update Query

Update TimePlanMacroPDDet Set UptoProdQty = t.UptoProdQty From

(Select LocationId,Yrmon,Matlid,PdId,ProcessId,Sum(ProdQty) UptoProdQty

From TimePlanMacroProdDet Where LocationId = @LocationId And Yrmon = @Yrmon

And MatlId = @MatlId And PdId = @PdId And ProdnDate between @FrDt And @ToDt

Group By LocationId,Yrmon,Matlid,PdId,ProcessId

) t

Where TimePlanMacroPDDet.LocationId = t.LocationId

And TimePlanMacroPDDet.Yrmon = t.Yrmon

And TimePlanMacroPDDet.MatlId = t.MatlId

And TimePlanMacroPDDet.PdId = t.PdId

And TimePlanMacroPDDet.processid = t.processid

And Type = 'Process'

Update from Select Query

Create Table TestUpdate
(
TranId int identity (1,1),
Val1 int,
Val2 Int,
Val3 Int
)


Insert into TestUpdate  values(1,2,1)



Update TestUpdate set Val3 = 4 from TestUpdate where val2 = 2