تعبئة - نسخة قابلة للطباعة

كود :

Dim cmd As New OleDbCommand("CREATE VIEW a AS SELECT MyTable.name, MyTable.Result FROM MyTable WHERE (((MyTable.Result)='yes')) GROUP BY MyTable.name, MyTable.Result", Conn)

        Conn.Open()

        cmd.ExecuteNonQuery()

        Conn.Close()

        '''''''''''''''''''''''''''''''''''''

        cmd.CommandText = "CREATE VIEW b AS SELECT MyTable.name, Count(a.Result) AS Result FROM MyTable LEFT JOIN a ON MyTable.name=a.name GROUP BY MyTable.name"

        Conn.Open()

        cmd.ExecuteNonQuery()

        Conn.Close()

        '''''''''''''''''''''''''''''''''''''

Dim dp As New OleDbDataAdapter("SELECT MyTable.name, Count(MyTable.subject) AS [Total Subject], b.Result,IIf(b.Result = 0 , 0, IIf(b.Result = 1 , 1, IIf(b.Result = 2 , 2, IIf(b.Result = 3 , 3, IIf(b.Result = 4 , 5, IIf(b.Result = 5 , 8, IIf(b.Result > 5 , b.Result, ))))))) As Points FROM MyTable INNER JOIN b ON MyTable.name = b.name GROUP BY MyTable.name, b.Result", Conn)

        Dim ds As New DataSet

        dp.Fill(ds)

        DGV_StatisticsN.DataSource = ds.Tables(0)

        '''''''''''''''''''''''''''''''''''''

        cmd.CommandText = "drop VIEW a, b"

        Conn.Open()

        cmd.ExecuteNonQuery()

        Conn.Close()

كود :

Try

            Conn.Open()

            SQLStr = "CREATE VIEW r AS SELECT A.name, IIF(B.Cnt IS NULL,0,B.Cnt) AS [Total Approved] FROM (SELECT DISTINCT name FROM MyTable) A LEFT JOIN (SELECT name,COUNT(result) AS Cnt FROM MyTable WHERE result='yes' GROUP BY name) B ON A.name = B.name"

            Dim cmd1 As New OleDbCommand(SQLStr, Conn)

            cmd1.ExecuteNonQuery()

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            SQLStr = "SELECT MyTable.name AS [Name], Count(MyTable.subject) AS [Total Subjects], [Total Approved], IIf([Total Approved] = 0, 0, IIf([Total Approved] = 1, '" & TB_S1.Text & "', IIf([Total Approved] = 2, '" & TB_S2.Text & "', IIf([Total Approved] = 3, '" & TB_S3.Text & "', IIf([Total Approved] = 4, '" & TB_S4.Text & "', IIf([Total Approved] = 5, '" & TB_S5.Text & "', IIf([Total Approved] > 5 , [Total Approved], ))))))) As Points FROM MyTable INNER JOIN r ON MyTable.name = r.name GROUP BY MyTable.name, [Total Approved]"

            Dim dp As New OleDbDataAdapter(SQLStr, Conn)

            Dim ds As New DataSet

            dp.Fill(ds)

            DGV_Statistics.DataSource = ds.Tables(0)

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            SQLStr = "drop VIEW r"

            Dim cmd2 As New OleDbCommand(SQLStr, Conn)

            cmd2.ExecuteNonQuery()

            Conn.Close()

        Catch ex As Exception

            Conn.Close()

            MsgBox(ex.Message, MsgBoxStyle.Critical, " Error")

        End Try