locked
Add pushpins on map from SQL Server data RRS feed

  • Question

  • Hi guys,

    Im a newbie with ASP.NET and mainly Virtual Earth. I have a simple table in an SQL Server database with 3 columns (id, longitude, latitude) and i want to plot each row in the table as a pushpin.

    From what i read i need to create some sort of an XML in order to read it either via GeoRSS or via javascript to display the pushpins on the map. To start with i dont know how to create that XML file...pls advice on how i can do this as well as which of the 2 above methods is best to use to plot the points (and how to do it)

    Thank you in advance,
    George
    Wednesday, August 6, 2008 6:01 PM

Answers

  • If you haven't already, download and install WindowsLiveTools_July2008CTP on you computer.

     

    Then in VS 2008 create a new ASP.Net web application using .NET Framework 3.5. In the Default.aspx page, add an AJAX Extensions ScriptManager with EnablePartialRendering=true. Now add the VE Map control to your page from VS 2008's toolbox. Set its width and height properties. For some reason, all of a sudden on my machine, setting the map's zoomlevel causes the designer to complain, but everything still works the same with or without the zoomlevel set.

     

    For the code behind that retrieves the database information, I use the following: (note this is using a MySQL database--you need to change the connection string to one for SQL Server)

     

    Public Partial Class Test

    Inherits System.Web.UI.Page

     

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Map1.Clear()

    Dim ds As Data.DataSet = GetPins("YourTableName")

    Dim SL As New Microsoft.Live.ServerControls.VE.ShapeLayer

    Map1.AddShapeLayer(SL)

    Dim BasePhotoURL As String = http://www.YourWebsite/photos/

    Dim i As Integer = 0

    For i = 0 To ds.Tables(0).Rows.Count - 1

    Dim MyPoint As New Microsoft.Live.ServerControls.VE.LatLongWithAltitude

    MyPoint.Latitude = ds.Tables(0).Rows(i).Item("Lat")

    MyPoint.Longitude = ds.Tables(0).Rows(i).Item("Lon")

    Dim MyPin As New Microsoft.Live.ServerControls.VE.Shape(Microsoft.Live.ServerControls.VE.ShapeType.Pushpin, MyPoint)

    MyPin.Title = ds.Tables(0).Rows(i).Item("Title")

    MyPin.Description = ds.Tables(0).Rows(i).Item("Description")

    MyPin.PhotoURL = BasePhotoURL & ds.Tables(0).Rows(i).Item("Link")

    SL.AddShape(MyPin)

    Next

    End Sub

     

    Private Function GetPins(ByVal MyTable As String) As Data.DataSet

    'Get the information from Database - This is for MySQL. You will need to make the appropriate changes for SQL Server

    Dim TableName As String = "MyTable"

    Dim conn As New System.Data.Odbc.OdbcConnection

    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=69.80.208.10;DATABASE=MyDatabaseName;UID=MyUID;PASSWORD=MyPassword;OPTION=3"

    conn.Open()

    Dim da As Data.Odbc.OdbcDataAdapter

    Dim ds As New Data.DataSet

    Dim SQLText As String = "SELECT * FROM " & TableName

    da = New Data.Odbc.OdbcDataAdapter(SQLText, conn) 

    da.Fill(ds)

    conn.Close()

    Return ds

    End Function

    End Class

    Wednesday, August 6, 2008 9:53 PM
  • Thank you a lot for your reply William...so glad im using VS 2008!!

    in case anybody wants the c# version of the above (slightly modified) i posted it below...

    Code Snippet

    protected void Page_Load(object sender, EventArgs e)   

    {

    if (!IsPostBack)

      {
      Map1.Clear();

      DataSet ds = GetPins("MapPointLocations");

      Microsoft.Live.ServerControls.VE.ShapeLayer SL = new Microsoft.Live.ServerControls.VE.ShapeLayer();

      Map1.AddShapeLayer(SL);

    string BasePhotoURL = "mysite";

      int i = 0;

      int lentgh = ds.Tables["MapPointLocations"].Rows.Count-1;

      for (i = 0; i <= lentgh; i++)
      {
      Microsoft.Live.ServerControls.VE.LatLongWithAltitude MyPoint = new Microsoft.Live.ServerControls.VE.LatLongWithAltitude();

    double lat = Convert.ToDouble(ds.Tables["MapPointLocations"].Rows[i][1]);
      double lon = Convert.ToDouble (ds.Tables["MapPointLocations"].Rows[i][2]);

    MyPoint.Latitude = lat;
      MyPoint.Longitude = lon;

      Microsoft.Live.ServerControls.VE.Shape MyPin = new
    Microsoft.Live.ServerControls.VE.Shape(Microsoft.Live.ServerControls.VE.ShapeType.Pushpin,
    MyPoint);

       MyPin.Title = ds.Tables["MapPointLocations"].Rows[i][0].ToString();

       SL.AddShape(MyPin);

                }

            }

        }



        private DataSet GetPins(string table)

        {

            string query = "SELECT * FROM [MapPointLocations]";



            // SQL Connection, Command and Adapter created

            string connectionString = "Data
    Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.MDF;Integrated
    Security=True;User Instance=True";

            SqlConnection con = new SqlConnection(connectionString);

            SqlCommand command = new SqlCommand(query, con);

            SqlDataAdapter adapter = new SqlDataAdapter(command);



            DataSet ds = new DataSet();



            // Connection opened and dataset filled

            try

            {

                con.Open();

                adapter.Fill(ds, table);

            }



            catch (System.Exception e)

            {

                return ds;

            }



            finally

            {

                con.Close();

            }



            return ds;}



    Thursday, August 7, 2008 12:16 AM

