query strings, dates and complicated data!

Recommended Posts

Hi there




I am a newbie using visual web designer with vb and i am using data from an access database.




I have a rather complicated query which displays cricket scores grouped by date for a particular player (url query string)




I would like to display bar columns for "balls bowled" and "runs conceded" and "wickets" grouped by the date of the match played (horizontal axis)




I have already got an graph working on the site that i am trying to do this for but i would obviously love to use fusioncharts free to display this information (for the existing graph go here: )




I have followed the examples and have got a little way into making it work for me but i am stuck as i a) dont know how to get the user id from a query string for this and :) i need to group each column by date and it doesnt seem to like that and c) i dont know how to add another column let alone another 2 columns and d) how can i format the date displayed from the database !




Here is what i have done so far, i have hard coded the userid into this code for now just to get it working.




Here is my code from the code behind




Imports DataConnection

Imports InfoSoftGlobal

Partial Class DBExample_BasicDBExample

   Inherits System.Web.UI.Page

   Public Function CreateChart() As String

       'In this example, we show how to connect FusionCharts to a database.

       'For the sake of ease, we've used an Access database which is present in

       '../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each


       'Database Objects - Initialization

       Dim oRs As DbConn, strQuery As String

       'strXML will be used to store the entire XML document generated

       Dim strXML As String

       'Generate the graph element

       strXML = ""

       'Iterate through each factory

       strQuery = "SELECT ChartWicketQuery.*, ChartWicketQuery.UserID FROM(ChartWicketQuery)WHERE (((ChartWicketQuery.UserID)=34))"

       oRs = New DbConn(strQuery)

       While oRs.ReadData.Read()

           'Now create second recordset to get details for this factory

           strQuery = "SELECT ChartWicketQuery.MatchWickets, ChartWicketQuery.MatchDateFormatted, ChartWicketQuery.MatchBallsBowled, ChartWicketQuery.MatchRunsConceded FROM(ChartWicketQuery)WHERE (((ChartWicketQuery.UserID)=34) AND ((ChartWicketQuery.MatchDateFormatted)=" & oRs.ReadData("MatchDateFormatted").ToString()

           Dim oRs2 As New DbConn(strQuery)



           strXML = strXML & ""

           'Close recordset


       End While


       'Finally, close  element

       strXML = strXML & ""

       'Create the chart - Pie 3D Chart with data from strXML

       Return FusionCharts.RenderChart("../FusionCharts/FCF_Column3D.swf", "", strXML, "FactorySum", "650", "450", False, False)

   End Function

End Class




This code doesnt work as i get an error which i believe is something to do with the date.




Could you please help me as i would really love to put this on the site and impress the players (one of whom is my son):)




Thank you in advance

Share this post

Link to post
Share on other sites

Thank you for that link but it doesnt really help me that much as the example is using hard coded data. I need to know how to implement this using an access database and get the three sets of database data into the graph, how to pass a url parameter (for the particular player) to the graph and how to group my data using a date as the parameter for the second, third and fourth query from the database.




If you have a look at the database example code that i modified in the last post, perhaps you will understand more what i am getting at.




Thanks in advance

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now