Invalid attempt to read when no data is present.

[3587ch]

Can anyone tell me what I'm doing wrong here?

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataSet
Imports InfoSoftGlobal
Partial Class MultiSeries
Inherits System.Web.UI.Page
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Generate chart in Literal Control
FCLiteral.Text = CreateChart()
End Sub

Public Function CreateChart() As String

'In this example, we plot a multi series chart. Three columns - first one for data label (product)
'and the next two for data values. The first data value column would store sales information
'for current year and the second one for previous year.

'Let's store the name of products.
Dim arrData(7, 3) As String
'Store Name of Products
arrData(0, 1) = "Prod 1"
arrData(1, 1) = "Prod 2"
arrData(2, 1) = "Prod 3"
arrData(3, 1) = "Prod 4"
arrData(4, 1) = "Prod 5"
arrData(5, 1) = "Prod 6"
arrData(6, 1) = "Prod 7"

'Database Objects - Initialization
'Current Year
Dim cmd As SqlCommand
Dim dtrFC As SqlDataReader
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("funddataSQL2ConnectionString").ConnectionString
cmd = New SqlCommand
cmd.CommandText = "ProdTypesYearly"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = New SqlConnection(ConnectStr)
cmd.Parameters.Add("@YearPassed", SqlDbType.Int).Value = 2009
cmd.Connection.Open()

'Previous year
'Database Objects - Initialization
Dim cmdPrev As SqlCommand
Dim dtrFCPrev As SqlDataReader
cmdPrev = New SqlCommand
cmdPrev.CommandText = "ProdTypesYearly"
cmdPrev.CommandType = CommandType.StoredProcedure
cmdPrev.Connection = New SqlConnection(ConnectStr)
cmdPrev.Parameters.Add("@YearPassed", SqlDbType.Int).Value = 2008
cmdPrev.Connection.Open()



Try
'Now, we need to convert this data into multi-series XML.
'We convert using string concatenation.
'strXML - Stores the entire XML
'strCategories - Stores XML for the <categories> and child <category> elements
'strDataCurr - Stores XML for current year's sales
'strDataPrev - Stores XML for previous year's sales
Dim strXML As String, strCategories As String, strDataCurr As String, strDataPrev As String, i As Integer

'Initialize <graph> element
strXML = "<graph caption='Donations by Type' numberPrefix='$' decimalPrecision='0' >"

'Initialize <categories> element - necessary to generate a multi-series chart
strCategories = "<categories>"

'Initiate <dataset> elements
strDataCurr = "<dataset seriesName='Current Year' color='AFD8F8'>"
strDataPrev = "<dataset seriesName='Previous Year' color='F6BD0F'>"

'Iterate through the data
For i = 0 To UBound(arrData) - 1

dtrFC = cmd.ExecuteReader()

dtrFCPrev = cmdPrev.ExecuteReader()

'Append <category name='...' /> to strCategories
strCategories = strCategories & "<category name='" & arrData(i, 1) & "' />"
'Add <set value='...' /> to both the datasets
strDataCurr = strDataCurr & "<set value='" & dtrFC.Item("Total").ToString() & "' />"
strDataPrev = strDataPrev & "<set value='" & dtrFCPrev.Item("Total").ToString() & "' />"
Next

'Close <categories> element
strCategories = strCategories & "</categories>"

'Close <dataset> elements
strDataCurr = strDataCurr & "</dataset>"
strDataPrev = strDataPrev & "</dataset>"

'Assemble the entire XML now
strXML = strXML & strCategories & strDataCurr & strDataPrev & "</graph>"

'Create the chart - MS Column 3D Chart with data contained in strXML
Return FusionCharts.RenderChartHTML("Charts/FCF_MSColumn3D.swf", "", strXML, "productSales", "600", "300", False) ', False)
Catch ex As Exception
Return ex.Message
FCLiteral.Text = ex.Message
Finally
cmd.Connection.Close()
cmdPrev.Connection.Close()
End Try
End Function


End Class

 

[jmeckley]

I would assume 2 things.
1. there is either no data returned from the query.
2. you are returning an error message on exception but the function expects you to return a html chart.

I would change up your code to do this

var DataSet data = new DataSet();
data.Tables.Add(new DataTable())
data.Tables.Add(new DataTable())
using(var connection = new SqlConnection())
using(var command1 = connection.CreateCommand())
using(var command2 = connection.CreateCommand())
{
   connection.Open();

   command1.CommandText = ...
   //set up parameters to

   command2.CommandText = ...
   //set up parameters to

   data.Tables[0].Load(command1.ExecuteReader());
   data.Tables[1].Load(command2.ExecuteReader());
}
return data;

then transform the dataset into the proper xml. notice I don't handle the exception explicitly.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190