Testing Mo

從實踐中學習

2008-09-23

FillDataSet - 3 Types

  • 1338
  • 0
  • 2008-11-13

摘要:fillDataSetFeeCodeFromServer

 

 

 Example 3 - one dataset more than 1 Tables

    Private Sub FillDataSetRemark()

        Try

            Dim ltRecKeys As gtRecordKeys
            Dim liCount As Integer

            With ltRecKeys
                .TableName = "Lab_Remark"
                .SQLExtraSelect = BLANK
                .NumberOfKeys = 0
                Erase .LastSearchKeyValues
                .NumberOfRequiredFields = 1
                ReDim .RequiredFields(.NumberOfRequiredFields)
                .RequiredFields(0) = "LR_EngDesc"

                .SQLOrder = "LR_EngDesc"
            End With

            ' Construct Select SQL statement
            Call GenerateSqlString(ltRecKeys)

            Dim adapter As New OleDbDataAdapter
            adapter.SelectCommand = New OleDbCommand(ltRecKeys.SQLString, gcnOLEDBHIS)
            adapter.Fill(Me.mdsLabDataSet, "Lab_Remark")
            adapter.SelectCommand.Dispose()
            adapter.Dispose()

        Catch Err As Exception
            Call ErrHandler(Err.Message, APP_NAME)
        End Try

    End Sub
    Private Sub FillDataSetAntibiotic()

        Try

            Dim ltRecKeys As gtRecordKeys
            Dim liCount As Integer

            With ltRecKeys
                .TableName = "Lab_Antibiotic"
                .SQLExtraSelect = BLANK
                .NumberOfKeys = 0
                Erase .LastSearchKeyValues
                .NumberOfRequiredFields = 1
                ReDim .RequiredFields(.NumberOfRequiredFields)
                .RequiredFields(0) = "LA_EngDesc"

                .SQLOrder = "LA_EngDesc"
            End With

            ' Construct Select SQL statement
            Call GenerateSqlString(ltRecKeys)

            Dim adapter As New OleDbDataAdapter
            adapter.SelectCommand = New OleDbCommand(ltRecKeys.SQLString, gcnOLEDBHIS)
            adapter.Fill(Me.mdsLabDataSet, "Lab_Antibiotic")
            adapter.SelectCommand.Dispose()
            adapter.Dispose()

        Catch Err As Exception
            Call ErrHandler(Err.Message, APP_NAME)
        End Try

Example 2 - fill Dataset and Combo

    Private Sub FillDataSetDoctorName(ByVal piDrStatus As Integer)

        Try

            Dim ltRecKeys As gtRecordKeys
            Dim liCount As Integer

            With ltRecKeys
                .TableName = "Doctor"
                .SQLExtraSelect = BLANK
                .NumberOfKeys = 0
                Erase .LastSearchKeyValues
                If piDrStatus <> DOC_STATUS_ALL Then
                    .SQLExtraCriteria = " Doctor_Status= " & VarSQLString(piDrStatus)
                End If

                .NumberOfRequiredFields = 3
                ReDim .RequiredFields(.NumberOfRequiredFields)
                .RequiredFields(0) = "Doctor_Name = LTrim(Rtrim(Doctor_Surname)) + ' '+ Doctor_GivenName+' '+Doctor_Engname+' '+ Chiname"
                .RequiredFields(1) = "Doctor_Code"
                .RequiredFields(2) = "Dr_Remarks"

                .SQLOrder = "Doctor_Name,Doctor_Code"
            End With

            ' Construct Select SQL statement
            Call GenerateSqlString(ltRecKeys)

            Dim adapter As New OleDbDataAdapter
            mdsDoctor = Nothing
            mdsDoctor = New DataSet
            adapter.SelectCommand = New OleDbCommand(ltRecKeys.SQLString, gcnOLEDBHIS)
            adapter.Fill(mdsDoctor, msDoctorTableName)
            adapter.SelectCommand.Dispose()
            adapter.Dispose()

        Catch Err As Exception
            Call ErrHandler(Err.Message, APP_NAME)
        End Try

    End Sub
    Public Sub FillComboDoctorName(ByRef pcboAny As C1.Win.C1List.C1Combo, ByVal psTable As System.Data.DataTable)

        Try

            pcboAny.DataSource = psTable.DefaultView

            pcboAny.DisplayMember = "Doctor_Name"
            pcboAny.ValueMember = "Doctor_Code"
            pcboAny.HScrollBar.Style = C1.Win.C1List.ScrollBarStyleEnum.Automatic

            pcboAny.Splits(0).DisplayColumns(0).Width = 350
            pcboAny.Splits(0).DisplayColumns(1).Width = 70
            pcboAny.Splits(0).DisplayColumns(2).Width = 180


            pcboAny.DropdownWidth = 600
            pcboAny.AutoDropDown = True
            pcboAny.AutoCompletion = True
            pcboAny.AutoSize = True

        Catch Err As Exception
            Call ErrHandler(Err.Message, APP_NAME)
        End Try

    End Sub

 

 

