Subquery in VB

What do you want to do with the output of that query?

Bob

I want to make a Report.

What kind of report?

Bob

A VB Report the extension is rdlc.

Have you seen anything like this?

One to Many Reports with VS.NET 2005 (2.0) Report Designer
http://www.codeproject.com/sqlrs/1tomanyreports_vsnet2005.asp

Bob

Thanks Bob for your answer, but i'm interested in the query part more than the report one.
In Access I can use a query as a FROM part for another query. Is that the point that I don't how to do in VB. Maybe is so easy but I don't kow. I write the code that i'm woring on:

Imports System.Data
Imports System.Data.OleDb

Public Class Form1

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Declare variables and objects
        Dim strConnectionString As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & "C:\EMP0243.Mdb;"
        Dim objConnection As New OleDbConnection(strConnectionString)
        Dim strSQL1 As String = "SELECT Table1.Ref, Sum(Table1.Cost) AS SumOfCost " & _
"FROM Table1 " & _
"GROUP BY Table1.Ref; "

Dim strSQL2 as String = "SELECT Table2.Ref, Sum(Table2.Cost2) AS SumOfCost2 " & _
"FROM Table2 " & _
"GROUP BY Table2.Ref;"

Dim strSQLUNION as String ="SELECT Query1.Ref, Query1.SumOfCost, Query2.SumOfCost2  " & _
"FROM Query1 LEFT JOIN Query2 ON Query1.Ref = Query2.ref  " & _
"UNION SELECT Query2.ref, Query1.SumOfCost, Query2.SumOfCost2 " & _
"FROM Query1 RIGHT JOIN Query2 ON Query1.Ref = Query2.ref;"

        Dim objCommand As New OleDbCommand(strSQLUNION, objConnection)
        Dim objReader As OleDbDataReader

        Try
            'Open the database connection
            objConnection.Open()
            'Initialize the DataReader object
            objReader = objCommand.ExecuteReader()
            While objReader.Read
                MessageBox.Show(objReader.Item(0) & " " & objReader.Item(1) & " " & objReader.Item(2))
            End While
        Catch OleDbExceptionErr As OleDbException
            MsgBox(OleDbExceptionErr.Message)
            'Write the exception
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Write the exception
            MsgBox(InvalidOperationExceptionErr.Message)
            Debug.WriteLine(InvalidOperationExceptionErr.Message)
        End Try
        'Close the DataReader object
        objReader.Close()

        'Close the database connection
        objConnection.Close()

        'Clean up
        objReader.Dispose()
        objReader = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing
    End Sub
End Class

That looks good to me, but I would use some of the 2.0 syntax improvements (like Using blocks, generics, and app.config files), and remove the Try/Catch if you aren't going to do anything with the message, and put in global exception handling.

Here is the suggested syntax changes:

Imports System.Collections.Generic
Imports System.Configuration

...

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim list As List(Of String) = GetSums()
        Dim values As String() = list.ToArray()
        Dim message As String = String.Join(vbCrLf, values)
        MessageBox.Show(message)
    End Sub

    Private Function GetSums() As List(Of String)
        ' Get the connection string from the app.config file <connectionStrings> section.
        Dim strConnectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

        Using objConnection As New OleDbConnection(strConnectionString)

            Dim strSQLUNION As String = "SELECT Query1.Ref, Query1.SumOfCost, Query2.SumOfCost2  " & _
                "FROM Query1 LEFT JOIN Query2 ON Query1.Ref = Query2.ref  " & _
                "UNION SELECT Query2.ref, Query1.SumOfCost, Query2.SumOfCost2 " & _
                "FROM Query1 RIGHT JOIN Query2 ON Query1.Ref = Query2.ref;"

            Using objCommand As New OleDbCommand(strSQLUNION, objConnection)
                'Open the database connection
                objConnection.Open()
                'Initialize the DataReader object
                Dim list As New List(Of String)
                Using objReader As OleDbDataReader = objCommand.ExecuteReader()
                    While objReader.Read()
                        list.Add(objReader("Ref") & " " & objReader("SumOfCost") & " " & objReader("SumOfCost2"))
                    End While
                    Return list
                End Using
            End Using
        End Using
    End Function

Bob

Perfect Bob, now it works.
But this was a sample and in the sample I can modify the MDB and create de querys, but in real I can't modify the MDB and create "Query1" and "Query2".
Can I execute the Query1, Query2 and strSQLUnion all in VB?

Are you talking about 1) creating dynamic queries, 2) combining Query1 and Query2 into a single query, or 3) running 2 queries and taking the results of those 2 and running a third?  

#1 and #2 are possible
#3 may not be possible

Bob

Hey Bob,
I was thinking in the #3. But could you explain the #1 and #2.

Is not posible the #3 one neither in the visual datasource panel?