Query within a Query

[winsonlee]

The first query works. But I wonder how can I make another query getting the sum ? ?

            cmd.Connection = conn
            conn.Open()


            Dim ds As New DataSet
            Dim ds2 As New DataSet

            cmd.CommandText = "SELECT PadFile.date, PadFile.apn, PadFile.store, PadFile.soh, PadFile.ordered, PadFile.sales_replen, Product.packsize FROM PadFile INNER JOIN Product ON PadFile.apn = Product.apn WHERE date = '071231'"
            da.Fill(ds, "data")

            conn.Close()

            For Each dt As DataTable In ds.Tables
                For Each dr As DataRow In dt.Rows

                    cmd.Connection = conn
                    conn.Open()

                    cmd.CommandText = "SELECT sum(ordered) as ordered, sum(delivered) as delivered FROM EDIFile WHERE date = @date AND apn = @apn AND store = @store"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@date", "080204")
                    cmd.Parameters.AddWithValue("@apn", Trim(dr.Item("apn")))
                    cmd.Parameters.AddWithValue("@store", Trim(dr.Item("store")))
                    da2.Fill(ds2, "data")
                    conn.Close()

                    For Each dt2 As DataTable In ds2.Tables
                        For Each dr2 As DataRow In dt2.Rows
                            MsgBox(Trim(dr2.Item("ordered")))


                        Next dr2
                    Next dt2
                Next dr
            Next dt
            MsgBox("update completed")

 

[jshultz]

Well, what problem are you having...? Also, you should not open the connection before you do da.Fill(), the dataadapter will open and close the connection itself, you do not need to be doing this.

 

[winsonlee]

the code works fine without this line of code where all the item(1) are loaded into the text box.

If ordered.Item(1) <> ordered.Item(0) Then

end if

When i add in the if statement to compare two item loaded from database, it gives me Error loading information from database.

Do u know why is this happening ?

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Try

            cmd.Connection = conn
            conn.Open()


            Dim ds As New DataSet
            Dim ds2 As New DataSet
            Dim ordered As SqlDataReader
            cmd.CommandText = "SELECT PadFile.date, PadFile.apn, PadFile.store, PadFile.soh, PadFile.ordered, PadFile.sales_replen, Product.packsize FROM PadFile INNER JOIN Product ON PadFile.apn = Product.apn WHERE date = '080225' AND PADFile.apn = '9311644024415' ORDER BY PadFile.apn, PadFile.store"
            da.Fill(ds, "data")


            For Each dt As DataTable In ds.Tables
                For Each dr As DataRow In dt.Rows


                    cmd.CommandText = "SELECT sum(ordered), sum(delivered) FROM EDIFile WHERE (date = @date or date = @date2 or date = @date3) AND apn = @apn AND store = @store"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@date", "080304")
                    cmd.Parameters.AddWithValue("@date2", "080311")
                    cmd.Parameters.AddWithValue("@date3", "080312")
                    cmd.Parameters.AddWithValue("@apn", Trim(dr.Item("apn")))
                    cmd.Parameters.AddWithValue("@store", Trim(dr.Item("store")))
                    ordered = cmd.ExecuteReader()
                    ordered.Read()
                    If ordered.Item(1) <> ordered.Item(0) Then
                        TextBox1.Text = TextBox1.Text & Trim(dr.Item("apn")) & " " & Trim(dr.Item("store")) & " " & ordered.Item(1) & vbCrLf
                    End If
                    ordered.Close()


                Next dr
            Next dt
            conn.Close()
            MsgBox("update completed")

        Catch ex As Exception
            MsgBox("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error")
        End Try


    End Sub

 

[jwh]

I'm not sure on your second question, so this is for your first question.

You can nest SQL Statements.

Say I have 2 Tables: Customers and Orders

If I want a list of customers, along with the SUM of all their orders (obviously you can play with the WHERE clause to pull just about any info your want)

SELECT Customers.CustomerName,
       (Select Sum(OrderTotal) as Expr1 from Orders where Order.CustomerID=Customer.CustomerID) as TotalOrders
FROM Customers

This query would return 2 columns; The Customer Name, and the total of orders for that customer.

I hope this helps.

 

[winsonlee]

            ordered = cmd2.ExecuteReader()

            Do While ordered.Read()

                If (ordered.Item(0) <> "" And ordered.Item(1) <> "") Then <- error on this line saying that conversion from string "" to type 'Double' is not valid.

                    newqty = ordered.Item(0) - ordered.Item(1)
                End If
                TextBox1.Text = TextBox1.Text & " " & Trim(pad.Item(1)) & " " & Trim(pad.Item(2)) & " " & vbCrLf
            Loop

After executing the cmd2.ExecuteREader(), although it did not return any result, it still goes through the do while loop. Anyone has any idea why is this happening ?

 

[cjard]

Hi winson..

Youre going about your data access in the wrong way. Take a read of the DW2 link in my signature, section "Creating a Simple Data App"


There are so many other problems with your code, that I dont actually have time to list them all.. But basically put, I'd dump the lot (sorry) and write a single SQL that does all that stuff... Re-Setting up a parameterized query for every row in a datatable is wasteful and silly.. like restarting windows every time you change a text box