mrp_prg.vb

connect db by vb.net

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

            'Case "PLANNING"
            '    rtnstr = Trim(CounterDS.Tables(0).Rows(0)("Plan_PRF")) & _
            '    Trim(CStr(CounterDS.Tables(0).Rows(0)("Plan_Ref_No")).PadLeft(8, "0"))
            '    If pUpdate Then
            '        Me.searchMaster1("update s_counterMaster set Plan_ref_no = " & _
            '        CounterDS.Tables(0).Rows(0)("Plan_Ref_No") + 1, "CounterMaster")
            '    End If

            'Case "PRODUCTION"
            '    rtnstr = Trim(CounterDS.Tables(0).Rows(0)("PROD_PRF")) & _
            '    Trim(CStr(CounterDS.Tables(0).Rows(0)("PROD_Ref_No")).PadLeft(8, "0"))
            '    If pUpdate Then
            '        Me.searchMaster1("update s_counterMaster set PROD_ref_no = " & _
            '        CounterDS.Tables(0).Rows(0)("PROD_Ref_No") + 1, "CounterMaster")
            '    End If

            'Case "PRODREC"
            '    rtnstr = Trim(CounterDS.Tables(0).Rows(0)("PROD_REC_PRF")) & _
            '    Trim(CStr(CounterDS.Tables(0).Rows(0)("PROD_REC_Ref_No")).PadLeft(8, "0"))
            '    If pUpdate Then
            '        Me.searchMaster1("update s_counterMaster set PROD_REC_ref_no = " & _
            '        CounterDS.Tables(0).Rows(0)("PROD_REC_Ref_No") + 1, "CounterMaster")
            '    End If

            'Case "DO"
            '    rtnstr = Trim(CounterDS.Tables(0).Rows(0)("DO_PRF")) & _
            '    Trim(CStr(CounterDS.Tables(0).Rows(0)("DO_Ref_No")).PadLeft(8, "0"))
            '    If pUpdate Then
            '        Me.searchMaster1("update s_counterMaster set DO_ref_no = " & _
            '        CounterDS.Tables(0).Rows(0)("DO_Ref_No") + 1, "CounterMaster")
            '    End If

            'Case "REWORK"
            '    rtnstr = Trim(CounterDS.Tables(0).Rows(0)("RWK_PRF")) & _
            '    Trim(CStr(CounterDS.Tables(0).Rows(0)("RWK_Ref_No")).PadLeft(8, "0"))
            '    If pUpdate Then
            '        Me.searchMaster1("update s_counterMaster set RWK_ref_no = " & _
            '        CounterDS.Tables(0).Rows(0)("RWK_Ref_No") + 1, "CounterMaster")
            '    End If

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

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

            'Case "TRAN"

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

            'Case "MCHIS"

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

            Case "SCH"

                rtnstr = Trim(CounterDS.Tables(0).Rows(0)("SCH_Prf")) & _
                Trim(CStr(CounterDS.Tables(0).Rows(0)("SCH_Prf_No")).PadLeft(8, "0"))
                If pUpdate Then
                    Me.searchMaster1("update s_CompanyInfo set SCH_Prf_No = " & _
                    CounterDS.Tables(0).Rows(0)("SCH_Prf_No") + 1, "CompanyInfo")
                End If

            Case "STUD"

                rtnstr = Trim(CounterDS.Tables(0).Rows(0)("STUD_Prf")) & _
                Trim(CStr(CounterDS.Tables(0).Rows(0)("STUD_Prf_No")).PadLeft(8, "0"))
                If pUpdate Then
                    Me.searchMaster1("update s_CompanyInfo set STUD_Prf_No = " & _
                    CounterDS.Tables(0).Rows(0)("STUD_Prf_No") + 1, "CompanyInfo")
                End If

            Case "TRAN"

                rtnstr = Trim(CounterDS.Tables(0).Rows(0)("Tran_prf")) & _
                Trim(CStr(CounterDS.Tables(0).Rows(0)("Tran_ref_no")).PadLeft(8, "0"))
                If pUpdate Then
                    Me.searchMaster1("update s_CompanyInfo set Tran_ref_no = " & _
                    CounterDS.Tables(0).Rows(0)("Tran_ref_no") + 1, "CompanyInfo")
                End If

        End Select

        Return rtnstr

    End Function

    Public Function UpdatePartDetail( _
    ByRef pPartCode As String, ByRef pFld As String, _
    ByRef pValue As Decimal, ByRef pOperator As String)

        Dim PartDetailDS As DataSet = _
        Me.searchMaster1("select * from s_PartDetail where PartCode = '" & pPartCode & "' ", "PartDetail")

        If PartDetailDS.Tables(0).Rows.Count > 0 Then

            Dim orgValue As Decimal = PartDetailDS.Tables(0).Rows(0)(pFld)
            Dim newValue As Decimal = 0

            Select Case Trim(pOperator)
                Case "+"
                    newValue = orgValue + pValue
                Case "-"
                    newValue = orgValue - pValue
            End Select

            If newValue <= 0 Then
                newValue = 0
            End If

            Me.searchMaster1("update s_PartDetail set " & pFld & " = " & newValue & _
            " where ROWID = " & PartDetailDS.Tables(0).Rows(0)("ROWID"), "UpdatePartDetail")

        End If


    End Function

    Public Function UpdatePODetail( _
    ByRef pPartCode As String, ByRef pPONO As String, _
    ByRef pFld As String, ByRef pValue As Decimal, _
    ByRef pOperator As String)

        Dim PODTLDS As DataSet = _
        Me.searchMaster1("select * from s_PODtl " & _
        "where PartCode = '" & pPartCode & "' " & _
        "and PONO = '" & pPONO & "' ", "PODTL")

        If PODTLDS.Tables(0).Rows.Count > 0 Then

            Dim orgValue As Decimal = PODTLDS.Tables(0).Rows(0)(pFld)
            Dim newValue As Decimal = 0

            Select Case Trim(pOperator)
                Case "+"
                    newValue = newValue + pValue
                Case "-"
                    newValue = orgValue - pValue
            End Select

            If newValue <= 0 Then
                newValue = 0
            End If

            Me.searchMaster1("update s_PODTL set " & pFld & " = " & newValue & _
            " where ROWID = " & PODTLDS.Tables(0).Rows(0)("ROWID"), "UpdatePODetail")

        End If

    End Function

    Public Function getCurrTime() As String
        Return Date.Now.ToString.Substring(11, 5)
    End Function

    Public Function getCurrDate() As String
        Return Date.Now.ToString.Substring(0, 10)
    End Function


    '============================================================
    '              SDP System Only
    '============================================================

    'Insert LotDetail By KSLIM 18-Oct-2004
    Public Sub InsertLotDetail( _
    ByRef pLotNo As String, _
    ByRef pStation As String, _
    ByRef pKeyBy As String, _
    ByRef pQty As Decimal, _
    ByRef pType As String, _
    ByRef pStatus As String, _
    ByRef pReason As String, _
    ByRef pTime As String, _
    ByRef pPostDate As String, _
    ByRef pBatchNo As Integer, _
    ByRef pBatchID As Integer, _
    ByRef pId_Upd As String)

        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 = _
            "INSERT INTO s_LotDetail " & _
            "(LotNo,Station,KeyBy,Qty,Type,Status,Reason,PostTime,PostDate,BatchNo,BatchID, id_upd,dt_upd) " & _
            "VALUES" & _
            "(@LotNo,@Station,@KeyBy,@Qty,@Type,@Status,@Reason,@PostTime,@PostDate,@BatchNo,@BatchID, @id_upd,@dt_upd) "

            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


            ' 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 written to 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 : " & "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
        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 = _
                     "INSERT INTO s_LotDetail " & _
                     "(LotNo,Station,KeyBy,Qty,Type,Status,Reason,PostTime,PostDate,BatchNo,BatchID, id_upd,dt_upd) " & _
                     "VALUES" & _
                     "(@LotNo,@Station,@KeyBy,@Qty,@Type,@Status,@Reason,@PostTime,@PostDate,@BatchNo,@BatchID, @id_upd,@dt_upd) "

            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.Text).Value = Today.ToShortDateString


            ' 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 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  : " & pLotNo & ", " & "" & ", " & 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

    'Update LotDetail By KSLIM 16-Oct-2004
    Public Sub UpdateLotDetail( _
    ByRef pLotNo As String, _
    ByRef pStation As String, _
    ByRef pKeyBy As String, _
    ByRef pQty As Decimal, _
    ByRef pType As String, _
    ByRef pStatus As String, _
    ByRef pReason As String, _
    ByRef pTime As String, _
    ByRef pPostDate As String, _
    ByRef pBatchNo As Integer, _
    ByRef pBatchID As Integer, _
    ByRef pId_Upd As String, _
    ByRef pROWID As Integer)
        isError = False
        If Trim(DataBaseType) = "SQL SERVER" Then
            Dim myConnection As New SqlConnection(myErpSqlUserConn)
            myConnection.Open()