populating array with the data from database

amorous · Jul 6, 2004

Hi All,

I am trying to create a simple bar chart using ASP.net and after searching online i got a sample code which does this with static data...but i want to fetch the data from my SQL database and not sure of how to change this below piece of code to populate the dynamic data from DB...

Any help would be appreciated...

Thanks in advance...
-VJ

Code:

void Page_Load(Object sender, EventArgs e) {            
    
        // Declare our variables
        String [] sItems = new String[10];
        Int32 [] iValue = new Int32 [10];

        // Populate our variables
        sItems[0] = "Carrots";
        iValue[0] = 23;
        
        sItems[1] = "Peas";
        iValue[1] = 53;
        
        sItems[2] = "Celery";
        iValue[2] = 11;
        
        sItems[3] = "Onions";
        iValue[3] = 21;
        
        sItems[4] = "Radishes";
        iValue[4] = 43;
        
        // Set our axis values
        dngchart.YAxisValues = iValue;
        
        // Set our axis strings
        dngchart.YAxisItems  = sItems;   
}

i tried something like this .....

created a db connection string, a data reader and looping thru the data but it didnt work...

Dim connString As String = "Data Source = STJSQL\STJDB; Database=STJData;User ID=xxxx;Password=xxxx"
Dim sql As String = "my sql string"

Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn)

conn.Open()

Dim ReaderObj As SqlDataReader = command.ExecuteReader()

Do While ReaderObj.Read()

jmeckley · Jul 6, 2004

I would use 1 datatable as your data source instead of 2 arrays.

Here's how to do it:

Open connection
Set datareader
Fill the dataset with the datareader
Close the datareader
Close the connection

Now you can use the dataset however you want. To access the table in the dataset use DataSet.Tables(0). Then to reference the items and values in the chart use

Code:

dngChart.YAxisValues = DataSet.Tables(0).Columns(0)
dngChart.YAxisItems = DataSet.Tables(0).Columns(1)

Jason Meckley
Database Analyst
WITF

amorous · Jul 6, 2004

Hi Jason,

Thanks for your Reply...

Here is what i did....

Code:

<%@ Register TagPrefix="DNG" TagName="DotNetGraph" Src="DotNetGraph.ascx" %>

<%@ Import Namespace="System.Drawing"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>


<script language="C#" runat="server">

Dim connString As String = "Data Source = STJSQL\STJDB; Database=STJData;User ID=xxx;Password=xxxxx"
Dim sql As String = "SELECT t.Description as InventName, SUM(CASE WHEN m.Active_Ind =1 THEN 1 ELSE 0 END) as itemtotal FROM Inventory_Main m join Inventory_Type t on m.Inventory_Type_ID=t.Inventory_Type_ID GROUP BY m.Inventory_Type_ID, t.Description ORDER BY t.Description ASC "

Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn)

conn.Open()

Dim ReaderObj As SqlDataReader = command.ExecuteReader()

ReaderObj.Close()
conn.Close() 


    void Page_Load(Object sender, EventArgs e) {            
    
        // Set our axis strings
        dngchart.YAxisValues  = DataSet.Tables(0).Columns(0);            
        dngchart.YAxisItems  = DataSet.Tables(0).Columns(1);  
        
        // Provide a title
        dngchart.ChartTitle  = "<b>Inventory Breakdown:</b>";
        
        // Provide an title for the X-Axis
        dngchart.XAxisTitle  = "(units display actual numbers)";        
        
    }
            
</script>

<html>
<body>
<!-- Note UserWidth is set "in-line".  It could just as easily been set on the page_load method. -->
<DNG:DotNetGraph id=dngchart UserWidth=200 runat=server />
</body>
</html>

Here is the other file used for creating graphis....

and it has array functions which collects the values...how do i change these...

Code:

