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)