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

[DanielaTm]

you can put all in the same dataset. I would reccomend to do it function of the app logic

[T2T2]

Daniela,

I don't understand, what do you mean by -

"I would reccomend to do it function of the app logic"

[aniskhan]

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?

yes u can use the same dataset and adapter.
Declarations

 Dim conn As New SqlConnection("Server=CHEETAH;Database=NorthWind;Trusted_Connection=True;")

    Dim adapter As SqlDataAdapter
    Dim ds As New DataSet()

used it to fill Categories table to dataset

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        adapter = New SqlDataAdapter("select * FROM Categories", conn)

        ds = New DataSet()

        conn.Open()
        adapter.Fill(ds, "Categories")
        conn.Close()

        Me.DataGridView1.DataSource = ds.Tables("Categories")
    End Sub

used it to fill Employees table to dataset

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        adapter = New SqlDataAdapter("select * FROM Employees", conn)

        ds = New DataSet()

        conn.Open()
        adapter.Fill(ds, "Employees")
        conn.Close()

        Me.DataGridView1.DataSource = ds.Tables("Employees")

    End Sub

When u use to fill data to dataset filled before, previous data clears and adapter fill new data in the dataset.
u can also clear the dataset using the clear method of dataset.

[aniskhan]

        Dim conn As New SqlConnection("Server=CHEETAH;Database=NorthWind;Trusted_Connection=True;")
        Dim adapter As New SqlDataAdapter("select * FROM Employees", conn)
        Dim ds As New DataSet()

        ds = New DataSet()

        conn.Open()
        adapter.Fill(ds, "Employees")
        conn.Close()

        Me.DataGridView1.DataSource = ds.Tables("Employees")

        Dim c As Integer = 0
        For Each r As DataRow In ds.Tables("Employees").Rows
            c += r.Item("EmployeeID")
        Next
 
        Me.Text = "Calculated :: " & c.ToString

u can iterate through the rows in table of dataset, i tried ur code and got the data filled.

[aniskhan]

what are u looking to do through this line

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

to get the table from, dataset to datatable just do this

Dim myTable As DataTable = myDataSet.Tables(0)

[T2T2]

Aniskan,

thanks for your posts.

The system I am working on is a customer relations management package (Goldmine).
The particular srectype code identifies sales rep activities
c - call
a - appointment
m - email
l - letter
...etc

The particular code :
Dim myTable As DataTable = myDataSet.Tables.Add("srectype")
I am trying to load the table column srectype into a datatable in the dataset. I am just starting out so this is an attempt to get things happening between Sql Server 2000 and VB.net.

I guess I don't understand the concept of Dataset construction and usage.

In the old ADO world I was interfacing with Databases throughout my VB programs pulling data via the connection into recordsets.
So as my program logic navigated to different procedures specific select sql statements were run dynamically to extract and insert/update sql was used to input data.

I guess a Dataset is used in the same way as a recordset, you bring in data to the program, process it and then apply transactions to the database.

Need to understand the dataset,adaptor,.... components of the new ADO.net way of doing things.

[aniskhan]

u can have a new datatable from table in dataset like this

   Dim dt As New DataTable

        dt.Columns.Add(ds.Tables(0).Columns(0).ColumnName.ToString, 

ds.Tables(0).Columns(0).DataType)

        For Each r As DataRow In ds.Tables("Employees").Rows
            dt.Rows.Add(r.Item(0))
        Next

        Me.DataGridView1.DataSource = dt

[aniskhan]

u can have add another table into dataset

ds.Tables.Add("mm")
        ds.Tables("mm").Columns.Add(ds.Tables(0).Columns(0).ColumnName.ToString, 

ds.Tables(0).Columns(0).DataType)

        For Each r As DataRow In ds.Tables("Employees").Rows
            ds.Tables("mm").Rows.Add(r.Item(0))
        Next

        Me.DataGridView1.DataSource = ds.Tables("mm")

[aniskhan]

Introduction to DataSets
Using ADO.NET