<script language="C#" runat="server">

    private String        _sXAxisTitle;
    private String        _sChartTitle;
    private Int32        _iUserWidth = 300;
    private String []    _sYAxisItems;
    private Int32 []    _iYAxisValues;

    public Int32 UserWidth {
        get { return _iUserWidth; }
        set { _iUserWidth = value; }
    }

    public Int32 [] YAxisValues {
        get { return _iYAxisValues; }
        set { _iYAxisValues = value; }
    }

    public String [] YAxisItems {
        get { return _sYAxisItems; }
        set { _sYAxisItems = value; }
    }

    public String XAxisTitle {
        get { return _sXAxisTitle; }
        set { _sXAxisTitle = value; }
    }

    public String ChartTitle {
        get { return _sChartTitle; }
        set { _sChartTitle = value; }
    }

    void Page_Load(Object sender, EventArgs e) {    
    
        // As long as we have values to display, do so
        if (_iYAxisValues != null) {
            
            // Color array
            String [] sColor = new String[9];
            sColor[0] = "red";
            sColor[1] = "lightblue";
            sColor[2] = "green";
            sColor[3] = "orange";
            sColor[4] = "yellow";
            sColor[5] = "blue";
            sColor[6] = "lightgrey";
            sColor[7] = "pink";
            sColor[8] = "purple";

            // Initialize the color category
            Int32 iColor = 0;

            // Display the chart title
            lblChartTitle.Text = _sChartTitle;

            // Get the largest value from the available items
            Int32 iMaxVal = 0;            
            for (int i = 0; i < _iYAxisValues.Length; i++) {
                if (_iYAxisValues[i] > iMaxVal)
                    iMaxVal = _iYAxisValues[i];
            }

            // Take the user-provided maximum width of the chart, and divide it by the
            // largest value in our valueset to obtain the modifier
            Int32 iMod = Math.Abs(_iUserWidth/iMaxVal);

            // This will be the string holder for our actual bars.
            String sOut = "";
            
            // Render a bar for each item
            for (int i = 0; i < _iYAxisValues.Length; i++) {

                // Only display this item if we have a value to display
                if (_iYAxisValues[i] > 0) {

                    sOut += "<tr><td align=right>" + _sYAxisItems[i] + "</td>";
                    sOut += "<td>" + RenderItem(_iYAxisValues[i], iMod, sColor[iColor]) + "</td></tr>";
                    iColor++;
                    
                    // If we have reached the end of our color array, start over
                    if (iColor > 8) iColor = 0;
                }
            }
            
            // Place the rendered string in the appropriate label
            lblItems.Text = sOut;
            
            // Drop in the Y Axis label
            lblXAxisTitle.Text = _sXAxisTitle;
        }
    }

  
    private String RenderItem (Int32 iVal, Int32 iMod, String sColor) {
StringBuilder sb = new StringBuilder();
sb.Append("<table border=0 bgcolor=" + sColor + " cellpadding=0 cellspacing=0><tr>");
sb.Append("<td align=center width=" + (iVal * iMod) + " nobr nowrap>");
sb.Append("<b>" + iVal + "</b>");
sb.Append("</tr><td></table>");
return sb.ToString();
}

        
</script>

<table>
    <tr>
        <td align=center>
            <asp:Label id=lblChartTitle runat=server />            
        </td>
    </tr>
    <tr>
        <td>
            <table border=1 bordercolor='#777777' cellspacing=0 cellpadding=0>
                <tr>
                    <td>
                        <table>
                            <asp:Label id=lblItems runat=server />
                        </table>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
    <tr>
        <td colspan=2 align=center>
            <asp:Label id=lblXAxisTitle runat=server />
        </td>
    </tr>
</table>

Thanks in advance
-VJ

jmeckley · Jul 6, 2004

the best idea for this may be to cycle through the rows and use arrays rather than the datatable.

You will also need to choose what language you want to write in, the copied code is in C# while the code you wrote is VB. I believe you need to pick one or the other per page.

You will need to place your code within an event, either onload, onclick, or onselectedindexchange.

You also need to declare the databset and fill the dataset with the datareader. Use the help index and lookup DataSet.Fill to find the exact syntax.

Jason Meckley
Database Analyst
WITF

amorous · Jul 6, 2004

Ok here is the thing...I am new to ASP.NET and C#...

I think i need to drop the idea of making changes to the code and start reading some stuff reading these languages..

Anyways thanks for your help

-VJ

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

populating array with the data from database

amorous

Programmer

jmeckley

Programmer

amorous

Programmer

jmeckley

Programmer

amorous

Programmer

Similar threads

Part and Inventory Search

Sponsor