Example1 - fill dataset only

    Public Sub FillDataSetPriceTurnTime(ByRef pdsSQLResult As DataSet)
        ' this function - Load FeeCode from server and Save in Dataset
        'reviewed
        Try

            Dim ltReckeys As gtRecordKeys
            Dim adapter As New OleDbDataAdapter
            Dim ltReckeys2 As gtRecordKeys

            With ltReckeys

                .TableName = "Fee_Code_Price_View FP"
                .SQLExtraSelect = ", Lab_Test LT"
                .NumberOfKeys = 0

                Erase .LastSearchKeyValues
                .SQLExtraCriteria += " FP.Fee_Type_Code = " & VarSQLString(FEE_TYPE_CODE_LA)
                .SQLExtraCriteria += " And FP.Hosp_Class_Code = " & VarSQLString(gsHospClassCode)
                .SQLExtraCriteria += " And FP.Fee_Code = LT.Fee_Code"
                .SQLExtraCriteria += " And FP.Fee_Type_code = LT.Fee_Type_Code"

                .NumberOfRequiredFields = 4
                ReDim .RequiredFields(.NumberOfRequiredFields)
                .RequiredFields(0) = "FP.Fee_Code"
                .RequiredFields(1) = "FP.Price"
                .RequiredFields(2) = "LT.Turn_Time"
                .RequiredFields(3) = "LT.Test_Performed_By"

            End With

            Call GenerateSqlString(ltReckeys)

            ' as the FeeTypeCode of H005 is HI  
            ' so we have to union the SQL
            ' Special case
            With ltReckeys2

                .TableName = "Fee_Code_Price_View FP"
                .SQLExtraSelect = ", Lab_Test LT"
                .NumberOfKeys = 0

                Erase .LastSearchKeyValues
                .SQLExtraCriteria = "FP.Fee_Code = " & VarSQLString("H005")
                .SQLExtraCriteria += " And FP.Fee_Type_Code = " & VarSQLString(FEE_TYPE_CODE_HI)
                .SQLExtraCriteria += " And FP.Hosp_Class_Code = " & VarSQLString(gsHospClassCode)
                .SQLExtraCriteria += " And FP.Fee_Code = LT.Fee_Code"
                .SQLExtraCriteria += " And FP.Fee_Type_code = LT.Fee_Type_Code"

                .NumberOfRequiredFields = 4
                ReDim .RequiredFields(.NumberOfRequiredFields)
                .RequiredFields(0) = "FP.Fee_Code"
                .RequiredFields(1) = "FP.Price"
                .RequiredFields(2) = "LT.Turn_Time"
                .RequiredFields(3) = "LT.Test_Performed_By"

            End With

            Call GenerateSqlString(ltReckeys2)
            ltReckeys.SQLString += " union " + ltReckeys2.SQLString

            pdsSQLResult = Nothing
            pdsSQLResult = New DataSet
            adapter.SelectCommand = New OleDbCommand(ltReckeys.SQLString, gcnOLEDBHIS)
            adapter.Fill(pdsSQLResult, "PriceTurnTime")
            adapter.SelectCommand.Dispose()
            adapter.Dispose()

        Catch Err As Exception
            Call ErrHandler(Err.Message, APP_NAME)
        End Try

    End Sub

 

 

 

------------------

熱愛生命 喜愛新奇 有趣的事物

 

過去 是無法改變
將來 卻能夠創造

 

希望使大家生活更便利
世界更美好

 

a guy who loves IT and life