All replies

  • If you use Visual Studio 2008, then you can use the new VE Map Control and then connect to your database table in the page load event and populate them map with pins based on your location data using some pretty simple code. So, are you using VS 2008?

     

    If not, it gets more complicated (in my experience).  For VS 2005, you still can connect directly to your database table info.  But now you need to turn it into an XML file on the fly using VS 2005 which is then used as a source for the JavaScript Control.  Or if you just want to use a static XML file as the source, you will still need some programming software to convert your SQL data to an XML file.

     

    Wednesday, August 6, 2008 7:41 PM
  • thank u for ur reply.

    happily i am using vs 2008...do u know how i can use the control to connect to my database?
    Wednesday, August 6, 2008 9:03 PM
  • If you haven't already, download and install WindowsLiveTools_July2008CTP on you computer.

     

    Then in VS 2008 create a new ASP.Net web application using .NET Framework 3.5. In the Default.aspx page, add an AJAX Extensions ScriptManager with EnablePartialRendering=true. Now add the VE Map control to your page from VS 2008's toolbox. Set its width and height properties. For some reason, all of a sudden on my machine, setting the map's zoomlevel causes the designer to complain, but everything still works the same with or without the zoomlevel set.

     

    For the code behind that retrieves the database information, I use the following: (note this is using a MySQL database--you need to change the connection string to one for SQL Server)

     

    Public Partial Class Test

    Inherits System.Web.UI.Page

     

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Map1.Clear()

    Dim ds As Data.DataSet = GetPins("YourTableName")

    Dim SL As New Microsoft.Live.ServerControls.VE.ShapeLayer

    Map1.AddShapeLayer(SL)

    Dim BasePhotoURL As String = http://www.YourWebsite/photos/

    Dim i As Integer = 0

    For i = 0 To ds.Tables(0).Rows.Count - 1

    Dim MyPoint As New Microsoft.Live.ServerControls.VE.LatLongWithAltitude

    MyPoint.Latitude = ds.Tables(0).Rows(i).Item("Lat")

    MyPoint.Longitude = ds.Tables(0).Rows(i).Item("Lon")

    Dim MyPin As New Microsoft.Live.ServerControls.VE.Shape(Microsoft.Live.ServerControls.VE.ShapeType.Pushpin, MyPoint)

    MyPin.Title = ds.Tables(0).Rows(i).Item("Title")

    MyPin.Description = ds.Tables(0).Rows(i).Item("Description")

    MyPin.PhotoURL = BasePhotoURL & ds.Tables(0).Rows(i).Item("Link")

    SL.AddShape(MyPin)

    Next

    End Sub

     

    Private Function GetPins(ByVal MyTable As String) As Data.DataSet

    'Get the information from Database - This is for MySQL. You will need to make the appropriate changes for SQL Server

    Dim TableName As String = "MyTable"

    Dim conn As New System.Data.Odbc.OdbcConnection

    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=69.80.208.10;DATABASE=MyDatabaseName;UID=MyUID;PASSWORD=MyPassword;OPTION=3"

    conn.Open()

    Dim da As Data.Odbc.OdbcDataAdapter

    Dim ds As New Data.DataSet

    Dim SQLText As String = "SELECT * FROM " & TableName

    da = New Data.Odbc.OdbcDataAdapter(SQLText, conn) 

    da.Fill(ds)

    conn.Close()

    Return ds

    End Function

    End Class

    Wednesday, August 6, 2008 9:53 PM
  • Thank you a lot for your reply William...so glad im using VS 2008!!

    in case anybody wants the c# version of the above (slightly modified) i posted it below...

    Code Snippet

    protected void Page_Load(object sender, EventArgs e)   

    {

    if (!IsPostBack)

      {
      Map1.Clear();

      DataSet ds = GetPins("MapPointLocations");

      Microsoft.Live.ServerControls.VE.ShapeLayer SL = new Microsoft.Live.ServerControls.VE.ShapeLayer();

      Map1.AddShapeLayer(SL);

    string BasePhotoURL = "mysite";

      int i = 0;

      int lentgh = ds.Tables["MapPointLocations"].Rows.Count-1;

      for (i = 0; i <= lentgh; i++)
      {
      Microsoft.Live.ServerControls.VE.LatLongWithAltitude MyPoint = new Microsoft.Live.ServerControls.VE.LatLongWithAltitude();

    double lat = Convert.ToDouble(ds.Tables["MapPointLocations"].Rows[i][1]);
      double lon = Convert.ToDouble (ds.Tables["MapPointLocations"].Rows[i][2]);

    MyPoint.Latitude = lat;
      MyPoint.Longitude = lon;

      Microsoft.Live.ServerControls.VE.Shape MyPin = new
    Microsoft.Live.ServerControls.VE.Shape(Microsoft.Live.ServerControls.VE.ShapeType.Pushpin,
    MyPoint);

       MyPin.Title = ds.Tables["MapPointLocations"].Rows[i][0].ToString();

       SL.AddShape(MyPin);

                }

            }

        }



        private DataSet GetPins(string table)

        {

            string query = "SELECT * FROM [MapPointLocations]";



            // SQL Connection, Command and Adapter created

            string connectionString = "Data
    Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.MDF;Integrated
    Security=True;User Instance=True";

            SqlConnection con = new SqlConnection(connectionString);

            SqlCommand command = new SqlCommand(query, con);

            SqlDataAdapter adapter = new SqlDataAdapter(command);



            DataSet ds = new DataSet();



            // Connection opened and dataset filled

            try

            {

                con.Open();

                adapter.Fill(ds, table);

            }



            catch (System.Exception e)

            {

                return ds;

            }



            finally

            {

                con.Close();

            }



            return ds;}



    Thursday, August 7, 2008 12:16 AM
  • im am using this code and it works great! now what method would i use to hide or show this layer? I believe all pins are on a shape called SL? Thanks I am new as well and sometimes this gets confusing. I tried this in my code

     

    <input id='CB1' type="checkbox" checked="checked" onclick="OnClientEventHandler(this)" />

    Show All Clear<br/>

    <script type="text/javascript" language="javascript">

    function OnClientEventHandler (CheckBox1Clicked, cb)

    {

    if (cb.checked)

    {

    SL.Show()

    }

    else

    {

    SL.Hide()

    }

    }</script>

     

     

     but it doesn't work, any help is appreciated
    Friday, August 15, 2008 7:48 PM
  • Hi. For show and hide shapes i use this code:  (hope that is helpful)


    Code:




    function DoShowShape() {
                shape.Show();
            }
       
    function DoHideShape() {
                shape.Hide();
            }
        



      function ShowShape() {
                if (document.getElementById('cbshape').checked) {
                    circle = icon
                    DoShowShape();
                }
                else {
                    if (circle != null)
                        DoHideShape();
                }
            }



    <input id="cbshape" value="Show Shape" type="checkbox" onclick="ShowShape();"
                    style="width: 20px"/>

    Friday, September 12, 2008 9:51 AM
  • Tsikkos , you put the code in C# page or in ASPX page?

    Sorry my english!!
    Monday, September 15, 2008 3:03 PM
  •  

    you insert this code in your server-behind (C#) page. You can either execute it on MapServerLoad or on PageLoad

     

    Hope this helps...

    Tuesday, September 16, 2008 3:58 PM
  • Forget, I discovered what's wrong.
    Tuesday, September 16, 2008 4:09 PM
  • I get shape not defined when i use this code its like the map is not available in java
    Thursday, October 23, 2008 4:35 PM
  • Hi all,

    Im very new to coding.

    Thanks for the above two coding examples which were very helpful.

    Thing is i'm using c# and mySQL so i have been trying to adap the code to suit.

    I have built successfully without error but it will not display my pushpins.

    My database table is called bar, and the longitude field is called BLong, latitude field is called BLat.

    Any help is appreciated.

    Thanks

    Code is as follows:


    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {

                    Map1.Clear();

                    DataSet ds = GetPins("bar");

                    Microsoft.Live.ServerControls.VE.ShapeLayer SL = new Microsoft.Live.ServerControls.VE.ShapeLayer();

                    Map1.AddShapeLayer(SL);

                    int i = 0;

                    int length = ds.Tables.Count - 1;

                    for (i = 0; i <= length; i++)
                    {
                        Microsoft.Live.ServerControls.VE.LatLongWithAltitude MyPoint = new Microsoft.Live.ServerControls.VE.LatLongWithAltitude();

                        double BLong = Convert.ToDouble(ds.Tables.ToString().Equals("1"));
                        double BLat = Convert.ToDouble(ds.Tables.ToString().Equals("2"));

                        MyPoint.Longitude = BLong;
                        MyPoint.Latitude = BLat;

                        Microsoft.Live.ServerControls.VE.Shape MyPin = new Microsoft.Live.ServerControls.VE.Shape(Microsoft.Live.ServerControls.VE.ShapeType.Pushpin,MyPoint);

                        MyPin.Title = ds.Tables.ToString();

                        SL.AddShape(MyPin);
                    }
                }
            }
     

            private DataSet GetPins(string table)
              {
                    //Get the information from Database - This is for MySQL. You will need to make the appropriate changes for SQL Server

                 String Stmt = "Select BLong, BLat, BName, BAddress from bar";
                 String ConnectionString = "Server=localhost;uid=root;pwd=****;DATABASE=****"
                 MySqlConnection con = new MySqlConnection(ConnectionString);
                 MySqlCommand command = new MySqlCommand(Stmt, con);
                 MySqlDataAdapter adapter = new MySqlDataAdapter(command);

                   DataSet ds = new DataSet();    

               
               

                con.Open();


                try
                {

                    adapter.Fill(ds);
                }
                catch (System.Exception e)
                {
                    return ds;
                }

                finally
                {

                    con.Close();
                }


                return ds;

               



            }
        }
    }
    Monday, February 23, 2009 7:40 PM
  • I got it to work, eventually.

    Pretty much the same as what was posted just a change to the connection statements.

    Thanks for the above code for sending me in the right direction.

    Here's the code for mysql and c#


            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {

                    Map1.Clear();

                    DataSet ds = GetPins("bar");
     
                    Microsoft.Live.ServerControls.VE.ShapeLayer SL = new Microsoft.Live.ServerControls.VE.ShapeLayer();

                    Map1.AddShapeLayer(SL);

                    int i = 0;

                    int length = ds.Tables["bar"].Rows.Count-1;

                    for (i = 0; i <= length; i++)
                    {
                        Microsoft.Live.ServerControls.VE.LatLongWithAltitude MyPoint = new Microsoft.Live.ServerControls.VE.LatLongWithAltitude();

                       
                        double BLong = Convert.ToDouble(ds.Tables["bar"].Rows[i][4]);
                        double BLat = Convert.ToDouble(ds.Tables["bar"].Rows[i][5]);

                        MyPoint.Longitude = BLong;
                        MyPoint.Latitude = BLat;

                        Microsoft.Live.ServerControls.VE.Shape MyPin = new Microsoft.Live.ServerControls.VE.Shape(Microsoft.Live.ServerControls.VE.ShapeType.Pushpin,MyPoint);

                        MyPin.Title = ds.Tables["bar"].Rows[i][6].ToString();
                        MyPin.Description = ds.Tables["bar"].Rows[i][7].ToString();

                        SL.AddShape(MyPin);
                    }
                }
            }
     

            private DataSet GetPins(string table)
              {

                  String sqlStmt = "Select * from bar";
                  MySqlConnection sqlConn = new MySqlConnection("Server=localhost;uid=root;pwd=***;DATABASE=*****;");
                  MySqlCommand sqlCmd = new MySqlCommand(sqlStmt, sqlConn);
                  MySqlDataAdapter adapter = new MySqlDataAdapter(sqlCmd);
                 

                   DataSet ds = new DataSet();    


                try
                {

                    sqlConn.Open();
                    adapter.Fill(ds, table);

                }
                catch
                {
                    return ds;
                }

                finally
                {

                    sqlConn.Close();
                }


                return ds;

            }
    Wednesday, February 25, 2009 11:21 AM