mrp_prg.vb

connect db by vb.net

            Dim myCommand As SqlCommand = myConnection.CreateCommand()
            Dim myTrans As SqlTransaction

            Dim sStr As String = _
            "Update s_LotDetail " & _
            "Set LotNo=@LotNo,Station=@Station,KeyBy=@KeyBy,Qty=@Qty," & _
            "Type=@Type,Status=@Status,Reason=@Reason,PostTime=@PostTime,PostDate=@PostDate," & _
            "BatchNo=@BatchNo,BatchID=@BatchID,id_upd=@id_upd,dt_upd=@dt_upd " & _
            "where ROWID = @ROWID "

            myCommand.Parameters.Add("@LotNo", System.Data.SqlDbType.Char).Value = IIf(Trim(pLotNo) = "", " ", Trim(pLotNo))
            myCommand.Parameters.Add("@Station", System.Data.SqlDbType.Char).Value = IIf(Trim(pStation) = "", " ", Trim(pStation))
            myCommand.Parameters.Add("@KeyBy", System.Data.SqlDbType.Char).Value = IIf(Trim(pKeyBy) = "", " ", Trim(pKeyBy))
            myCommand.Parameters.Add("@Qty", System.Data.SqlDbType.Decimal).Value = pQty
            myCommand.Parameters.Add("@Type", System.Data.SqlDbType.Char).Value = IIf(Trim(pType) = "", " ", Trim(pType))
            myCommand.Parameters.Add("@Status", System.Data.SqlDbType.Char).Value = IIf(Trim(pStatus) = "", " ", Trim(pStatus))
            myCommand.Parameters.Add("@Reason", System.Data.SqlDbType.Char).Value = IIf(Trim(pReason) = "", " ", Trim(pReason))
            myCommand.Parameters.Add("@PostTime", System.Data.SqlDbType.Char).Value = IIf(Trim(pTime) = "", " ", Trim(pTime))

            If IsDate(pPostDate) Then
                myCommand.Parameters.Add("@PostDate", System.Data.SqlDbType.DateTime).Value = _
                CDate(pPostDate).ToShortDateString
            Else
                myCommand.Parameters.Add("@PODate", System.Data.SqlDbType.Char).Value = _
                pPostDate
            End If

            myCommand.Parameters.Add("@BatchNo", System.Data.SqlDbType.Int).Value = pBatchNo
            myCommand.Parameters.Add("@BatchID", System.Data.SqlDbType.Int).Value = pBatchID

            myCommand.Parameters.Add("@id_Upd", System.Data.SqlDbType.Char).Value = pId_Upd
            myCommand.Parameters.Add("@dt_Upd", System.Data.SqlDbType.DateTime).Value = Today.ToShortDateString
            myCommand.Parameters.Add("@ROWID", System.Data.SqlDbType.Int).Value = pROWID


            ' Start a local transaction
            myTrans = myConnection.BeginTransaction()
            ' Must assign both transaction object and connection
            ' to Command object for a pending local transaction
            myCommand.Connection = myConnection
            myCommand.Transaction = myTrans

            Try
                myCommand.CommandText = sStr
                myCommand.ExecuteNonQuery()
                myTrans.Commit()
                Console.WriteLine("Both records are update into database.")
            Catch e As SqlException
                Try
                    myTrans.Rollback()
                Catch ex As SqlException
                    If Not myTrans.Connection Is Nothing Then
                        MsgBox("An exception of type " & ex.GetType().ToString() & _
                                          " was encountered while attempting to roll back the transaction.")
                    End If
                End Try

                Dim errorMessages, evLog As String
                Dim i As Integer

                For i = 0 To e.Errors.Count - 1
                    errorMessages += "Index #" & i.ToString() & ControlChars.NewLine _
                                   & "Message: " & e.Errors(i).Message & ControlChars.NewLine

                    evLog += "Index #: " & i.ToString() & ControlChars.NewLine _
                            & "Message: " & e.Errors(i).Message & ControlChars.NewLine _
                            & "Entry  : " & pLotNo & ", " & "" & ", " & pId_Upd & ControlChars.NewLine _
                            & "Module : " & "updateItem" & ControlChars.NewLine _
                            & "User   : " & pId_Upd
                Next i
                MsgBox(errorMessages, MsgBoxStyle.Exclamation, "MRP SQL Adapter")

                Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog

                log.Source = "MRP"
                isError = True
            Finally
                myConnection.Close()
            End Try
        Else

            Dim myConnection As New OleDb.OleDbConnection(myErpSqlUserConn)
            myConnection.Open()

            Dim myCommand As OleDb.OleDbCommand = myConnection.CreateCommand()
            Dim myTrans As OleDb.OleDbTransaction

            Dim sStr As String = _
            "Update s_LotDetail " & _
            "Set LotNo=@LotNo,Station=@Station,KeyBy=@KeyBy,Qty=@Qty," & _
            "Type=@Type,Status=@Status,Reason=@Reason,PostTime=@PostTime,PostDate=@PostDate," & _
            "BatchNo=@BatchNo,id_upd=@id_upd,dt_upd=@dt_upd " & _
            "where ROWID = @ROWID "

            myCommand.Parameters.Add("@LotNo", System.Data.SqlDbType.Char).Value = IIf(Trim(pLotNo) = "", " ", Trim(pLotNo))
            myCommand.Parameters.Add("@Station", System.Data.SqlDbType.Char).Value = IIf(Trim(pStation) = "", " ", Trim(pStation))
            myCommand.Parameters.Add("@KeyBy", System.Data.SqlDbType.Char).Value = IIf(Trim(pKeyBy) = "", " ", Trim(pKeyBy))
            myCommand.Parameters.Add("@Qty", System.Data.SqlDbType.Decimal).Value = pQty
            myCommand.Parameters.Add("@Type", System.Data.SqlDbType.Char).Value = IIf(Trim(pType) = "", " ", Trim(pType))
            myCommand.Parameters.Add("@Status", System.Data.SqlDbType.Char).Value = IIf(Trim(pStatus) = "", " ", Trim(pStatus))
            myCommand.Parameters.Add("@Reason", System.Data.SqlDbType.Char).Value = IIf(Trim(pReason) = "", " ", Trim(pReason))
            myCommand.Parameters.Add("@PostTime", System.Data.SqlDbType.Char).Value = IIf(Trim(pTime) = "", " ", Trim(pTime))

            If IsDate(pPostDate) Then
                myCommand.Parameters.Add("@PostDate", System.Data.SqlDbType.DateTime).Value = _
                CDate(pPostDate).ToShortDateString
            Else
                myCommand.Parameters.Add("@PODate", System.Data.SqlDbType.Char).Value = _
                pPostDate
            End If

            myCommand.Parameters.Add("@BatchNo", System.Data.SqlDbType.Int).Value = pBatchNo
            myCommand.Parameters.Add("@id_Upd", System.Data.SqlDbType.Text).Value = pId_Upd
            myCommand.Parameters.Add("@dt_Upd", System.Data.SqlDbType.Text).Value = Today.ToShortDateString
            myCommand.Parameters.Add("@ROWID", System.Data.SqlDbType.Int).Value = pROWID


            ' Start a local transaction
            myTrans = myConnection.BeginTransaction()
            ' Must assign both transaction object and connection
            ' to Command object for a pending local transaction
            myCommand.Connection = myConnection
            myCommand.Transaction = myTrans

            Try
                myCommand.CommandText = sStr
                myCommand.ExecuteNonQuery()
                myTrans.Commit()
                Console.WriteLine("Both records are update into database.")
            Catch e As OleDb.OleDbException
                Try
                    myTrans.Rollback()
                Catch ex As OleDb.OleDbException
                    If Not myTrans.Connection Is Nothing Then
                        MsgBox("An exception of type " & ex.GetType().ToString() & _
                                          " was encountered while attempting to roll back the transaction.")
                    End If
                End Try

                Dim errorMessages, evLog As String
                Dim i As Integer

                For i = 0 To e.Errors.Count - 1
                    errorMessages += "Index #" & i.ToString() & ControlChars.NewLine _
                                   & "Message: " & e.Errors(i).Message & ControlChars.NewLine

                    evLog += "Index #: " & i.ToString() & ControlChars.NewLine _
                            & "Message: " & e.Errors(i).Message & ControlChars.NewLine _
                            & "Entry  : " & pLotNo & ", " & "" & ", " & pId_Upd & ControlChars.NewLine _
                            & "Module : " & "updateItem" & ControlChars.NewLine _
                            & "User   : " & pId_Upd
                Next i
                MsgBox(errorMessages, MsgBoxStyle.Exclamation, "MRP SQL Adapter")

                Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog

                log.Source = "MRP"
                isError = True
            Finally
                myConnection.Close()
            End Try

        End If
    End Sub 'RunSqlTransaction

    'Delete LotDetail By KSLIM 16-Oct-2004
    Public Sub DeleteLotDetail( _
    ByRef pROWID As Integer)
        isError = False
        If Trim(DataBaseType) = "SQL SERVER" Then
            Dim myConnection As New SqlConnection(myErpSqlUserConn)
            myConnection.Open()

            Dim myCommand As SqlCommand = myConnection.CreateCommand()
            Dim myTrans As SqlTransaction

            Dim sStr As String = _
            "Delete from s_LotDetail where ROWID = @ROWID "

            myCommand.Parameters.Add("@ROWID", System.Data.SqlDbType.Int).Value = pROWID


            ' Start a local transaction
            myTrans = myConnection.BeginTransaction()
            ' Must assign both transaction object and connection
            ' to Command object for a pending local transaction
            myCommand.Connection = myConnection
            myCommand.Transaction = myTrans

            Try
                myCommand.CommandText = sStr
                myCommand.ExecuteNonQuery()
                myTrans.Commit()
                Console.WriteLine("Both records are update into database.")
            Catch e As SqlException
                Try
                    myTrans.Rollback()
                Catch ex As SqlException
                    If Not myTrans.Connection Is Nothing Then
                        MsgBox("An exception of type " & ex.GetType().ToString() & _
                                          " was encountered while attempting to roll back the transaction.")
                    End If
                End Try
                Dim errorMessages, evLog As String
                Dim i As Integer
                isError = True
            Finally
                myConnection.Close()
            End Try
        Else
            Dim myConnection As New OleDb.OleDbConnection(myErpSqlUserConn)
            myConnection.Open()

            Dim myCommand As OleDb.OleDbCommand = myConnection.CreateCommand()
            Dim myTrans As OleDb.OleDbTransaction

            Dim sStr As String = _
            "Delete from s_LotDetail where ROWID = @ROWID "

            myCommand.Parameters.Add("@ROWID", System.Data.SqlDbType.Int).Value = pROWID


            ' Start a local transaction
            myTrans = myConnection.BeginTransaction()
            ' Must assign both transaction object and connection
            ' to Command object for a pending local transaction
            myCommand.Connection = myConnection
            myCommand.Transaction = myTrans

            Try
                myCommand.CommandText = sStr
                myCommand.ExecuteNonQuery()
                myTrans.Commit()
                Console.WriteLine("Both records are update into database.")
            Catch e As OleDb.OleDbException
                Try
                    myTrans.Rollback()
                Catch ex As OleDb.OleDbException
                    If Not myTrans.Connection Is Nothing Then
                        MsgBox("An exception of type " & ex.GetType().ToString() & _
                                          " was encountered while attempting to roll back the transaction.")
                    End If
                End Try
                Dim errorMessages, evLog As String
                Dim i As Integer
                isError = True
            Finally
                myConnection.Close()
            End Try

        End If

    End Sub 'RunSqlTransaction

    '==============================ENCRYPT & DECRYPT Function==================================================
    Private crpSample As SampleCrypto
    Private FormHasLoaded As Boolean = False
    Private strCurrentKeyFile As String
    Private strSourcePath As String
    Private strRijndaelSaltIVFile As String
    Private strTripleDESSaltIVFile As String
    Private strCrypto As String

    Public Sub ED_LOAD()
        ' Set the default crypto type.
        crpSample = New SampleCrypto("Rijndael")

        ' Set the path to save the key file to the Solution root folder by stripping 
        ' "bin" from the current directory.
        'Dim strCurrentDirectory As String = _
        '    Microsoft.VisualBasic.Left(Environment.CurrentDirectory, _
        '    Len(Environment.CurrentDirectory) - 3)
        Dim strCurrentDirectory As String = _
        Application.StartupPath

        ' Initialize paths for both types of key files.
        strRijndaelSaltIVFile = strCurrentDirectory & "\RijndaelSaltIV.dat"
        strTripleDESSaltIVFile = strCurrentDirectory & "\TripleDESSaltIV.dat"

        strSourcePath = strCurrentDirectory & "\SysPara.xml"

        ' Set the current key file path to the key for default crypto type.
        strCurrentKeyFile = strRijndaelSaltIVFile

        '' Call Select() to put focus on the "Encrypt" button and prevent the text in 
        '' the TextBox from being automatically highlighted.
        'btnEncrypt.Select()

        FormHasLoaded = True

    End Sub

    Public Sub Process(ByRef pType As String)
        'Dim btn As Button = CType(sender, Button)
        ED_LOAD()

        Try

            If IsValid() Then
                With crpSample
                    .SaltIVFile = strCurrentKeyFile
                    .Password = "Feey1234"
                End With
            Else
                Exit Sub
            End If

            crpSample.SourceFileName = strSourcePath

            If Trim(pType) = "btnEncrypt" Then
                crpSample.EncryptFile()
            Else
                crpSample.DecryptFile()
            End If

            strCrypto = ReadFileAsString(strSourcePath)

        Catch expCrypto As CryptographicException
            MsgBox("The file could not be decrypted. Make sure you entered " & _
                "the correct password. " & vbCrLf & "This error can also be caused by changing " & _
                "crypto type between encryption and decryption.", _
                MsgBoxStyle.Critical, "MRP_PROG")
        Catch exp As Exception
            MsgBox(exp.Message, MsgBoxStyle.Critical, "MRP_PROG")
        End Try
    End Sub

    Private Function IsValid() As Boolean
        If Not PasswordIsValid() Then
            Return False
        End If

        If strSourcePath = "" Then
            MsgBox("You must first load a source file!", MsgBoxStyle.Exclamation, "MRP_PROG")
            Return False
        End If

        Return True
    End Function

    Private Function PasswordIsValid() As Boolean
        'If Not Regex.IsMatch(txtPassword.Text, "^\s*(\w){8}\s*$") Then
        '    MsgBox("You must enter an 8-digit password consisting of numbers " & _
        '        "and/or letters.", MsgBoxStyle.Exclamation, Me.Text)
        '    Return False
        'End If

        'Return True
        If Not Regex.IsMatch("12345678", "^\s*(\w){8}\s*$") Then
            MsgBox("You must enter an 8-digit password consisting of numbers " & _
                "and/or letters.", MsgBoxStyle.Exclamation, "MRP_PROG")
            Return False
        End If

        Return True

    End Function

    Shared Function ReadFileAsString(ByVal path As String) As String
        Dim fs As New FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read)
        Dim abyt(CInt(fs.Length - 1)) As Byte

        fs.Read(abyt, 0, abyt.Length)
        fs.Close()

        Return UTF8.GetString(abyt)
    End Function


    '========================================================================


    'Insert LotDetail By KSLIM 18-Oct-2004
    Public Sub InitSchedule(ByRef ptmpDs As DataSet)

        Dim pDesc As String
        Dim pMonth As String
        Dim pSignal As String
        Dim pWeekNo As String
        Dim pMCNo As String
        Dim pModel As String
        Dim pSN As String
        Dim pYear As String = Year(Today)
        Dim pId_Upd As String = ""


        isError = False
        If Trim(DataBaseType) = "SQL SERVER" Then
            Dim myConnection As New SqlConnection(myErpSqlUserConn)
            myConnection.Open()