google charts not showing on server machine

Matevz Uros Pavlic

unread,

Mar 4, 2013, 6:04:12 AM3/4/13

to google-visua...@googlegroups.com

Hi all,

I've created a site where I get the data from SQL server and draw a chart. Now everything works perfectly on my local machine (localhost), but when I deploy it on the server, the chart don't show. Here's the code i use :

	@using System.Data;
	@using System.Collections.Generic;
	@using System.Data.SqlClient;
	@{    
		Layout = null;
 
		string connectionString="Server=192.168.1.125,1433\\sqlexpress; Database=HEK; Uid=sa; Pwd=lubelincek12121";
		DataTable dt = new DataTable();
 
        String rows = "";
        
		using (SqlConnection conn = new SqlConnection(connectionString))
		{
			conn.Open();
			using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 500  Datum, Sava, P1, P2, P3 FROM vws ORDER BY DATUM DESC", conn))
			{
				adapter.Fill(dt);
                    
				List<string> rowsList = new List<string>();
 
               // rowsList.Add("['Datum', 'Sava', 'RPWS1']");
                foreach (DataRow row in dt.Rows)
                {
                    //Converts your object into a DateTime (so that it's properties can be accessed properly
                    DateTime yourDate = Convert.ToDateTime(row["Datum"]);
                    rowsList.Add("[new Date(" + yourDate.Year + ", " + (yourDate.Month - 1) + ", " + yourDate.Day + ", " + yourDate.Hour + ", " + yourDate.Minute + ", " + yourDate.Second + "), " 
                    + row["Sava"] + ", " 
                    + row["P1"] + "," 
                    + row["P2"] + "," 
                    + row["P3"] + "]");
                }
				rows = String.Join(", ", rowsList);
			};
		};
	}
@if (Roles.IsUserInRole("admin")||(Roles.IsUserInRole("hek"))){
    <span style="color: rgba(0,0,0,.33)"><b></b>.</span>
}
else{
    Response.Redirect("~/members/AdminError");
}
<html>
<head>
    <title>Test Graph</title>
<script type='text/javascript' src="http://www.google.com/jsapi"></script>
<script type="text/javascript" src="/java/asgallant.LineChartAsAnnotatedTimeline[0.4b].js"></script>
<script type="text/javascript">
    function drawChart() {
        var data1 = new google.visualization.DataTable()
        data1.addColumn('datetime', 'Datum');
        data1.addColumn('number', 'Sava');
        data1.addColumn('number', 'P1');
        data1.addColumn('number', 'P2');
        data1.addColumn('number', 'P3');
 
        data1.addRows([@Html.Raw(rows)]);
 
        var chart = new asgallant.LineChartAsAnnotatedTimeline(document.getElementById('chart_div'));
        chart.draw(data1, {
            title: 'Absolutni nivo vode',
            height: 350,
            width:850,
            vAxis: {
                minValue: 162,
                maxValue: 164
 
            }
            
        });
    }
    google.load('visualization', '1', { 'packages': ['controls'] });
    google.setOnLoadCallback(drawChart);
</script>
</head>
 
  <body>
    <div id="chart_div"></div>
  </body>
</html>

I've copied the whole folder with all java files and everything.

Any idead would be appreciated , m

asgallant

unread,

Mar 4, 2013, 12:34:26 PM3/4/13

to google-visua...@googlegroups.com

Open it in chrome and see what the error console says (ctrl+shift+j to open).

Matevz Uros Pavlic

unread,

Mar 4, 2013, 12:59:28 PM3/4/13

to google-visua...@googlegroups.com

here is the attached snip of the error.

Probably something with path to js files...?

error.PNG

asgallant

unread,

Mar 4, 2013, 1:05:55 PM3/4/13

to google-visua...@googlegroups.com

The javascript file for my ATL conversion tool isn't where you linked to it in the script tag. It also looks like there is another error on the page which probably has to be resolved, but lets clear this one up first.

Matevz Uros Pavlic

unread,

Mar 4, 2013, 1:23:42 PM3/4/13

