Passing MS SQL2005 query result into Javascript function

I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function.

This is how I retrieve the longitude:

<asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"

SelectCommand="SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"></asp:SqlDataSource>

I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this:

var map = new GMap2(document.getElementById("map"));

var lat = <%=lat%>;

var lon = <%=lon%>;

var center = new GLatLng(lat,lon);

map.setCenter(center, 13);

However, lat & long do not contain the retrieved result but rather a useless System.something string.

How do I assign the retrieved results to these variables and port them over to Javascript as required?

Many thanks!

Could you show the code that you use to apply the results from the query(ies) to the variableslon andlat? Only it seems the fault is at that part.|||

I think the problem we are having is that we're not sure how to assign the results to a variable.

Do you know how we pass the returned value into a variable in ASP first?

|||

SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)

When we execute that query in SQL Management Studio we get the latest longitude entry from our database no problem (we also do it for latitude).

However, we assumed that this was passed into the ID of the piece of ASP code where we have it and hence accessible to the javascript, but it's not. How do we assign the result of this SQL execution to a string variable and have it accessible to javascript?

I know it sounds simple but I'm just finding rubbish in Google.
Thanks!

|||

You need to assign the two values you get from the database to string variables then write those strings to the page as part of your javascript code block.

Something like this:

SqlCommand cmd = new SqlCommand("SELECT lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)", conn);
string lon = (string)cmd.ExecuteScalar();

lon is now the variable containing a string representaion of the value, which you should be able to use in the way you did before:

<%= lon %>

Have a look at this link for more on the command object and retrieving scalar values:http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx

|||

Thanks Mike.

However, when I put this code between the...

-><asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"
AND
-> </asp:SqlDataSource>

..tags, I get an error that "SqlCommand" is not a valid attribute of the element SQqlDataSource.

|||Am I just to put this C# code between percentage brackets?<% like these?? %>|||

<asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"SelectCommand="SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"></asp:SqlDataSource>

Should I dump this method of connecting to the database altogether? Your method seems to be just using pure c#, whereas we are placing this in a page of html/ASP.

Sorry for sounding completely remedial!

|||Probably easier not to use the Sqldatasource in this instance. Try the C# in the code behind. The sqlDatasource is fine for quickly binding data to a GridView or something.|||

Thanks!I've managed to get the code working in a .cs file and have referenced it at the top of my Routes.aspx file asCodeFile="Routes.aspx.cs"

I set breakpoints and stepped through the code and have a while loop to read the last two longitude and latitude entries from the database.

In the .cs file I set up the variables as doubles.

while (rdr.Read())
{
Double lon=rdr.GetDouble(0);
Double lat=rdr.GetDouble(1);
}

Back in the main aspx file I set::

var lat = <%=lat%>
var lon = <%=lon%>

However I get the error "lat" and "lon" do not exist in the current context.
AFAIK, the .cs code should execute before the aspx file is run?

|||

The cs file and the aspx file both form (partial) parts of the same page class. It all gets executed at the same time. Anyway, it maybe better if you used literal controls:

public string lon;
public string lat;

while (rdr.Read())
{
lon=rdr.GetDouble(0).ToString();
lat=rdr.GetDouble(1).ToString();

}

litLon.Text = lon;
litLat.Text = lat;

Then in your aspx:

var lat = <asp:Literal ID="litLat" runat="server" />
var lon =<asp:Literal ID="litLon" runat="server" />

|||Thank you! This is now working..|||

Hi Guys,

Im now trying to use an array from the C# page and pass it to Javascript on the aspx page.

Is this similar to what we have above?

Thanks for all your help.

|||

Im still not sure how to do this exactly.

Ive been searching forums for a while now.

Ive read a couple of post that say its not possible to pass a C# array back to the Javascript. Can some one confirm this?

Any tips at all?

Thanks

|||

If I were you, I'd create a new post for this. The original question was answered, so this thread is marked as resolved. That means not many people will be looking at it. Also, a new thread with a relevant subject line will be easier for people to search in the future.

When you post, mention the nature of the array - whether it's one dimensional etc, and how you are generating it.

sql