Member Avatar for westsiderailway

Hi There,
here is a form from my VS2013C Project
Texting_Form.jpg

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.

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.

Member Avatar for 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.?

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.

Member Avatar for westsiderailway

Thanks for that....

saved me 40 lines of code. :-)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.