how to load different data into the same dataset

[westsiderailway]

Hi There,
here is a form from my VS2013C Project

As you can see, there are 9 labels that need filling.

My Question is, can I load the dataset with 9 different select statements from the same Table, and then fill the 9 labels with the correct data.?

("select MIN(price_a_liter) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", CON)

("select MIN(total_liters) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", CON2)

("select MIN(total_dollars) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", CON3)

The above is just 3 of the statements that I need to do....

  Dim M12S As String = Places.Item(0)
        Dim M12F As String = Places.Item(1)

        'Get SQL MIN (Price@Liter)
        'Create a NEW SQL Connection String
        Dim CON As New SqlConnection
        CON.ConnectionString = "Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=Petrol_Costs;Integrated Security=True;"
        'Create a NEW SQLDB DataAdapter
        Dim SQLDAdP As SqlDataAdapter = New SqlDataAdapter("select MIN(price_a_liter) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", CON)
        CON.Close()

        'Create a NEW DataSet
        Dim DS As New DataSet
        Dim dt As New DataTable
        'Fill Data Adapter
        SQLDAdP.Fill(DS, "load")

        ' Fill Data Tables
        dt = DS.Tables("load")

The above code , is how I am doing it at the moment,if I have to do it this way, it means 9 sets of code just like this. The only diffenent being the "AVG","MIN","MAX"

Thanks for reading.

[Santanu.Das]

You can do it anywhere and anytime in your project, where you assume that you must need to do this. For this you can use 9 sets of data objects (dataadeptor, dataset, datatable) or if you want to use a single set every time you must have to dispose or close them before next use.

[westsiderailway]

From what I have been reading, you can not have a Global Connection. You must open & Close everytime.

   Using con As New SqlConnection("constr1")
            Using AD As SqlDataAdapter = New SqlDataAdapter("select MAX(total_dollars) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                con.Open()
                AD.Fill(ds.Tables("Table3"))
            End Using
        End Using

With this I belive that you do not havew to worry about closing the connection, as it does itself.?

[Santanu.Das]

You can do the all 9 queries in the connection string using statement.
It should be like

Using con As New SqlConnection("constr1")
    con.Open()

    Using AD As SqlDataAdapter = New SqlDataAdapter("select MAX(total_dollars) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
        AD.Fill(ds.Tables("Table3"))

    End Using

    'Same for AD2
    'Same for AD3
    '............
    '............
    'Same for AD9

    con.Close()

End Using

Hope it can help you.

[westsiderailway]

Thanks for that....

saved me 40 lines of code. :-)