mrp_prg.vb

connect db by vb.net

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

        Else

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

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


            ' 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
                Dim count As Integer

                'Dim sStr As String = _
                '"INSERT INTO s_Schedule " & _
                '"(Description,MTH,Signal,WKNO,MCNo,Model,SN,YR, id_upd,dt_upd) " & _
                '"VALUES" & _
                '"(@Description,@MTH,@Signal,@WKNO,@MCNo,@Model,@SN,@YR, @id_upd,@dt_upd) "


                Do While count < ptmpDs.Tables(1).Rows.Count

                    pDesc = ptmpDs.Tables(1).Rows(count)("Desc")
                    pMonth = ptmpDs.Tables(1).Rows(count)("Month")
                    pSignal = ptmpDs.Tables(1).Rows(count)("Signal")
                    pWeekNo = ptmpDs.Tables(1).Rows(count)("WeekNo")
                    pMCNo = ptmpDs.Tables(1).Rows(count)("McNo")
                    pModel = ptmpDs.Tables(1).Rows(count)("Model")
                    pSN = ptmpDs.Tables(1).Rows(count)("SN")
                    pWeekNo = ptmpDs.Tables(1).Rows(count)("WeekNo")

                    Dim sStr As String = _
                    "INSERT INTO s_Schedule " & _
                    "(Description,MTH,Signal,WKNO,MCNo,Model,SN,YR, id_upd,dt_upd) " & _
                    "VALUES" & _
                    "('" & pDesc & "','" & pMonth & "'," & _
                    "'" & pSignal & "','" & pWeekNo & "'," & _
                    "'" & pMCNo & "','" & pModel & "'," & _
                    "'" & pSN & "','" & pYear & "'," & _
                    "'" & pId_Upd & "','" & Today.ToShortDateString & "'" & _
                    ") "



                    'myCommand.Parameters.Add("@Description", System.Data.SqlDbType.Char).Value = IIf(Trim(pDesc) = "", " ", Trim(pDesc))
                    'myCommand.Parameters.Add("@MTH", System.Data.SqlDbType.Char).Value = IIf(Trim(pMonth) = "", " ", Trim(pMonth))
                    'myCommand.Parameters.Add("@Signal", System.Data.SqlDbType.Char).Value = IIf(Trim(pSignal) = "", " ", Trim(pSignal))
                    'myCommand.Parameters.Add("@WkNo", System.Data.SqlDbType.Char).Value = IIf(Trim(pWeekNo) = "", " ", Trim(pWeekNo))
                    'myCommand.Parameters.Add("@MCNo", System.Data.SqlDbType.Char).Value = IIf(Trim(pMCNo) = "", " ", Trim(pMCNo))
                    'myCommand.Parameters.Add("@Model", System.Data.SqlDbType.Char).Value = IIf(Trim(pModel) = "", " ", Trim(pModel))
                    'myCommand.Parameters.Add("@SN", System.Data.SqlDbType.Char).Value = IIf(Trim(pSN) = "", " ", Trim(pSN))
                    'myCommand.Parameters.Add("@YR", System.Data.SqlDbType.Char).Value = IIf(Trim(pYear) = "", " ", Trim(pYear))

                    'myCommand.Parameters.Add("@id_Upd", System.Data.SqlDbType.Char).Value = pId_Upd
                    'myCommand.Parameters.Add("@dt_Upd", System.Data.SqlDbType.Text).Value = Today.ToShortDateString

                    myCommand.CommandText = sStr
                    myCommand.ExecuteNonQuery()


                    count = count + 1
                Loop

                myTrans.Commit()
                Console.WriteLine("Both records are written to 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  : " & pYear & ", " & "" & ", " & pId_Upd & ControlChars.NewLine _
                            & "Module : " & "insertItem" & 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


    Public Function isTimeFormat(ByRef inTime As String) As Boolean
        Dim NumberList As String = "1234567890"


        If inTime.Length < 5 Or inTime.Length > 5 Then
            Return False
        End If

        If NumberList.IndexOf(inTime.Substring(0, 1)) < 0 Then
            Return False
        End If

        If NumberList.IndexOf(inTime.Substring(1, 1)) < 0 Then
            Return False
        End If

        If NumberList.IndexOf(inTime.Substring(3, 1)) < 0 Then
            Return False
        End If

        If NumberList.IndexOf(inTime.Substring(4, 1)) < 0 Then
            Return False
        End If

        'If NumberList.IndexOf(inTime.Substring(6, 1)) < 0 Then
        '    Return False
        'End If

        'If NumberList.IndexOf(inTime.Substring(7, 1)) < 0 Then
        '    Return False
        'End If

        'If NumberList.IndexOf(inTime.Substring(8, 1)) < 0 Then
        '    Return False
        'End If

        'If NumberList.IndexOf(inTime.Substring(9, 1)) < 0 Then
        '    Return False
        'End If
        If inTime.Length >= 3 Then
            If inTime.Substring(2, 1) <> "." Then
                Return False
            End If
        Else
            If inTime.Substring(1, 1) <> "." Then
                Return False
            End If

        End If

        If IsNumeric(inTime) Then
            If CDec(inTime) > 24 Then
                Return False
            End If
        End If

        Return True
    End Function


    Public Function encrypt(ByRef pPSW As String) As String
        Dim p As String = ""
        Dim count As Integer = 0

        'MsgBox(Trim(Me.MRP_Prog.myCHR_M_Code))
        'MsgBox(Trim(Me.MRP_Prog.myCHR_D_Code))
        'MsgBox(Trim(Me.MRP_Prog.myCHR_Code))

        Do While count < pPSW.Length



            p = p & Trim(Me.myCHR_M_Code) & _
            Trim(Str((Asc(pPSW.Substring(count, 1)) Mod Me.myINT_Code))) & _
            Trim(Me.myCHR_D_Code) & _
            Trim(Str((Int(Asc(pPSW.Substring(count, 1)) / Me.myINT_Code)))) & _
            Trim(Me.myCHR_Code)

            count = count + 1
        Loop

        Return p
    End Function

    Public Function decrypt(ByRef pPSW As String) As String
        Dim p As String = ""
        Dim count As Integer = 0

        Do While count < pPSW.Length



            'Dim pECHR_Code As Integer = pPSW.IndexOf(Me.MRP_Prog.myCHR_Code, count + 1)

            'p = p & Trim(Me.DeCode(pPSW.Substring(count + 1, pECHR_Code - (count + 1))))

            'count = pECHR_Code + 1


            Dim pSCHR_Code As Integer

            If count = 0 Then
                pSCHR_Code = 0
            Else
                pSCHR_Code = count
            End If

            Dim pECHR_Code As Integer = pPSW.IndexOf(Me.myCHR_Code, pSCHR_Code + 1)

            p = p & Trim(Me.DeCode(pPSW.Substring(pSCHR_Code, pECHR_Code - pSCHR_Code)))

            count = pECHR_Code + 1
        Loop

        Return p
    End Function

    Public Function DeCode(ByRef pStr As String) As String
        Dim count As Integer = 0
        Dim pMod As String
        Dim pDiv As String
        Dim p As String = ""

        Dim iMod As Integer = pStr.IndexOf(Me.myCHR_M_Code)
        Dim iDiv As Integer = pStr.IndexOf(Me.myCHR_D_Code)

        'get Modulus Code
        pMod = CInt(pStr.Substring(iMod + 1, iDiv - (iMod + 1)))
        pDiv = CInt(pStr.Substring(iDiv + 1, pStr.Length - (iDiv + 1)))

        p = Chr(Me.myINT_Code * pDiv + pMod)

        Return p

    End Function

    Public Shared Sub LogFile( _
    ByRef pFileName As String, _
    ByRef pUserID As String, _
    ByRef pModule As String, _
    ByRef pAction As String, _
    ByRef pLogMessage As String)
        Dim w As StreamWriter = File.AppendText(Application.StartupPath & "\" & pFileName & ".txt")
        Log(pUserID, pModule, pAction, pLogMessage, w)
        'Log("Test2", w)
        ' Close the writer and underlying file.
        w.Close()
        ' Open and read the file.
        Dim r As StreamReader = File.OpenText(Application.StartupPath & "\" & pFileName & ".txt")
        DumpLog(r)
    End Sub
    Public Shared Sub Log( _
    ByRef pUserID As String, _
    ByRef pModule As String, _
    ByRef pAction As String, _
    ByRef pLogMessage As String, _
    ByVal w As TextWriter)
        w.Write(ControlChars.CrLf & "Log Entry : ")
        w.WriteLine("{0} {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
        w.WriteLine("User ID  :{0}", pUserID)
        w.WriteLine("Module  :{0}", pModule)
        w.WriteLine("Action  :{0}", pAction)
        w.WriteLine("Message  :{0}", pLogMessage)
        w.WriteLine("-------------------------------")
        ' Update the underlying file.
        w.Flush()
    End Sub
    Public Shared Sub DumpLog(ByVal r As StreamReader)
        ' While not at the end of the file, read and write lines.
        Dim line As String
        line = r.ReadLine()
        While Not line Is Nothing
            Console.WriteLine(line)
            line = r.ReadLine()
        End While
        r.Close()
    End Sub


    Public Function myLabelName(ByRef sLabelName As String) As String

        Dim Doc As New XmlDocument
        Dim Nav As XPath.XPathNavigator
        Dim Iterator As XPath.XPathNodeIterator
        Dim conStr As String

        On Error GoTo error_message

        Doc.Load(myLibPath() & "\Label.xml")

        'Set nav object.
        Nav = CType(Doc, XPath.IXPathNavigable).CreateNavigator()

        'Set node iterator.
        Iterator = Nav.Select("root/" & sLabelName)

        'Move to the desired node.
        Iterator.MoveNext()

        'Get the value of the current node.
        conStr = Iterator.Current.Value

        Return conStr

error_message:
        Return "nil"

    End Function

    Public Function myLibPath() As String

        Dim Doc As New XmlDocument
        Dim Nav As XPath.XPathNavigator
        Dim Iterator As XPath.XPathNodeIterator
        Dim conStr As String

        On Error GoTo error_message

        Doc.Load(Application.StartupPath & "\SysPara.xml")

        'Set nav object.
        Nav = CType(Doc, XPath.IXPathNavigable).CreateNavigator()

        'Set node iterator.
        Iterator = Nav.Select("root/" & "LibraryPath")

        'Move to the desired node.
        Iterator.MoveNext()

        'Get the value of the current node.
        conStr = Iterator.Current.Value

        Return conStr

error_message:
        Return "nil"

    End Function


    Public Function myXML_Value(ByRef pXMLCode As String) As String

        Dim Doc As New XmlDocument
        Dim Nav As XPath.XPathNavigator
        Dim Iterator As XPath.XPathNodeIterator
        Dim conStr As String


        If Trim(myConnStr) <> "" Then
            conStr = myConnStr
        Else
            'MsgBox(Application.StartupPath)
            Doc.Load(Application.StartupPath & "\SysPara.xml")

            'Set nav object.
            Nav = CType(Doc, XPath.IXPathNavigable).CreateNavigator()

            'Set node iterator.
            If Trim(DataBaseType) = "SQL SERVER" Then
                Iterator = Nav.Select("root/" & pXMLCode)
            Else
                Iterator = Nav.Select("root/" & pXMLCode)
            End If


            'Move to the desired node.
            Iterator.MoveNext()

            'Get the value of the current node.
            conStr = Iterator.Current.Value

        End If


        Return conStr

    End Function
End Class