|  |  | 
            
              | อันนี้ Code ครับติดที่ว่าเมื่อเลือกผิดแล้วไม่ยอมเตือนขึ้นเป็นError แทน 
 Code (VB.NET)
 
 Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)
        Dim conStr As String = ""
        Select Case Extension
            Case ".xls"
                'Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'") _
                .ConnectionString()
                Exit Select
            Case ".xlsx"
                'Excel 07
                conStr = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FilePath & "';Extended Properties= 'Excel 12.0 Xml;HDR=No'") _
                .ConnectionString()
                Exit Select
        End Select
        conStr = String.Format(conStr, FilePath, isHDR)
        Dim connExcel As New OLEDBConnection(conStr)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        Dim dt As New DataTable()
        cmdExcel.Connection = connExcel
        'Get the name of First Sheet
        'connExcel.Open()
        'Dim ExcelTable As DataTable
        'Dim SheetName As String
        'ExcelTable = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing})
        'SheetName = ExcelTable.Rows(0).Item(2).ToString
        'MsgBox(SheetName)
        'SheetName = ExcelTable.Rows(1).Item(2).ToString
        'MsgBox(SheetName)
        'SheetName = ExcelTable.Rows(2).Item(2).ToString
        'MsgBox(SheetName)
        'connExcel.Close()
        'Read Data from First Sheet
        connExcel.Open()
        Dim Con As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)
        Select Case MsgBox("Are you sure ?", MsgBoxStyle.OkOnly, "ADD DIE")
            Case MsgBoxResult.Ok
                If conStr <> "Galley" Then
                    MsgBox("please choose new file")
                    Response.Redirect("priorityfold.aspx")
                    Response.Close()
                    connExcel.Close()
                End If
        End Select
        connExcel.Open()
        Con.Open()
        Dim sqlUserName As String = "SELECT * FROM groupdie"
        cmdExcel.CommandText = "SELECT [" & "Galley$" & "].[f3] as Project, [" & "Galley$" & "].[f4] as Item, [" & "Galley$" & "].[f5]  as PARTNAME, [" & "Galley$" & "].[f10] as ShipWK, [" & "Galley$" & "].[f13] as Planwk, [f41] as Status From [" & "Galley$" & "] WHERE  [" & "Galley$" & "].[f9] = 'Galley PartsFold' OR [" & "Galley$" & "].[f19] IS NOT NULL AND [" & "Galley$" & "].[f5] IS NOT NULL  "
        Dim com As New SqlCommand(sqlUserName, Con)
        Dim dr As DataRow
        Dim ex As String
        ex = "SELECT * FROM [" & "Galley$" & "]"
        Dim conn As New OleDbCommand(ex, connExcel)
        'เลือกไฟล์ผิดจะหา galley ไม่เจอแต่ถ้าเลือกถูกข้อมูลขึ้น ???
        'เตือนเมื่อเลือกไฟล์ผิด()
        'Dim tbn As String
        'tbn = "Galley" ' ชื่อsheet ที่ถูก
        'MsgBox("Start")
        ''MsgBox()
        'Dim msg = "please choose new file"
        'Dim Currentname As String
        'Currentname = Convert.ToString(conn.ExecuteScalar)
        'If Currentname = "tbn" Then
        '    MsgBox(msg)
        ' เพิ่มcolumn
        oda.SelectCommand = cmdExcel
        oda.Fill(dt)
        dt.Columns.Add("die r 1", Type.GetType("System.String"))
        dt.Columns.Add("die v 1", Type.GetType("System.String"))
        dt.Columns.Add("die r 2", Type.GetType("System.String"))
        dt.Columns.Add("die v 2", Type.GetType("System.String"))
        dt.Columns.Add("die r 3", Type.GetType("System.String"))
        dt.Columns.Add("die v 3", Type.GetType("System.String"))
        dt.Columns.Add("die r 4", Type.GetType("System.String"))
        dt.Columns.Add("die v 4", Type.GetType("System.String"))
        dt.Columns.Add("die r 5", Type.GetType("System.String"))
        dt.Columns.Add("die v 5", Type.GetType("System.String"))
        dt.Columns.Add("die r 6", Type.GetType("System.String"))
        dt.Columns.Add("die v 6", Type.GetType("System.String"))
        dt.Columns.Add("die r 7", Type.GetType("System.String"))
        dt.Columns.Add("die v 7", Type.GetType("System.String"))
        dt.Columns.Add("die r 8", Type.GetType("System.String"))
        dt.Columns.Add("die v 8", Type.GetType("System.String"))
        dt.Columns.Add("die r 9", Type.GetType("System.String"))
        dt.Columns.Add("die v 9", Type.GetType("System.String"))
        dt.Columns.Add("die r 10", Type.GetType("System.String"))
        dt.Columns.Add("die v 10", Type.GetType("System.String"))
        dt.Columns.Add("rev", Type.GetType("System.String"))
        dt.Columns.Add("remark", Type.GetType("System.String"))
        'จับคู่ฐานข้อมูล()
        For Each dr In dt.Rows
            com = New SqlCommand("select groupdie.dier1 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 1") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev1 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 1") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier2 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 2") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev2 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 2") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier3 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 3") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev3 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 3") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier4 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 4") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev4 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 4") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier5 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 5") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev5 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 5") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier6 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 6") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev6 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 6") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier7 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 7") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev7 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 7") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier8 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 8") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev8 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 8") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier9 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 9") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev9 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 9") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.dier10 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die r 10") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.diev10 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("die v 10") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.revdie from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("rev") = Convert.ToString(com.ExecuteScalar)
            com = New SqlCommand("select groupdie.remark from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
            dr("remark") = Convert.ToString(com.ExecuteScalar)
        Next
        'การ Grouping die เรียงตาม Plan week
        Dim dra As String
        dra = "SELECT [" & "Galley$" & "].[f3] as Project, [" & "Galley$" & "].[f4] as Item, [" & "Galley$" & "].[f5]  as PARTNAME, [" & "Galley$" & "].[f10] as ShipWK, [" & "Galley$" & "].[f13] as Planwk, [f41] as Status From [" & "Galley$" & "] from [" & "Galley$" & "] ORDER BY(f13) in (select [f3] from [" & "Galley$" & "] group by [f13] having count(f13) >='1')"
        'Dim pri As String = "SELECT [" & "Galley$" & "].[f3] as Project, [" & "Galley$" & "].[f4] as Item, [" & "Galley$" & "].[f5]  as PARTNAME, [" & "Galley$" & "].[f10] as ShipWK, [" & "Galley$" & "].[f13] as Planwk, [f41] as Status From [" & "Galley$" & "] from [" & "Galley$" & "] ORDER BY(f13) in (select [f3] from [" & "Galley$" & "] group by [f13] having count(f13) >='1')"
        connExcel.Close()
        oda.SelectCommand = cmdExcel
        oda.Fill(dt)
        Con.Close()
        ' Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath)
        GridView1.DataSource = dt
        GridView1.DataBind()
        connExcel.Close()
    End Sub
 |  
              | 
                
                  |  |  |  |  
                  |  | 
                      
                        | Date :
                            2015-03-17 13:44:05 | By :
                            plam23233 |  |  |  
                  |  |  |  |  |  |  |