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

pass in or retrieve a computername using vb.net & sql sp

I have two textboxes on a Winform. I want to be able to enter a Computername like "PapaSmurf" into the first textbox, an application name like "adobe" into the second textbox, have it retrieve the data via a DataGrid/DataTable and a Stored Procedure.

My Stored Procedure (I'm using SQL 2005) looks like this:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%Microsoft%' AND WORKSTATION_NAME LIKE '%B001321D14F%'.

It's obvious that I can't use a static Workstation_Name.

It looks like I will need to setup a parameter but how do I set it up so that whatever is in the ComputerName textbox on the form get's passed into the Stored Procedure Parameter or maybe I have it backwards and the SP needs to somehow pull it from the Winform textbox.

Any ideas?

Wallace

You can use parameters to pass in the fields use ADO.net in VB.net

change the stored procedure to:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

@.ComputerName as varchar(50),

@.WorkStation as varchar(50)

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%' + @.ComputerName +'%' AND WORKSTATION_NAME LIKE '%' + @.WorkStation + '%'.

Then the ado.net:

Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnection").ToString)

sqlConn.Open()

Try

Dim command As SqlCommand = New SqlCommand("uspSelectAnyPkgName", sqlConn)

command.Parameters.AddWithValue("@.ComputerName", ComputerName.Text)

command.Parameters.AddWithValue("@.WorkStation", WorkStation.Text)

command.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

sqlConn.Close()

sqlConn = Nothing

GC.Collect()

End Try

You'll probably want to use a datareader to populate a grid since you're selecting.

Adamus

|||

Hi Adam,

It appears as if this may work but I left out one important detail. I have no idea on how to write a loop (since I am new to vb.net) in the Stored procedure or in vb.net, which ever it may be, to get for instance, an application name that starts with "KB" (Like as in the Microsoft Knowledge Base) from ComputerName "MYPC".

The "command.ExecuteNonQuery()" gives me the error, "Line 1: Incorrect syntax near 'uspSelectAnyPkgName'".

I used the following SQL DataAdapter and Fill method but it grabbed every software package name and every computer name other than:

SqlDA_SinglePkgName.Fill(AllTablesDataSet1.uspSelectAnyPkgName)

So, it appears that the Fill method or the command.ExecuteNonQuery are not the appropriate methods or I'm not passing something in.

Ideally, I just want the specific software package on one computer to be pulled from the DB.

Your help again would be appreciated.

Thanks,

Wallace

|||

Adamus,

Thank you for your help. This worked great.

Wally

pass in or retrieve a computername using vb.net & sql sp

I have two textboxes on a Winform. I want to be able to enter a Computername like "PapaSmurf" into the first textbox, an application name like "adobe" into the second textbox, have it retrieve the data via a DataGrid/DataTable and a Stored Procedure.

My Stored Procedure (I'm using SQL 2005) looks like this:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%Microsoft%' AND WORKSTATION_NAME LIKE '%B001321D14F%'.

It's obvious that I can't use a static Workstation_Name.

It looks like I will need to setup a parameter but how do I set it up so that whatever is in the ComputerName textbox on the form get's passed into the Stored Procedure Parameter or maybe I have it backwards and the SP needs to somehow pull it from the Winform textbox.

Any ideas?

Wallace

You can use parameters to pass in the fields use ADO.net in VB.net

change the stored procedure to:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

@.ComputerName as varchar(50),

@.WorkStation as varchar(50)

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%' + @.ComputerName +'%' AND WORKSTATION_NAME LIKE '%' + @.WorkStation + '%'.

Then the ado.net:

Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnection").ToString)

sqlConn.Open()

Try

Dim command As SqlCommand = New SqlCommand("uspSelectAnyPkgName", sqlConn)

command.Parameters.AddWithValue("@.ComputerName", ComputerName.Text)

command.Parameters.AddWithValue("@.WorkStation", WorkStation.Text)

command.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

sqlConn.Close()

sqlConn = Nothing

GC.Collect()

End Try

You'll probably want to use a datareader to populate a grid since you're selecting.

Adamus

|||

Hi Adam,

It appears as if this may work but I left out one important detail. I have no idea on how to write a loop (since I am new to vb.net) in the Stored procedure or in vb.net, which ever it may be, to get for instance, an application name that starts with "KB" (Like as in the Microsoft Knowledge Base) from ComputerName "MYPC".

The "command.ExecuteNonQuery()" gives me the error, "Line 1: Incorrect syntax near 'uspSelectAnyPkgName'".

I used the following SQL DataAdapter and Fill method but it grabbed every software package name and every computer name other than:

SqlDA_SinglePkgName.Fill(AllTablesDataSet1.uspSelectAnyPkgName)

So, it appears that the Fill method or the command.ExecuteNonQuery are not the appropriate methods or I'm not passing something in.

Ideally, I just want the specific software package on one computer to be pulled from the DB.

Your help again would be appreciated.

Thanks,

Wallace

|||

Adamus,

Thank you for your help. This worked great.

Wally