VB.net chart controls and sql server

Question

Problem: I am currently using a vb application(Visual Studio 2012) to query my database (SQL Server 2012) and display the information using the chart control feature in vb.net

Additional information: I want the x axis to display location name and y axis to display the count of the location I have complied the code and cannot seem to find the error in the line of code. Please find the code below!

Code:

My code is as follows:

    Dim cnn3 As New SqlConnection
    Dim cmd3 As New SqlCommand

    cnn3.ConnectionString = ("Data Source=SARAHSCOMPUTER;Initial Catalog=FYPDB1;Integrated Security=True")
    cmd3.Connection = cnn3

    Dim tblFields As String = "SELECT * from tblTagInfo"
    Dim oData As New SqlDataAdapter(tblFields, cnn3)
    Dim ds As New DataSet
    Dim oCmd As New SqlCommand(tblFields, cnn3)


    cnn3.Open()
    oData.Fill(ds, "tblTagInfo")
    cnn3.Close()

    Chart1.DataSource = ds.Tables("tblTagInfo")
    Dim Series1 As Series = Chart1.Series("Series1")
    Series1.Name = "Location"
    Chart1.Series(Series1.Name).XValueMember = "Location"
    Chart1.Series(Series1.Name).YValueMembers = "SELECT COUNT (Area) FROM tblLocation group by Location"

    Chart1.Size = New System.Drawing.Size(780, 350)
End Sub

"*cannot seem to find the error...*" what is the **error** you getting? — huMpty duMpty, Feb 28 '14 at 11:39
You are passing strings "Location" and "SELECT COUNT (Area) FROM tblLocation group by Location" rather than the actual values in the dataset — twoleggedhorse, Feb 28 '14 at 11:40
@twoleggedhorse i understand what you are saying how would you go about doing this? — user3284316, Feb 28 '14 at 13:14
@huMptyduMpty there is no error just the chart is not working it compiles the code no problem — user3284316, Feb 28 '14 at 13:18
Check out this previous question http://stackoverflow.com/questions/10712371/showing-all-values-in-chart-control-xaxis — twoleggedhorse, Feb 28 '14 at 14:01

score 2 · Accepted Answer · edited Feb 28 '14 at 15:36

Please try this code:

Dim cnn3 As New SqlConnection
Dim cmd3 As New SqlCommand

cnn3.ConnectionString = ("Data Source=SARAHSCOMPUTER;Initial Catalog=FYPDB1;Integrated Security=True")
cmd3.Connection = cnn3

Dim tblFields As String = "SELECT COUNT(Location) AS LocationCount, Location AS LocationName FROM tblTagInfo group by Location"
Dim oData As New SqlDataAdapter(tblFields, cnn3)
Dim ds As New DataSet
Dim oCmd As New SqlCommand(tblFields, cnn3)


cnn3.Open()
oData.Fill(ds, "tblTagInfo")
cnn3.Close()

Chart1.DataSource = ds.Tables("tblTagInfo")
Dim Series1 As Series = Chart1.Series("Series1")
Series1.Name = "Sales"
Chart1.Series(Series1.Name).XValueMember = "LocationName"
Chart1.Series(Series1.Name).YValueMembers = "LocationCount"

Chart1.Size = New System.Drawing.Size(780, 350)

thank you so much this worked for me! I can now see my error! — user3284316, Feb 28 '14 at 15:37

VB.net chart controls and sql server

1 Answers1