ช่วยแก้ code vb.net ให้หน่อยครับ insert ข้อมูลแล้วออกมาทีเดียว5 แถวทำไงดีครับ

Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frmOrderDispart

    Private connect As SqlConnection = getconnect()
    Private connect2 As SqlConnection = getconnect()
    Private reader As SqlDataReader
    Private SqlString, SqlString2 As String
    Private Sqlcom As SqlCommand
    Private Sqlcmd As SqlCommand
    Private AutoCount As Integer
    Private sqlAdapter As SqlDataAdapter
    Dim successinsert As Integer

    Private CountAuto, CountS, CountProduct As Integer
    Private CountSum As Double


    Private Sub ClrText()

        txtDistanceKilo.Text = "0.00"
        txtSearchjob.Text = ""
        txtTruck1.Text = ""
        txtTruck2.Text = ""
        txtOilName1.Text = ""
        txtOilName2.Text = ""
        txtOilBurnRate1.Text = ""
        txtOilBurnRate2.Text = ""
        txtTotalCar1.Text = ""
        txtTotalCar2.Text = ""
        txtTotalTruck1.Text = ""
        txtTotalTruck2.Text = ""
        txtDistanceKiloTotal.Text = ""
        txtDistanceKiloTotal1.Text = ""
        txtDistanceKiloTotal2.Text = ""
        txtOilTotal.Text = ""
        txtOilTotal1.Text = ""
        txtOilTotal2.Text = ""
        txtPriceTotal.Text = ""
        txtPriceTotalCar1.Text = ""
        txtPriceTotalCar2.Text = ""
        txtPlace.Text = ""


    End Sub
    Private Sub SetAutoNo()


        SqlString = "SELECT OrderDP_ID  AS MINT_ID FROM  OrderDispart WHERE  OrderDP_ID LIKE '%DP%' ORDER BY OrderDP_ID DESC "
        '
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()

        reader = Sqlcom.ExecuteReader()

        CountAuto = 0
        CountProduct = 0

        If reader.Read Then

            CountAuto = CInt(Microsoft.VisualBasic.Right(reader("MINT_ID").ToString, 4)) + 1

            If CountAuto > 999 Then
                txtJobID.Text = Microsoft.VisualBasic.Left(reader("MINT_ID").ToString, 2) & CountAuto
            ElseIf CountAuto > 99 Then
                txtJobID.Text = Microsoft.VisualBasic.Left(reader("MINT_ID").ToString, 2) & "0" & CountAuto
            ElseIf CountAuto > 9 Then
                txtJobID.Text = Microsoft.VisualBasic.Left(reader("MINT_ID").ToString, 2) & "00" & CountAuto
            Else
                txtJobID.Text = Microsoft.VisualBasic.Left(reader("MINT_ID").ToString, 2) & "000" & CountAuto
            End If

            CountProduct = CountProduct + 1


        End If

        If CountProduct = 0 Then

            txtJobID.Text = "DP0001"

        End If
        txtStatus.Text = "S0:เสนอราคา"
        reader.Close()
        reader = Nothing
        connect.Close()
    End Sub

    Private Sub LoadBill()
        SetAutoNo()
        SetComboSoil1()
        SetComboCar1()
        SetComboSoil2()
        SetComboCar2()
        'Dim i As Integer

        If txtSearchjob.Text = "" Then
            SqlString = "SELECT Date_Order, OrderDP_ID , Status_ID, Status_Type, Cus_ID, Cus_Fname, " & _
            " Cus_Lname, Cus_Company, Soil_Name, Sum_Distance, Sum_Price, OrderDP_Cancel,  " & _
            " Area_Distance FROM dbo.V_Dispart ORDER BY OrderDP_ID DESC  "
        Else

            SqlString = "SELECT Date_Order, OrderDP_ID,  Status_ID, Status_Type,  Cus_ID, Cus_Fname, " & _
            " Cus_Lname, Cus_Company, Soil_Name, Sum_Distance, Sum_Price, OrderDP_Cancel, " & _
            " Area_Distance FROM dbo.V_Dispart  where OrderDP_ID like'%" & txtSearchjob.Text & "%' ORDER BY OrderDP_ID DESC  "
        End If
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()

        Dim sqlAdp As SqlDataAdapter = New SqlDataAdapter(Sqlcom)
        Dim ds As New DataSet()
        sqlAdp.Fill(ds, "OrderDP")

        dgvOrderDP.AutoGenerateColumns = False
        dgvOrderDP.DataSource = ds
        dgvOrderDP.DataMember = "OrderDP"
        connect.Close()
    End Sub

    Private Sub frmOrderDispart_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        lblEmpName.Text = clsvariable.ls
        LoadBill()
    End Sub

    Private Sub btnSearchCusName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchCusName.Click
        If txtJobID.Text <> "" Then
            clsvariable.TmpCusID = ""
            frmCustomerSelect.ShowDialog()
            If clsvariable.TmpCusID <> "" Then
                txtCusID.Text = clsvariable.TmpCusID
                txtCusFname.Text = clsvariable.TmpCusName
                txtCompany.Text = clsvariable.TmpCusCompany
                txtTel.Text = clsvariable.TmpCusTel
            End If
        End If
    End Sub

    Private Sub SetComboCar1()

        Dim tmpPro As String
        cboCar1.Items.Clear()
        SqlString = "SELECT     Car_ID, Car_Type FROM dbo.Car ORDER BY Car_ID "
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        reader = Sqlcom.ExecuteReader()

        While reader.Read
            tmpPro = reader("Car_ID").ToString & " - " & reader("Car_Type").ToString
            cboCar1.Items.Add(tmpPro)
        End While

        reader.Close()
        reader = Nothing
        connect.Close()
    End Sub
    Private Sub SetComboCar2()

        Dim tmpPro As String
        cboCar2.Items.Clear()
        SqlString = "SELECT     Car_ID, Car_Type FROM dbo.Car ORDER BY Car_ID "
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        reader = Sqlcom.ExecuteReader()

        While reader.Read
            tmpPro = reader("Car_ID").ToString & " - " & reader("Car_Type").ToString
            cboCar2.Items.Add(tmpPro)
        End While

        reader.Close()
        reader = Nothing
        connect.Close()
    End Sub

    Private Sub SetComboSoil1()

        Dim tmpPro As String
        cboSoil1.Items.Clear()
        SqlString = "SELECT     Soil_ID, Soil_Name FROM dbo.Soil ORDER BY Soil_ID "
        sqlCom = New SqlCommand(SqlString, connect)
        connect.Open()
        reader = sqlCom.ExecuteReader()
        While reader.Read
            tmpPro = reader("Soil_ID").ToString & " - " & reader("Soil_Name").ToString
            cboSoil1.Items.Add(tmpPro)
        End While

        reader.Close()
        reader = Nothing
        connect.Close()
      
    End Sub
    Private Sub SetComboSoil2()

        Dim tmpPro As String
        cboSoil2.Items.Clear()
        SqlString = "SELECT     Soil_ID, Soil_Name FROM dbo.Soil ORDER BY Soil_ID "
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        reader = Sqlcom.ExecuteReader()
        While reader.Read
            tmpPro = reader("Soil_ID").ToString & " - " & reader("Soil_Name").ToString
            cboSoil2.Items.Add(tmpPro)
        End While

        reader.Close()
        reader = Nothing
        connect.Close()
       
    End Sub

    Private Sub txtDistanceKilo_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDistanceKilo.LostFocus
        txtDistanceKiloTotal.SelectAll()
    End Sub

    Private Sub txtDistanceKilo_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles txtDistanceKilo.MouseClick
        txtDistanceKiloTotal.SelectAll()
    End Sub

    Private Sub cboCar1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCar1.SelectedIndexChanged
        If cboCar1.SelectedIndex > -1 Then
            If txtDistanceKilo.Text = "0.00" Or txtDistanceKilo.Text = "" Or txtTotalCar1.Text = "" Then

                MsgBox("กรุณาบันทึกข้อมูลรายการจำนวนรอบ และระยะทางให้ครบถ้วนครับ")
            Else

                LoadCar1()
            End If
        Else

        End If
    End Sub
    Private Sub LoadCar1()

        SqlString = "SELECT  Car_ID, Oi_IDl, Oil_Name, Oil_Price, Car_Type, Oilburn_Rate, Car_Rate  FROM dbo.V_CAR WHERE Car_ID = '" & Microsoft.VisualBasic.Left(cboCar1.Text, 5) & "'  "
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        reader = Sqlcom.ExecuteReader()

        While reader.Read
            txtTruck1.Text = Convert.ToDouble(reader("Car_Rate").ToString)
            txtOilName1.Text = reader("Oi_IDl").ToString & " : " & reader("Oil_Name").ToString
            txtOilBurnRate1.Text = Convert.ToDouble(reader("Oilburn_Rate").ToString)
            'txtTotalCar1.Text = Math.Floor(Convert.ToDouble(txtTruck1.Text))
            txtTotalTruck1.Text = Format(Convert.ToDouble(txtTruck1.Text * txtTotalCar1.Text), "##,###.00")
            txtDistanceKiloTotal1.Text = Format(Convert.ToDouble(txtDistanceKilo.Text * txtTotalCar1.Text), "##,###.00")
            txtOilTotal1.Text = Format(Convert.ToDouble(txtOilBurnRate1.Text * txtDistanceKiloTotal1.Text), "##,###.00")
            txtPriceTotalCar1.Text = Format(Convert.ToDouble(txtOilTotal1.Text * Convert.ToDouble(reader("Oil_Price").ToString)), "##,###.00")
        End While

        txtDistanceKiloTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtDistanceKiloTotal1.Text) + Convert.ToDouble(txtDistanceKiloTotal2.Text)), "##,###.00")
        txtOilTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtOilTotal1.Text) + Convert.ToDouble(txtOilTotal2.Text)), "##,###.00")
        txtPriceTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtPriceTotalCar1.Text) + Convert.ToDouble(txtPriceTotalCar2.Text)), "##,###.00")

        reader.Close()
        reader = Nothing
        connect.Close()
    End Sub
    Private Sub cboCar2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCar2.SelectedIndexChanged
        If cboCar2.SelectedIndex > -1 Then
            If txtDistanceKilo.Text = "0.00" Or txtDistanceKilo.Text = "" Or txtTotalCar2.Text = "" Then
                MsgBox("กรุณาบันทึกข้อมูลรายการจำนวนรอบ และระยะทางให้ครบถ้วนครับ")
            Else
                If cboCar1.Text <> cboCar2.Text Then
                    LoadCar2()
                Else
                    MsgBox("กรุณาตรวจสอบการเลือกรถ ด้วยครับ")
                End If
            End If
        End If
      
    End Sub
    Private Sub LoadCar2()

        SqlString = "SELECT  Car_ID, Oi_IDl, Oil_Name, Oil_Price, Car_Type, Oilburn_Rate, Car_Rate  FROM dbo.V_CAR WHERE Car_ID = '" & Microsoft.VisualBasic.Left(cboCar2.Text, 5) & "'  "
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        reader = Sqlcom.ExecuteReader()

        While reader.Read
            txtTruck2.Text = Convert.ToDouble(reader("Car_Rate").ToString)
            txtOilName2.Text = reader("Oi_IDl").ToString & " : " & reader("Oil_Name").ToString
            txtOilBurnRate2.Text = Convert.ToDouble(reader("Oilburn_Rate").ToString)
            'txtTotalCar2.Text = Math.Floor(Convert.ToDouble(txtSurplusTruck1.Text) / Convert.ToDouble(txtTruck2.Text)) + 1
            txtTotalTruck2.Text = Format(Convert.ToDouble(txtTruck2.Text * txtTotalCar2.Text), "##,###.00")
            'txtSurplusTruck2.Text = 0
            txtDistanceKiloTotal2.Text = Format(Convert.ToDouble(txtDistanceKilo.Text * txtTotalCar2.Text), "##,###.00")
            txtOilTotal2.Text = Format(Convert.ToDouble(txtDistanceKiloTotal2.Text * txtOilBurnRate2.Text), "##,###.00")
            txtPriceTotalCar2.Text = Format(Convert.ToDouble(txtOilTotal2.Text * Convert.ToDouble(reader("Oil_Price").ToString)), "##,###.00")
        End While

        txtDistanceKiloTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtDistanceKiloTotal1.Text) + Convert.ToDouble(txtDistanceKiloTotal2.Text)), "##,###.00")
        txtOilTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtOilTotal1.Text) + Convert.ToDouble(txtOilTotal2.Text)), "##,###.00")
        txtPriceTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtPriceTotalCar1.Text) + Convert.ToDouble(txtPriceTotalCar2.Text)), "##,###.00")

        reader.Close()
        reader = Nothing
        connect.Close()
    End Sub

    Private Sub txtTotalCar1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTotalCar1.LostFocus
        txtTotalCar1.SelectAll()
    End Sub

    Private Sub txtTotalCar1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles txtTotalCar1.MouseClick
        txtTotalCar1.SelectAll()
    End Sub

    Private Sub txtTotalCar2_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTotalCar2.LostFocus
        txtTotalCar2.SelectAll()
    End Sub


    Private Sub txtTotalCar2_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles txtTotalCar2.MouseClick
        txtTotalCar2.SelectAll()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        SetAutoNo()
        SetComboSoil1()
        SetComboCar1()
        SetComboSoil2()
        SetComboCar2()
        ClrText()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        SaveBill()
        LoadBill()
        'ClrText()
    End Sub

    Private Sub SaveBill()

        If (txtJobID.Text = "") Then
            MsgBox("ไม่มีรหัสงาน กรุณาระบุรหัสงานด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If

        If (txtStatus.Text = "") Then
            MsgBox("ไม่มีสถานะงาน กรุณาระบุสถานะงานด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If

        If txtDistanceKiloTotal.Text = "0.00" Or txtDistanceKiloTotal.Text = "" Then
            MsgBox("ค่าระยะทางไม่ถูกต้อง หรือยังไม่ระบุ กรุณาตรวจสอบด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If

        If txtOilTotal.Text = "0.00" Or txtOilTotal.Text = "" Then
            MsgBox("ค่าการใช้น้ำมันไม่ถูกต้อง หรือยังไม่ระบุ กรุณาตรวจสอบด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If

        If txtPriceTotal.Text = "0.00" Or txtPriceTotal.Text = "" Then
            MsgBox("ค่าราคารวมไม่ถูกต้อง หรือยังไม่ระบุ กรุณาตรวจสอบด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If

        If txtPlace.Text = "" Then
            MsgBox("ค่าราคารวมไม่ถูกต้อง หรือยังไม่ระบุ กรุณาตรวจสอบด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If

        Dim S0 As String
        S0 = Microsoft.VisualBasic.Left(txtStatus.Text, 2) = "S0"


        'SqlString = "INSERT INTO OrderDispart (  OrderDP_ID, Emp_ID, Cus_ID, Status_ID, Date_Order, Date_Start, Date_End, Soil_ID, , Arae_Distance, Space_Dispart, Sum_Distance, Sum_Oil, Sum_Price ) values " & _
        '" ('" & txtJobID.Text & "','" & lblEmpName.Text & " ','" & txtCusID.Text & " ','S1', '" & ConvertDate(Now.Date.Date) & "','" & ConvertDate(DateTimePicker2.Value) & " ','" & ConvertDate(DateTimePicker3.Value) & "','" & Microsoft.VisualBasic.Left(cboSoil1.Text, 5) & "'," & Convert.ToDouble(txtDistanceKilo.Text) & ",'" & txtPlace.Text & " '," & Convert.ToDouble(txtDistanceKiloTotal.Text) & "," & Convert.ToDouble(txtOilTotal.Text) & "," & Convert.ToDouble(txtPriceTotal.Text) & ")"

        SqlString = "INSERT INTO OrderDispart (  OrderDP_ID, Emp_ID, Cus_ID, Status_ID, Date_Order," & _
       "  Date_Start, Date_End, Soil_ID, Area_Distance, Space_Dispart, Sum_Distance, Sum_Oil, Sum_Price) values " & _
      " ('" & txtJobID.Text & "','" & lblEmpName.Text & " ','" & txtCusID.Text & " ','S1', '" & ConvertDate(Now.Date.Date) & "', " & _
      " '" & ConvertDate(DateTimePicker2.Value) & " ','" & ConvertDate(DateTimePicker3.Value) & "','" & Microsoft.VisualBasic.Left(cboSoil1.Text, 5) & "', " & _
      " " & Convert.ToDouble(txtDistanceKilo.Text) & ",'" & txtPlace.Text & " '," & _
      " " & Convert.ToDouble(txtDistanceKiloTotal.Text) & "," & Convert.ToDouble(txtOilTotal.Text) & "," & Convert.ToDouble(txtPriceTotal.Text) & ")"




        Sqlcmd = New SqlCommand(SqlString, connect)
        connect.Open()
        successinsert = Sqlcmd.ExecuteNonQuery()
        connect.Close()

        If cboCar1.SelectedIndex > -1 Then

            SaveCar1()
            txtStatus.Text = "S1:เสนอราคา"

        End If
        If cboCar2.SelectedIndex > -1 Then

            SaveCar2()

        End If

    End Sub

    Private Sub SaveCar1()

        SqlString = " SELECT     MAX(Orderdpdetail_ID) AS MAX FROM dbo.OrderDispartDetail"

        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()

        reader = Sqlcom.ExecuteReader()

        If reader.Read Then

            AutoCount = CInt(reader("MAX").ToString) + 1
        Else
            AutoCount = 0
        End If
        connect.Close()

        SqlString = "INSERT INTO OrderDispartDetail (  Orderdpdetail_ID, Orderdp_ID,Orderdpdetail_NO, Car_ID, Oil_ID, " & _
        " Orderdpdetail_use, Orderdpdetail_distance, Orderdpdetail_oiluse, " & _
        " Orderdpdetail_price,Emp_ID ) values " & _
                    " (" & AutoCount & ",'" & txtJobID.Text & "','1','" & Microsoft.VisualBasic.Left(cboCar1.Text, 5) & " ','" & Microsoft.VisualBasic.Left(txtOilName1.Text, 5) & " ', " & _
                    " " & Convert.ToDouble(txtTotalCar1.Text) & "," & Convert.ToDouble(txtDistanceKiloTotal1.Text) & "," & _
                    " " & Convert.ToDouble(txtOilTotal1.Text) & "," & Convert.ToDouble(txtPriceTotalCar1.Text) & ",'" & lblEmpName.Text & "')"
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        Sqlcom.ExecuteNonQuery()

        connect.Close()

    End Sub
    Private Sub SaveCar2()

        AutoCount = AutoCount + 1
        SqlString = "INSERT INTO OrderDispartDetail (  Orderdpdetail_ID, Orderdp_ID,Orderdpdetail_NO, Car_ID, Oil_ID, " & _
        " Orderdpdetail_use, Orderdpdetail_distance, Orderdpdetail_oiluse, " & _
        " Orderdpdetail_price,Emp_ID ) values " & _
                    " (" & AutoCount & ",'" & txtJobID.Text & "','2','" & Microsoft.VisualBasic.Left(cboCar2.Text, 5) & " ','" & Microsoft.VisualBasic.Left(txtOilName2.Text, 5) & " ', " & _
                    " " & Convert.ToDouble(txtTotalCar2.Text) & "," & Convert.ToDouble(txtDistanceKiloTotal2.Text) & "," & _
                    " " & Convert.ToDouble(txtOilTotal2.Text) & "," & Convert.ToDouble(txtPriceTotalCar2.Text) & ",'" & lblEmpName.Text & "')"
        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        Sqlcom.ExecuteNonQuery()
        connect.Close()

    End Sub


    Private Sub txtSearchjob_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearchjob.TextChanged
        If txtSearchjob.Text.Length > 1 Then
            LoadBill()
        End If
    End Sub

    'Private Sub lstBill_ItemSelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.ListViewItemSelectionChangedEventArgs) Handles lstBill.ItemSelectionChanged
    '    clsvariable.tmpBill = lstBill.Items.Item(e.Item.Index).SubItems(1).Text
    'End Sub

    Private Sub LoadBillHead()

        SqlString = "SELECT     OrderDP_ID, Date_Order, Status_ID, Status_Type, Cus_ID, Cus_Fname, Cus_Lname, Cus_Company, Cus_Tel, Soil_ID, Soil_Name, Date_Start, Date_End, " & _
        " Area_Distance,Space_Dispart, Sum_Distance, Sum_Oil, Sum_Price " & _
        " FROM  dbo.V_Dispart Where OrderDP_ID   = '" & clsvariable.tmpBill & "'"

        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()

        Dim sqlAdp As SqlDataAdapter = New SqlDataAdapter(Sqlcom)
        Dim ds As New DataSet()
        sqlAdp.Fill(ds, "OrderCH")

        dgvOrderDP.AutoGenerateColumns = False
        dgvOrderDP.DataSource = ds
        dgvOrderDP.DataMember = "OrderCH"
        connect.Close()

   
    End Sub


    Private Sub CancelBill()

        SqlString = "UPDATE  OrderDispart  SET OrderDP_Cancel = 1 " & _
    " WHERE OrderDP_ID = '" & txtJobID.Text & "' "

        Sqlcmd = New SqlCommand(SqlString, connect)
        connect.Open()
        Sqlcmd.ExecuteNonQuery()
        connect.Close()

        SqlString = "UPDATE  OrderDispartDetail  SET Orderdpdetail_cancel = 1 " & _
   " WHERE OrderDP_ID = '" & txtJobID.Text & "'AND Orderdpdetail_NO = '1' "

        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        Sqlcom.ExecuteNonQuery()
        connect.Close()

        SqlString = "UPDATE  OrderDispartDetail  SET Orderdpdetail_cancel = 1 " & _
    " WHERE OrderDP_ID = '" & txtJobID.Text & "'AND Orderdpdetail_NO = '2' "

        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        Sqlcom.ExecuteNonQuery()
        connect.Close()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        CancelBill()
        LoadBill()
    End Sub

    Private Sub repair_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnrepair.Click
        repairBill()
        LoadBill()
    End Sub
    Private Sub repairBill()
        ' ตรวจสอบเงื่อนไข ของรหัสงาน
        If (txtJobID.Text = "") Then
            MsgBox("ไม่มีรหัสงาน กรุณาระบุรหัสงานด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If
        ' ตรวจสอบเงื่อนไขสถานะงาน
        If (txtStatus.Text = "") Then
            MsgBox("ไม่มีสถานะงาน กรุณาระบุสถานะงานด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If
        ' ตรวจสอบค่าระยะทาง รวม ว่ามีหรือไม่
        If txtDistanceKiloTotal.Text = "0.00" Or txtDistanceKiloTotal.Text = "" Then
            MsgBox("ค่าระยะทางไม่ถูกต้อง หรือยังไม่ระบุ กรุณาตรวจสอบด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If
        ' ตรวจสอบค่าการใช้น้ำมัน รวม ว่ามีหรือไม่
        If txtOilTotal.Text = "0.00" Or txtOilTotal.Text = "" Then
            MsgBox("ค่าการใช้น้ำมันไม่ถูกต้อง หรือยังไม่ระบุ กรุณาตรวจสอบด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If
        ' ตรวจสอบค่าราคา รวม ว่ามีหรือไม่
        If txtPriceTotal.Text = "0.00" Or txtPriceTotal.Text = "" Then
            MsgBox("ค่าราคารวมไม่ถูกต้อง หรือยังไม่ระบุ กรุณาตรวจสอบด้วยครับ", MsgBoxStyle.Critical)
            Exit Sub
        End If

        Dim S0 As String
        S0 = Microsoft.VisualBasic.Left(txtStatus.Text, 2) = "S0"    'ตรวจสอบ สถานะการทำงาน 


        ' มาจากการเปิดต้องการแก้ไข 
        SqlString = "UPDATE  OrderDispart   SET Cus_ID = '" & txtCusID.Text & " ', " & _
            " Status_ID = '" & Microsoft.VisualBasic.Left(txtStatus.Text, 2) & " ', Date_Start =  '" & ConvertDate(DateTimePicker2.Text) & " ' ," & _
            "Date_end =  '" & ConvertDate(DateTimePicker3.Text) & " ',Soil_ID = '" & Microsoft.VisualBasic.Left(cboSoil1.Text, 5) & "', " & _
            " Area_Distance =" & Convert.ToDouble(txtDistanceKilo.Text) & " ,Space_Dispart = '" & txtPlace.Text & " '," & _
            " Sum_Distance =" & Convert.ToDouble(txtDistanceKiloTotal.Text) & " , Sum_Oil =" & Convert.ToDouble(txtOilTotal.Text) & " ,Sum_Price =" & Convert.ToDouble(txtPriceTotal.Text) & "" & _
    " WHERE OrderDP_ID = '" & txtJobID.Text & "' "

        Sqlcmd = New SqlCommand(SqlString, connect)
        connect.Open()
        successinsert = Sqlcmd.ExecuteNonQuery()
        connect.Close()

        If cboCar1.SelectedIndex > -1 Then

            UpdateCar1()

        End If
        If cboCar2.SelectedIndex > -1 Then

            UpdateCar2()

        End If


    End Sub
    Private Sub UpdateCar1()

        SqlString = "UPDATE  OrderDispartDetail  SET Car_ID = '" & Microsoft.VisualBasic.Left(cboCar1.Text, 5) & " ', " & _
             " Oil_ID = '" & Microsoft.VisualBasic.Left(txtOilName1.Text, 5) & " '," & _
             "  Orderdpdetail_use =" & Convert.ToDouble(txtTotalCar1.Text) & " ," & _
             "  Orderdpdetail_distance =" & Convert.ToDouble(txtDistanceKiloTotal1.Text) & " , Orderdpdetail_oiluse =" & Convert.ToDouble(txtOilTotal1.Text) & " ,Orderdpdetail_price =" & Convert.ToDouble(txtPriceTotalCar1.Text) & "" & _
         " WHERE OrderDP_ID = '" & txtJobID.Text & "'AND Orderdpdetail_NO = '1' "

        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        Sqlcom.ExecuteNonQuery()
        connect.Close()

    End Sub
    Private Sub UpdateCar2()
        Dim CSqlCom As Integer
        SqlString = "UPDATE  OrderDispartDetail  SET Car_ID = '" & Microsoft.VisualBasic.Left(cboCar2.Text, 5) & " ', " & _
             " Oil_ID = '" & Microsoft.VisualBasic.Left(txtOilName2.Text, 5) & " '," & _
             "  Orderdpdetail_use =" & Convert.ToDouble(txtTotalCar2.Text) & " ," & _
             "  Orderdpdetail_distance =" & Convert.ToDouble(txtDistanceKiloTotal2.Text) & " , Orderdpdetail_oiluse =" & Convert.ToDouble(txtOilTotal2.Text) & " ,Orderdpdetail_price =" & Convert.ToDouble(txtPriceTotalCar2.Text) & "" & _
         " WHERE OrderDP_ID = '" & txtJobID.Text & "'AND Orderdpdetail_NO = '2' "

        Sqlcom = New SqlCommand(SqlString, connect)
        connect.Open()
        CSqlCom = Sqlcom.ExecuteNonQuery()
        connect.Close()

    End Sub


    Private Sub dgvOrderDP_RowHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvOrderDP.RowHeaderMouseClick

 


        Dim connect As SqlConnection = getconnect()
        'คลิกที่ Datagridview

        connect.Open()
        Dim OrderDP_ID As String = dgvOrderDP.Rows.Item(e.RowIndex).Cells(0).Value.ToString() 'เมือคลิกตรง DataGridview จะเอาข้อมูลใน Cell ที่ 0 มาเก็บค่าใน DonorID
        Dim sqlString2 As String = "SELECT     Orderdpdetail_ID, Orderdp_ID, Orderdpdetail_NO, Car_ID, " & _
                        " Car_Type, Oilburn_Rate, Car_Rate, Oil_ID, Oi_IDl, Oil_Name, Oil_Price, Orderdpdetail_use, " & _
                       "  Orderdpdetail_distance, Orderdpdetail_oiluse, Orderdpdetail_price, Emp_ID " & _
                " FROM dbo.V_DispartDetail  Where Orderdp_ID   = '" + OrderDP_ID + "'"

        '"Select*from dbo.V_CharterDetail where OrderCH_ID = '" + OrderCH_ID + "'"

        Sqlcom = New SqlCommand(sqlString2, connect)

        reader = Sqlcom.ExecuteReader()
        While reader.Read
            If reader("Orderdpdetail_NO").ToString = 1 Then
                cboCar1.Text = reader("Car_ID").ToString & ":" & reader("Car_Type").ToString
                txtTruck1.Text = Format(Convert.ToDouble(reader("Car_Rate").ToString), "##,###.00")
                txtOilName1.Text = reader("Oil_ID").ToString & ":" & reader("Oil_Name").ToString
                txtOilBurnRate1.Text = reader("Oilburn_Rate").ToString
                txtTotalCar1.Text = CInt(reader("Orderdpdetail_use").ToString)
                txtTotalTruck1.Text = Format(Convert.ToDouble(txtTruck1.Text * txtTotalCar1.Text), "##,###.00")
                txtDistanceKiloTotal1.Text = Format(Convert.ToDouble(reader("Orderdpdetail_distance").ToString), "##,###.00")
                txtOilTotal1.Text = Format(Convert.ToDouble(reader("Orderdpdetail_oiluse").ToString), "##,###.00")
                txtPriceTotalCar1.Text = Format(Convert.ToDouble(reader("Orderdpdetail_price").ToString), "##,###.00")
                txtDistanceKiloTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtDistanceKiloTotal1.Text) + Convert.ToDouble(txtDistanceKiloTotal2.Text)), "##,###.00")
                txtOilTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtOilTotal1.Text) + Convert.ToDouble(txtOilTotal2.Text)), "##,###.00")
                txtPriceTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtPriceTotalCar1.Text) + Convert.ToDouble(txtPriceTotalCar2.Text)), "##,###.00")


            Else

                cboCar2.Text = reader("Car_ID").ToString & ":" & reader("Car_Type").ToString
                txtTruck2.Text = Format(Convert.ToDouble(reader("Car_Rate").ToString), "##,###.00")
                txtOilName2.Text = reader("Oil_ID").ToString & ":" & reader("Oil_Name").ToString
                txtOilBurnRate2.Text = reader("Oilburn_Rate").ToString
                txtTotalCar2.Text = Convert.ToInt64(reader("Orderdpdetail_use"))
                txtTotalTruck2.Text = Format(Convert.ToDouble(txtTruck2.Text * txtTotalCar2.Text), "##,###.00")
                txtDistanceKiloTotal2.Text = Format(Convert.ToDouble(reader("Orderdpdetail_distance").ToString), "##,###.00")
                txtOilTotal2.Text = Format(Convert.ToDouble(reader("Orderdpdetail_oiluse").ToString), "##,###.00")
                txtPriceTotalCar2.Text = Format(Convert.ToDouble(reader("Orderdpdetail_price").ToString), "##,###.00")
                txtDistanceKiloTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtDistanceKiloTotal1.Text) + Convert.ToDouble(txtDistanceKiloTotal2.Text)), "##,###.00")
                txtOilTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtOilTotal1.Text) + Convert.ToDouble(txtOilTotal2.Text)), "##,###.00")
                txtPriceTotal.Text = Format(Convert.ToDouble(Convert.ToDouble(txtPriceTotalCar1.Text) + Convert.ToDouble(txtPriceTotalCar2.Text)), "##,###.00")


            End If

        End While
        reader.Close()


        Dim OrderDP2_ID As String = dgvOrderDP.Rows.Item(e.RowIndex).Cells(0).Value.ToString()
        SqlString = "SELECT     OrderDP_ID, Date_Order, Status_ID, Status_Type, Cus_ID, Cus_Fname, Cus_Lname, Cus_Company, Cus_Tel, Soil_ID, Soil_Name, Date_Start, Date_End, " & _
        " Area_Distance,Space_Dispart, Sum_Distance, Sum_Oil, Sum_Price " & _
        " FROM  dbo.V_Dispart Where OrderDP_ID   = '" + OrderDP2_ID + "'"

        Sqlcom = New SqlCommand(SqlString, connect)

        reader = Sqlcom.ExecuteReader()

        While reader.Read
            txtJobID.Text = reader("OrderDP_ID").ToString
            txtStatus.Text = reader("Status_ID").ToString & ":" & reader("Status_Type").ToString
            DateTimePicker2.Text = reader("Date_Start").ToString
            DateTimePicker3.Text = reader("Date_End").ToString
            txtCusID.Text = reader("Cus_ID").ToString
            txtCusFname.Text = reader("Cus_Fname").ToString & "    " & reader("Cus_Lname").ToString
            txtCompany.Text = reader("Cus_Company").ToString
            txtTel.Text = reader("Cus_Tel").ToString
            cboSoil1.Text = reader("Soil_ID").ToString & ":" & reader("Soil_Name").ToString
            txtDistanceKilo.Text = Format(Convert.ToDouble(reader("Area_Distance").ToString), "##,###.00")
            txtPlace.Text = reader("Space_Dispart").ToString
            txtDistanceKiloTotal.Text = Format(Convert.ToDouble(reader("Sum_Distance").ToString), "##,###.00")
            txtOilTotal.Text = Format(Convert.ToDouble(reader("Sum_Oil").ToString), "##,###.00")
            txtPriceTotal.Text = Format(Convert.ToDouble(reader("Sum_Price").ToString), "##,###.00")


        End While
        reader.Close()
        connect.Close()
    End Sub

    Private Sub btnNewCus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewCus.Click
        SHOWFORM(frmCustomer)
    End Sub
End Class