Update panel issue in for next loop

Question

I have an update panel. The contents of which contain a Google Map and a multiline text box in which users can paste Postal Codes. The Postal Codes are taken and used in a WHERE IN (list) mysql statement to return the latitude and longitude from our database. Once the Dataset is complete it is turning into a JSON string for create map markers.

The update panel is causing issues so though I would remove it, however in doing so the Dataset only gets the last record from the last entry in the list and the JSON data equally only contain the last entry.

Here is my asps code:-

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>
    <table border="0px" style="width: 100%; font-family: Arial, Helvetica, sans-serif; font-size: small; color: #000000;">
        <tr>
        <td class="style15" valign="top">
            <table border="0px" style="border: 1px solid #000000; font-family: Arial, Helvetica, sans-serif; font-size: small; color: #000000;"width="100%">
                <tr>
                    <td align="center" class="style14" colspan="2">
                        <asp:TextBox ID="PostcodeListTextBox" runat="server" Height="500px" 
                            Width="240px" TextMode="MultiLine"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td align="center" class="style14" colspan="2">
                        <asp:Button ID="ShowLocsButton" runat="server" Text="Show Locations" />
                    </td>
                </tr>
             </table>
        </td>

         <td align="center" valign="top">
                <div id="mapArea" style="border:1px solid black">
                </div>
                <asp:Literal ID="Literal1" runat="server"></asp:Literal>
            </td>
        </tr>
        </table>
 </ContentTemplate>
</asp:UpdatePanel>

And here is my code behind:-

Imports MySql.Data.MySqlClient
Imports MySql.Data
Imports System.Data
Imports System.Web
Imports System.IO
Imports System.Configuration
Imports System.Web.Script.Services
Imports System.Web.Script.Serialization
Partial Class admin_routing
Inherits System.Web.UI.Page
Dim i As Integer = 0
Dim markers As String = ""

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

Protected Sub ShowLocsButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ShowLocsButton.Click
    Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
    Dim conn As New MySqlConnection(ConnString)
    Dim command As New MySqlCommand
    Dim ds As New DataSet
    Dim sql As String
    Dim dt As New DataTable
    Dim markers As String = ""
    Dim i As Integer = 0
    Dim PCList As String = Replace(Me.PostcodeListTextBox.Text, vbLf, "','")
    Dim da As New MySqlDataAdapter
    Dim myJS As New StringBuilder
    PCList = "'" & PCList & "'"
    sql = "SELECT postcode, latitude,longitude FROM geocodes WHERE postcode IN (" & PCList & ")"
    da = New MySqlDataAdapter(sql, conn)
    da.Fill(ds, "locations")
    Dim jsonData = GetJson(ds.Tables(0))
    'Dim jsondata = DataSetToJSON(ds)
    'Me.JsonTextBox.Text = jsonData
    myJS.AppendLine("<script type=""text/javascript"">" & vbCrLf)
    myJS.AppendLine("var mapOptions = {center: new google.maps.LatLng(54.236107, -4.548055999999974)," & vbCrLf)
    myJS.AppendLine("zoom: 6," & vbCrLf)
    myJS.AppendLine("mapTypeId : google.maps.MapTypeId.ROADMAP" + "};" & vbCrLf)
    myJS.AppendLine("var myMap = new google.maps.Map(document.getElementById('mapArea'), mapOptions);" & vbCrLf)
    myJS.AppendLine("var markers = JSON.parse(""<%=jsonData %>"");" & vbCrLf)
    myJS.AppendLine("console.log(""<%=jsonData %>"");" & vbCrLf)
    myJS.AppendLine("for (i = 0; i < markers.length; i++) {" & vbCrLf)
    myJS.AppendLine("var data = markers[i];" & vbCrLf)
    myJS.AppendLine("var myLatLng = new google.maps.LatLng(data.latitude, data.longitude);" & vbCrLf)
    myJS.AppendLine("var marker = new google.maps.Marker({" & vbCrLf)
    myJS.AppendLine("position: myLatLng," & vbCrLf)
    myJS.AppendLine("map: myMap," & vbCrLf)
    myJS.AppendLine("title:  data.postcode" & vbCrLf)
    myJS.AppendLine("});" & vbCrLf)
    myJS.AppendLine("}" & vbCrLf)
    myJS.AppendLine("</script>")
    Dim str As String = myJS.ToString
    ScriptManager.RegisterStartupScript(Me, Me.GetType(), "Init", myJS.ToString, True)
