mrp_prg.vb

connect db by vb.net

Imports System.IO
Imports System.Text.Encoding
Imports System.Text.RegularExpressions
Imports System.Security.Cryptography

Imports System
Imports System.Globalization
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms
Imports System.xml


Public Class mrp_prg

    Public Com_Name As String = "SDP Manufacturing Sdn. Bhd."
    Public Com_Code As String = ""
    Public COm_Tel As String = "07-8618800"
    Public Com_Fax As String = "07-8616800"
    'Public DataBaseType As String = "SQL SERVER"
    Public DataBaseType As String = "ACCESS"
    Public isError As Boolean
    Public myConnStr As String

    Public Function myErpSqlUserConn() 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/" & "SQL_Con2")
            Else
                Iterator = Nav.Select("root/" & "SQL_Con3")
            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

    Public Function myCHR_Code() 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/" & "CHR_CODE")
            Else
                Iterator = Nav.Select("root/" & "CHR_CODE")
            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

    Public Function myCHR_M_Code() 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/" & "CHR_M_Code")
            Else
                Iterator = Nav.Select("root/" & "CHR_M_Code")
            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

    Public Function myCHR_D_Code() 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/" & "CHR_D_Code")
            Else
                Iterator = Nav.Select("root/" & "CHR_D_Code")
            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


    Public Function myINT_Code() As Integer

        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/" & "INT_CODE")
            Else
                Iterator = Nav.Select("root/" & "INT_CODE")
            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


    Public Function searchMaster1(ByRef inStr As String, ByRef tblName As String) As DataSet

        Dim connectionString As String = Me.myErpSqlUserConn

        If DataBaseType = "SQL SERVER" Then
            Dim sStr As String = inStr
            Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand(sStr, sqlConnection)
            Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)

            Dim tmpDS As System.Data.DataSet = New System.Data.DataSet
            '
            sqlCommand.CommandType = CommandType.Text '.StoredProcedure

            sqlConnection.Open()
            Try
                dataAdapter.Fill(tmpDS, tblName)

            Catch e As SqlException
                'Throw sErr
                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
                Next i
                MsgBox(errorMessages, MsgBoxStyle.Exclamation, "Total ERP SQL Adapter")
            Finally
                sqlConnection.Close()
            End Try

            Return tmpDS
        Else
            Dim sStr As String = inStr

            Dim oledbConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
            Dim oledbCommand As New System.Data.OleDb.OleDbCommand(sStr, oledbConnection)
            Dim oledbdataAdapter As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(oledbCommand)


            Dim tmpDS As System.Data.DataSet = New System.Data.DataSet
            '
            oledbCommand.CommandType = CommandType.Text '.StoredProcedure

            oledbConnection.Open()
            Try
                oledbdataAdapter.Fill(tmpDS, tblName)

            Catch e As OleDb.OleDbException
                'Throw sErr
                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
                Next i
                MsgBox(errorMessages, MsgBoxStyle.Exclamation, "Total ERP SQL Adapter")
            Finally
                oledbConnection.Close()
            End Try

            Return tmpDS


        End If

    End Function


    Public Function getValue(ByRef inSQLStr As String, ByRef RtnFld As String) As Object

        Dim tempDs As DataSet = Me.searchMaster1(inSQLStr, "tbl0")

        If tempDs.Tables(0).Rows.Count > 0 Then
            Return tempDs.Tables(0).Rows(0)(RtnFld)
        End If

        Return 0

    End Function


    Public Function isFound(ByRef inSQLStr As String) As Boolean

        Dim tempDs As DataSet = Me.searchMaster1(inSQLStr, "tbl0")

        If tempDs.Tables(0).Rows.Count > 0 Then
            Return True
        End If

        Return False

    End Function

    'Author :KSLIM
    'Check the format of the Date 
    'Due to system still can accept "5//5//03" this invalid date format
    'Even used the function of  "isDate"
    Public Function isDateFormat(ByRef indate As String) As Boolean
        Dim NumberList As String = "1234567890"

        If indate.Length < 10 Or indate.Length > 10 Then
            Return False
        End If

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

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

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

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

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

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

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

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

        If indate.Substring(2, 1) <> "/" Then
            Return False
        End If

        If indate.Substring(5, 1) <> "/" Then
            Return False
        End If
        Return True
    End Function

    Public Function getCounter(ByRef pAction As String, ByRef pUpdate As Boolean) As String
        Dim CounterDS As DataSet = _
        Me.searchMaster1("select * from s_CompanyInfo ", "CompanyInfo")
        Dim rtnstr As String
        Dim mCounter As Integer

        Select Case Trim(pAction.ToUpper)
            'Case "PO"

            '    rtnstr = Trim(CounterDS.Tables(0).Rows(0)("PO_PRF")) & _
            '    Trim(CStr(CounterDS.Tables(0).Rows(0)("PO_Ref_No")).PadLeft(8, "0"))
            '    If pUpdate Then
            '        Me.searchMaster1("update s_counterMaster set po_ref_no = " & _
            '        CounterDS.Tables(0).Rows(0)("PO_Ref_No") + 1, "CounterMaster")
            '    End If

            'Case "SALES"
            '    rtnstr = Trim(CounterDS.Tables(0).Rows(0)("SPO_PRF")) & _
            '    Trim(CStr(CounterDS.Tables(0).Rows(0)("SPO_Ref_No")).PadLeft(8, "0"))
            '    If pUpdate Then
            '        Me.searchMaster1("update s_counterMaster set Spo_ref_no = " & _
            '        CounterDS.Tables(0).Rows(0)("spo_Ref_No") + 1, "CounterMaster")
            '    End If


            'Case "GID"