Empty Dataset

[T2T2]

Within the VS editor I used the Server Explorer to create a SQL Server 2000 connection.
I then incorporated the connection into the web form by dragging a table. This created a SqlConnection control and a SqlAdapter control within the form.
I then clicked on Data menu and selected Generate Dataset.

Subsequently I created a form that counted row values for a particular column and reported this.

It worked ok.

As part of my learning I wanted to program this rather than set it up in the editor.
I want to know how to set up and run sql within a project, so that it can be as flexible as possible.

Below I have the Form Load code I have written to achieve the same result as above, except it appears my Dataset is not loaded with data.
I cannot see what I am doing wrong.
If anyone can help it will be appreciated.

   

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

        Dim myConnection As New SqlConnection("server=theotosh;database=goldmine;User ID=sa; Password=; Trusted_Connection=false")
        Const strSQL As String = "select * from conthist"
        Dim myCommand As New SqlCommand(strSQL, myConnection)

        'Fill the DataSet and close the connection...
        'myConnection.Open()
        Dim myDataAdapter As New SqlDataAdapter(myCommand)
        Dim myDataSet As New DataSet
        myDataAdapter.Fill(myDataSet)
        myConnection.Close()

        Dim myTable As DataTable = myDataSet.Tables.Add("srectype")

        Dim row As DataRow
        Dim call_cnt, app_cnt, other_cnt, tot_cnt As Integer
        Dim srectype As String

        tot_cnt = 9

        For Each row In myTable.Rows

            Console.WriteLine(srectype)

            lboSRecType.Items.Add(srectype)

            tot_cnt = tot_cnt + 1

            srectype = row.Item("srectype")

            Select Case srectype
                Case "C"
                    call_cnt = call_cnt + 1
                Case "A"
                    app_cnt = app_cnt + 1
                Case Else
                    other_cnt = other_cnt + 1
            End Select
            ''If srectype = "C" Then
            ''call_cnt = call_cnt + 1
            ''End If

        Next

        lblCallCnt.Text = call_cnt
        lblAppCnt.Text = app_cnt
        lblOtherCnt.Text = other_cnt
        lblTotalCnt.Text = tot_cnt



    End Sub

If I wanted to write a second Sql query for another object on the form, would I be creating a new adapter with sql code and adding it to the same dataset?

Thanks