to google-visua...@googlegroups.com

ok, got that settleed -(relative path was given). But the charts still don't show up...

got one error that could be the problem :

error1.PNG

error1a.PNG

asgallant

unread,

Mar 4, 2013, 1:27:42 PM3/4/13

to google-visua...@googlegroups.com

That error is pretty self-explanatory: the DataTable was expecting 5 columns of data, but at least 1 of the rows input has more or less than 5 columns. Post the rendered javascript and I'll take a look.

Matevz Uros Pavlic

unread,

Mar 4, 2013, 1:29:09 PM3/4/13

to google-visua...@googlegroups.com

i think it has to do with regional setting on the machine (decimal symbol and thousand separator). This is the snip out of the rendered code :

data1.addRows([[new Date(2013, 1, 1, 10, 0, 0), 162,86, 159,18,163,5,160,67], [new Date(2013, 1, 1, 8, 0, 0), 162,86, 159,18,163,52,160,67],

There should be decimal dots like in example 162.86, 159.18, 163.5, 160.67 ...

Where can i change that?

m

Matevz Uros Pavlic

unread,

Mar 4, 2013, 1:42:15 PM3/4/13

to google-visua...@googlegroups.com

I am sure it's about the decimal point...there should be 5 columns but are more now, because it renders the decimal separator as comma. Here's the render;

Thank for the help,

m

Decimal.htm

asgallant

unread,

Mar 4, 2013, 2:48:22 PM3/4/13

to google-visua...@googlegroups.com

Hmmm...I have a half-baked solution in mind: build your rows as a list of lists, where each sub-list contains the cells in the row, use a JSON serializer on the list, and then in javascript, use the JSON.parse method on it. I don't know precisely how it would be implemented, but that would be the elegant solution.

Maybe the easier-to-implement solution would be to hack something together using ASP.net's localization features to set the locale for those outputs to 'en-US', which would give the right decimal separator.

Matevz Uros Pavlic

unread,

Mar 4, 2013, 3:16:09 PM3/4/13

to google-visua...@googlegroups.com

Hi,

the second option sounds way easier ;) . I'll check it out...

thanks

Matevz Uros Pavlic

unread,

Mar 4, 2013, 3:28:28 PM3/4/13

to google-visua...@googlegroups.com

i have another problem regarding chart data. In SQL i have stored some null data, which are marked as -777. Is it possible to not use this data in charts and use NULL instead?

asgallant

unread,

Mar 4, 2013, 3:53:38 PM3/4/13

to google-visua...@googlegroups.com

Yes, you can use nulls in the chart, but you will have to translate "-777" into null manually.

Matevz Uros Pavlic

unread,

Mar 4, 2013, 4:04:45 PM3/4/13

to google-visua...@googlegroups.com

What do you mean manually?

I'd like to write a If sentence which would check if there is -777 and write null instead. But i don't understand where in my code should i do this, because i have a rowsList that adds all the rows in a Array.

asgallant

unread,

Mar 4, 2013, 7:06:54 PM3/4/13

to google-visua...@googlegroups.com

You would want to check before adding to the rowsList. It could also be done in javascript, but it is probably easier to do in the server-side code.

Matevz Uros Pavlic

unread,

Mar 5, 2013, 11:53:00 AM3/5/13

to google-visua...@googlegroups.com

Hi,

i've been working the whole day on finding out how to check if the values have -777 and replacing it with null value. I made that work (with help of asp.net forums)...BUT...there is a problem.

I think we need to change that in Javascript, because if I change it in C# part, i can only change n-1 rows. For example, if i check and replace with nulls four out of four columns, the chart doesn't show, if i leave the last column not-null the chart shows. If i check the code in Console (shift+ctrl+j) i get the error :

Uncaught Error: Row given with size different than 6 (the number of columns in the table).

If i check Source the Array looks like this, when replaced with nulls :

[new Date(2013, 0, 18, 14, 0, 0), , , , , ]

And where values are not null, the array looks like that :

[new Date(2013, 0, 18, 16, 0, 0), 268.14, 270.09, 270.55, 268.55, 259.3]

It certainly has something to do with the last value when it is changed to NULL. I tried changing it to NULL also in SQL Server and importing such values. The result was the same.

This is the code i used to replace the -777 values :

{
                    //Converts your object into a DateTime (so that it's properties can be accessed properly
                    DateTime yourDate = Convert.ToDateTime(row["Datum"]);

 
                    if (Convert.ToString(row["MPT11"]) == "-777") {row["MPT11"]=    DBNull.Value;}
                    if (Convert.ToString(row["MPT12"]) == "-777") {row["MPT12"]=    DBNull.Value;}
                    if (Convert.ToString(row["MPT2"]) == "-777") {row["MPT2"]=      DBNull.Value;}
                    if (Convert.ToString(row["MPT31"]) == "-777") {row["MPT31"]=    DBNull.Value;}
                    if (Convert.ToString(row["MPT32"]) == "-777") {row["MPT32"]=    DBNull.Value;}

 
                    rowsList.Add("[new Date(" + yourDate.Year + ", " + (yourDate.Month - 1) + ", " + yourDate.Day + ", " + yourDate.Hour + ", " + yourDate.Minute + ", " + yourDate.Second + "), "

 
                    + row["MPT11"] + ", " 
                    + row["MPT12"] + ", " 
                    + row["MPT2"]  + ", " 
                    + row["MPT31"] + ", "
                    + row["MPT32"] + "]");

Any ideas? could that also be because of regional settings (bTW , this is on local machine, not server).

thanks, m

asgallant

unread,

Mar 5, 2013, 12:18:18 PM3/5/13

to google-visua...@googlegroups.com

You should replace the -777 with a string "null", so that the output looks like this:

[new Date(2013, 0, 18, 14, 0, 0), null, null, null, null, null]

Most browsers interpret ", ," as a null, but the last comma ",]" gets ignored in most browsers (which is why you get that "Row given with size different than 6" error) and causes IE to bomb.

Matevz Uros Pavlic

unread,

Mar 5, 2013, 1:56:01 PM3/5/13

to google-visua...@googlegroups.com

Hi,

nice to know that ;)

I managed to change the last column like this :

                String lastCol = Convert.ToString(row["MPT32"]);

              
                    if (Convert.ToString(row["MPT11"]) == "-777") {row["MPT11"] = DBNull.Value;}
                    if (Convert.ToString(row["MPT12"]) == "-777") {row["MPT12"] = DBNull.Value;}
                    if (Convert.ToString(row["MPT2"])  == "-777") {row["MPT2"]  = DBNull.Value;}
                    if (Convert.ToString(row["MPT31"]) == "-777") {row["MPT31"] = DBNull.Value;}

                    if (lastCol == "-777") {lastCol = "null";}

 
                    rowsList.Add("[new Date(" + yourDate.Year + ", " + (yourDate.Month - 1) + ", " + yourDate.Day + ", " + yourDate.Hour + ", " + yourDate.Minute + ", " + yourDate.Second + "), " 
                    + row["MPT11"] + ", " 
                    + row["MPT12"] + ", " 
                    + row["MPT2"]  + ", " 
                    + row["MPT31"] + ", "

                    + lastCol + "]");

And it's finally working.

Still having troubles to hack it on server thought...Decimal points vs. Comma.

Thanks for the help!

asgallant

unread,

Mar 5, 2013, 2:50:56 PM3/5/13

to google-visua...@googlegroups.com

Instead of using DBNull, just use "null":

if (Convert.ToString(row["MPT11"]) == "-777") {row["MPT11"] = "null";}

That way, your code will be consistent, and you won't run afoul of any browsers that mishandle empty slots in an array.

Matevz Uros Pavlic

unread,

Mar 5, 2013, 2:59:34 PM3/5/13

to google-visua...@googlegroups.com

I tried that before, but it doesn't work. I get this error :

System.FormatException: Input string was not in a correct format.

m

asgallant

unread,

Mar 5, 2013, 5:02:23 PM3/5/13

to google-visua...@googlegroups.com

What if you use intermediary variables to carry the data?

String MPT11 = "";

...

MPT11 = (row["MPT11"] == -777) ? "null" : row["MPT11"].ToString();

...

rowList.Add(... , MPT11, ...);

Matevz Uros Pavlic

unread,

Mar 5, 2013, 5:38:15 PM3/5/13

to google-visua...@googlegroups.com

Error,

operator '==' can not be applied to operadns of type 'object' and 'int'.

But, as i understand, i did something similar to what you mean by :

foreach (DataRow row in dt.Rows)

{

//Converts your object into a DateTime (so that it's properties can be accessed properly

DateTime yourDate = Convert.ToDateTime(row["Datum"]);

String lastCol = Convert.ToString(row["MPT32"]);

if (Convert.ToString(row["MPT11"]) == "-777") {row["MPT11"] = DBNull.Value;}

if (Convert.ToString(row["MPT12"]) == "-777") {row["MPT12"] = DBNull.Value;}

if (Convert.ToString(row["MPT2"]) == "-777") {row["MPT2"] = DBNull.Value;}

if (Convert.ToString(row["MPT31"]) == "-777") {row["MPT31"] = DBNull.Value;}

if (lastCol == "-777") {lastCol = "null";}

rowsList.Add("[new Date(" + yourDate.Year + ", " + (yourDate.Month - 1) + ", " + yourDate.Day + ", " + yourDate.Hour + ", " + yourDate.Minute + ", " + yourDate.Second + "), "

+ row["MPT11"] + ", "

+ row["MPT12"] + ", "

+ row["MPT2"] + ", "

+ row["MPT31"] + ", "

+ lastCol + "]");

}

rows = String.Join(", ", rowsList);

I just changed the last value (lazy) , and it works.

Now i have that error from before, when using it like this i get commas instead of dots in server render, when running the script from server.

And java console says i have more than 5 columns....that's what i can't fix (currently).

Thanks for the help

asgallant

unread,

Mar 5, 2013, 8:02:15 PM3/5/13

to google-visua...@googlegroups.com

Try this:

NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;

String MPT11 = "";

...

MPT11 = (row["MPT11"].ToString() == "-777") ? "null" : row["MPT11"].ToString("N", nfi);

...

rowList.Add(... , MPT11, ...);

Matevz Uros Pavlic

unread,

Mar 6, 2013, 2:34:13 AM3/6/13

to google-visua...@googlegroups.com

Tried something similar already, but as with your suggestions, i get this error :

"No overload for method ToString takes 2 argument."

m

Matevz Uros Pavlic

unread,

Mar 6, 2013, 6:44:56 AM3/6/13

to google-visua...@googlegroups.com

Well, with the help of asp.net forums, i manage to solve part of this.

But i guess when Javascript changes the input from 'string' as C# to number, it changes the dots (.) to commas (,).

Any ideas?

thanks, m

asgallant

unread,

Mar 6, 2013, 8:28:28 AM3/6/13

to google-visua...@googlegroups.com

Ok, the row is returning an Object when you call row["MPT11"], and the ToString method for Objects doesn't take any arguments. You have to convert the output to an appropriate data type (int, decimal, double, whatever), and then use the ToString method on that.

NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;

String MPT11 = "";

...

MPT11 = (row["MPT11"].ToString() == "-777") ? "null" : Convert.ToDouble(row["MPT11"]).ToString("N", nfi);

...

rowList.Add(... , MPT11, ...);

That should solve the formatting problem and the null problem together in one.

Matevz Uros Pavlic

unread,

Mar 6, 2013, 11:35:21 AM3/6/13

to google-visua...@googlegroups.com

Yeeeah, that worked. You the man. Thanks!!!!! M

asgallant

unread,

Mar 6, 2013, 2:19:09 PM3/6/13

to google-visua...@googlegroups.com

Awesome, glad to hear it.

Reply all

Reply to author

Forward