End Sub
Public Shared Function GetJson(ByVal dt As DataTable) As String
    Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
    Dim rows As New System.Collections.Generic.List(Of System.Collections.Generic.Dictionary(Of String, Object))()
    Dim row As System.Collections.Generic.Dictionary(Of String, Object) = Nothing
    For Each dr As DataRow In dt.Rows
        row = New System.Collections.Generic.Dictionary(Of String, Object)()
        For Each col As DataColumn In dt.Columns
            row.Add(col.ColumnName, dr(col))
        Next
        rows.Add(row)
    Next
    Return serializer.Serialize(rows)
End Function

End Class

Also I'm having trouble passing the JSON to the javascript.

Answer 1

I know this can be done using WebMethods. I hope this isn't too far from what you are looking for.

HTML:

<table border="0px" style="width: 100%; font-family: Arial, Helvetica, sans-serif; font-size: small; color: #000000;">
    <tr>
    <td class="style15" valign="top">
        <table border="0px" style="border: 1px solid #000000; font-family: Arial, Helvetica, sans-serif; font-size: small; color: #000000;"width="100%">
            <tr>
                <td align="center" class="style14" colspan="2">
                    <textarea ID="PostcodeListTextBox" cols="40" rows="5"></textarea>
                </td>
            </tr>
            <tr>
                <td align="center" class="style14" colspan="2">
                    <button type="button" ID="ShowLocsButton" onclick="GetPostalJSON()">Show Locations</button>
                </td>
            </tr>
         </table>
    </td>

     <td align="center" valign="top">
            <div id="mapArea" style="border:1px solid black"></div>
        </td>
    </tr>
</table>

There isn't really any need for you to use server side controls at least for what you explained. Though if you do want to use them, I think you need to prefix your selector. ex. #MainContent_PostcodeListTextBox.

VB.NET

To use WebMethods you will need to add Imports System.Web.Services to your imports.

Imports MySql.Data.MySqlClient
Imports MySql.Data
Imports System.Data
Imports System.Web
Imports System.Web.Services
Imports System.IO
Imports System.Configuration
Imports System.Web.Script.Services
Imports System.Web.Script.Serialization

Partial Class admin_routing
Inherits System.Web.UI.Page

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


<WebMethod()>
Public Shared Function GetJson(PCList as String) As String

    Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString,
        conn As New MySqlConnection(ConnString),
        da As New MySqlDataAdapter,
        ds As New DataSet,
        sql = "SELECT postcode, latitude,longitude FROM geocodes WHERE postcode IN (" & PCList & ")"

    da = New MySqlDataAdapter(sql, conn)
    da.Fill(ds, "locations")
    Dim dt as DataTable = ds.Tables(0)

    Dim rows As New System.Collections.Generic.List(Of System.Collections.Generic.Dictionary(Of String, Object))(),
        row As System.Collections.Generic.Dictionary(Of String, Object) = Nothing

    For Each dr As DataRow In dt.Rows
        row = New System.Collections.Generic.Dictionary(Of String, Object)()
        For Each col As DataColumn In dt.Columns
            row.Add(col.ColumnName, dr(col))
        Next
        rows.Add(row)
    Next

    Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
    Return serializer.Serialize(rows)
End Function

I removed any unnecessary code and I moved the code that is used to build up your DataTable into the GetJson function, but aside from that it is the same as you had it. If you notice anything missing (like where you build up your postal code string you use in your where in) it is done in the JS. See below.

Javascript:

<script type="text/javascript">
    var myMap;

    function GetPostalJSON(){
        var res = $("#PostcodeListTextBox").val().split("\n"),
            postCodeStr = "";

        for (var i = 0; i < res.length; i++) {
            if (i > 0) {
                postCodeStr += ","
            }
            postCodeStr += "'" + res[i].trimRight() + "'"
        }

        PageMethods.GetJson(postCodeStr, function(result){
            UpdateMarkers(result)
        }); 
    }

    function CreateMap(){
        var mapOptions = {
                            center: new google.maps.LatLng(54.236107, -4.548055999999974),
                            zoom: 6,
                            mapTypeId : google.maps.MapTypeId.ROADMAP
                        };

        myMap = new google.maps.Map(document.getElementById('mapArea'), mapOptions);
    }

    function UpdateMarkers(jsonData){
        if (myMap == null) CreateMap()

        var markers = JSON.parse(jsonData);
        console.log(jsonData);
        for(i =0; i < markers.length; i++) {
            var data = marker[i];
            var myLatLng = new google.maps.LatLng(data.latitude, data.longitude);
            var marker = new google.maps.Marker({
                position: myLatLng,
                map: myMap,
                title:  data.postcode
            });
        }
    }

</script>

Basically any JS you were building up with your StringBuilder I would just put on the front end. It seems like a waste to create a map every time someone presses the button, so you might as well make that global and check to see if it has been created before modifying it. Something that you may not recognize is the PageMethods.GetJson part. That calls the WebMethod.