locked
Updating a control does not clear the first dropdown selection queried in sql visual basic statement RRS feed

  • Question

  • User-448731444 posted

     Hi there.  I have a map control that changes markers based on selection from a dropdown.  When I make the next selection, the markers from the first selection still appear on the map and are added to the new selection.  Anybody please know how to clear the first selection (is it cache or what I wonder).  Here is my code and your help is awesomely appreciated.

        Protected Sub ComboBox2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged

            Dim textboxval As String

            textboxval = ComboBox2.SelectedIndex + 1

            Using connection As New SqlConnection("Data Source=CHRIS-PC\SQLEXPRESS;Initial Catalog=TorGHL;Integrated Security=True")

                Dim cmd1 As String

                connection.Open()

                If Not (String.IsNullOrEmpty(TextBox1.Text)) Then

                    cmd1 = "SELECT [BarName], [MenuSubId], [Email], [Address], [PhoneNo], [BarURL], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = " & textboxval

                    'cmd1 = "SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = 3"

                Else

                    cmd1 = "SELECT [BarName], [MenuSubId], [Email], [Address], [PhoneNo], [BarURL], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = " & textboxval

                    'cmd1 = "SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = 3"

                    MsgBox(2)

                End If

                Dim dapubcb As New SqlDataAdapter

                Dim sqlcomm As New SqlCommand(cmd1, connection)

                dapubcb.SelectCommand = sqlcomm

                Dim dspubcb As New DataSet("BarDetails")

     

                ' you must have either a commandbuilder or update commands to make changes

                'to the backend (SQL Server)

                dapubcb.Fill(dspubcb, "BarDetails")

     

                ' map parameters

                GoogleMap1.Address = "100 Maitland Street, Toronto M4Y 2E9"

                GoogleMap1.Zoom = "16"

                GoogleMap1.ShowScaleControl = True

                GoogleMap1.EnableInfoWindow = True

                GoogleMap1.EnableMarkerManager = True

                GoogleMap1.EnableInfoWindow = True

                GoogleMap1.ShowScaleControl = True

                GoogleMap1.ToolTip = "Click to view Venue and Location"

     

                For Each irow In dspubcb.Tables(0).Rows

                    If IsDBNull((irow("bg_lat"))) = False Then

                        Dim myMarker As New GoogleMarker()

                        Dim xxx As String

                        xxx = (irow("BarUrl")).ToString

                        GoogleMap1.Markers.Add(myMarker)

                        myMarker.Title = (irow("BarName".Trim))

                        myMarker.Latitude = (irow("bg_lat".Trim))

                        myMarker.Longitude = (irow("bg_long".Trim))

                        myMarker.Text = "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='" & "http://" & (irow("BarUrl").ToString().Replace(" ", String.Empty)) & " '>" & (irow("BarUrl")) & "</a>"

                        myMarker.ShadowSize = New GoogleSize(0, 0)

                        '<a href="description.html" target="_blank">

                    ElseIf IsDBNull((irow("Address"))) = True Then

                        'do nothing if address and latitude are null

                    ElseIf IsDBNull((irow("bg_lat"))) = True Then

                        'take the address

                        Dim x As String

                        x = (irow("Address")).ToString

                        Dim myMarker As New GoogleMarker()

                        GoogleMap1.Markers.Add(myMarker)

                        myMarker.Address = (irow("Address")) & "Toronto"

                        myMarker.Text = "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='" & (irow("BarUrl".ToString)).trim & "'>" & (irow("BarUrl")) & "</a>"

                        '       myMarker.Text = "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='http://www.google.ca'> Website </a>"

                    End If

         

                Next irow

     

            End Using

        End Sub

     

     

    Friday, January 29, 2010 10:15 AM

Answers

  • User-448731444 posted

    Thanks for your time Karan.  Turns out it has to do with wrapping the map with an ajax update panel.  I am new to the ajax concept and somehow the panel was refreshing the map with old data.  Now that I have removed the panel the map refreshes with the current data. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 31, 2010 12:14 AM

All replies

  • User188494879 posted

    There should be some property within that control to clear all markers.

    Something like GoogleMap1.Markers.Clear() or some overlay property.

    Check to see the map contains these property and try setting and clearing those.

    Friday, January 29, 2010 10:28 AM
  • User-448731444 posted

    Thanks Karan, I tried that, and the correct markers appear momentarily, and then the previous selection reappears.  It's like the previous selection is remaining in cache or something and wants to rewrite the page.  Weird.  Any other ideas? 

    Friday, January 29, 2010 12:01 PM
  • User188494879 posted

    Try to add, GoogleMap1.Markers.Clear() before your "For Each irow In dspubcb.Tables(0).Rows" statement.

    Also are you sure your table still doesnt contains your previous rows.

    Try debugging and step through from this "For Each irow In dspubcb.Tables(0).Rows" line and see if your

    previous rows are added again.


    Friday, January 29, 2010 12:12 PM
  • User-448731444 posted

    Thanks again.  Still no luck.  I added the code where you suggested and looped through the code.  I checked against data and verified that the loops were indeed correct.   I tried the dropdown, the correct markers appeared momentarily and then the page refreshed with the markers from the last selection.  Very weird.

        Protected Sub ComboBox2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged

     

            Dim textboxval As String

            textboxval = ComboBox2.SelectedIndex + 1

            Using connection As New SqlConnection("Data Source=CHRIS-PC\SQLEXPRESS;Initial Catalog=TorGHL;Integrated Security=True")

                Dim cmd1 As String

                connection.Open()

                If Not (String.IsNullOrEmpty(TextBox1.Text)) Then

                    cmd1 = "SELECT [BarName], [MenuSubId], [Email], [Address], [PhoneNo], [BarURL], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = " & textboxval

                    'cmd1 = "SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = 3"

                Else

                    cmd1 = "SELECT [BarName], [MenuSubId], [Email], [Address], [PhoneNo], [BarURL], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = " & textboxval

                    'cmd1 = "SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = 3"

     

                End If

                Dim dapubcb As New SqlDataAdapter

                Dim sqlcomm As New SqlCommand(cmd1, connection)

                dapubcb.SelectCommand = sqlcomm

                Dim dspubcb As New DataSet("BarDetails")

     

                ' you must have either a commandbuilder or update commands to make changes

                'to the backend (SQL Server)

     

                dapubcb.Fill(dspubcb, "BarDetails")

                ' map parameters

                GoogleMap1.Address = "100 Maitland Street, Toronto M4Y 2E9"

                GoogleMap1.Zoom = "16"

                GoogleMap1.ShowScaleControl = True

                GoogleMap1.EnableInfoWindow = True

                GoogleMap1.EnableMarkerManager = True

                GoogleMap1.EnableInfoWindow = True

                GoogleMap1.ShowScaleControl = True

                GoogleMap1.ToolTip = "Click to view Venue and Location"

                GoogleMap1.Markers.Clear()

     

                For Each irow In dspubcb.Tables(0).Rows

                    If IsDBNull((irow("bg_lat"))) = False Then

                        Dim myMarker As New GoogleMarker()

                        Dim xxx As String

                        xxx = (irow("BarUrl")).ToString

                        GoogleMap1.Markers.Add(myMarker)

                        myMarker.Title = (irow("BarName".Trim))

                        myMarker.Latitude = (irow("bg_lat".Trim))

                        myMarker.Longitude = (irow("bg_long".Trim))

                        myMarker.Text = "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='" & "http://" & (irow("BarUrl").ToString().Replace(" ", String.Empty)) & " '>" & (irow("BarUrl")) & "</a>"

                        myMarker.ShadowSize = New GoogleSize(0, 0)

                        Stop

                        '<a href="description.html" target="_blank">

                    ElseIf IsDBNull((irow("Address"))) = True Then

                        'do nothing if address and latitude are null

                    ElseIf IsDBNull((irow("bg_lat"))) = True Then

                        'take the address

                        Dim x As String

                        x = (irow("Address")).ToString

                        Dim myMarker As New GoogleMarker()

                        GoogleMap1.Markers.Add(myMarker)

                        myMarker.Address = (irow("Address")) & "Toronto"

                        myMarker.Text = "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='" & (irow("BarUrl".ToString)).trim & "'>" & (irow("BarUrl")) & "</a>"

                        '       myMarker.Text = "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='http://www.google.ca'> Website </a>"

                    End If

     

                Next irow

                cmd1 = Nothing

            End Using

     

     

        End Sub

     

    Friday, January 29, 2010 12:33 PM
  • User188494879 posted

    Ok,

    Are you populating your map control in your page load also?

    If so can you post your page load event

    Friday, January 29, 2010 12:38 PM
  • User-448731444 posted

    What's weird is that when I place the google1map.marker at the END of the sub, it clears.  If I add it to the beginning of the sub it does not work and that's exactly where I need it.  Here's the page load if that helps.

     

     

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load' Dim queryString As String = "SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email], [BarDescription], [Id] FROM [BarDetails] WHERE [MenuSubId] = 1"

     

    connection.Open()

     

    Using connection As New SqlConnection("Data Source=CHRIS-PC\SQLEXPRESS;Initial Catalog=TorGHL;Integrated Security=True")Dim cmd As String

     

    If Not (String.IsNullOrEmpty(TextBox1.Text)) Then

    cmd =

     

    "SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] =" & TextBox1.TextElse

    cmd =

    "SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email], [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] "

     

    End If

     

     

    dapubcb.SelectCommand = sqlcomm

     

     

     

    Dim dapubcb As New SqlDataAdapterDim sqlcomm As New SqlCommand(cmd, connection)Dim dspubcb As New DataSet("BarDetails")' you must have either a commandbuilder or update commands to make changes

     

    'to the backend (SQL Server)

    dapubcb.Fill(dspubcb,

     

    "BarDetails")' map parameters

    GoogleMap1.Address =

    "120 Maitland Street, Toronto"

    GoogleMap1.Zoom =

    "16"

    GoogleMap1.ShowScaleControl =

    True

    GoogleMap1.EnableInfoWindow =

    True

    GoogleMap1.EnableMarkerManager =

    True

    GoogleMap1.EnableInfoWindow =

    True

    GoogleMap1.ShowScaleControl =

    True

    GoogleMap1.ToolTip =

    "Click to view Venue and Location"

     

     

    For Each irow In dspubcb.Tables(0).RowsIf IsDBNull((irow("bg_lat"))) = False Then

     

     

    Dim myMarker As New GoogleMarker()Dim xxx As String

    xxx = (irow(

    GoogleMap1.Markers.Add(myMarker)

    myMarker.Title = (irow(

    myMarker.Latitude = (irow(

    myMarker.Longitude = (irow(

    myMarker.Text =

    "BarUrl")).ToString"BarName".Trim))"bg_lat".Trim))"bg_long".Trim))"<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='" & "http://" & (irow("BarUrl").ToString().Replace(" ", String.Empty)) & " '>" & (irow("BarUrl")) & "</a>"

    myMarker.ShadowSize =

     

    New GoogleSize(0, 0)'<a href="description.html" target="_blank">

     

    ElseIf IsDBNull((irow("bg_lat"))) = False Then

     

    'do nothing if address and latitude are null

     

    ElseIf IsDBNull((irow("bg_lat"))) = True Then

     

    'take the address

     

    Dim x As String

    x = (irow(

     

    GoogleMap1.Markers.Add(myMarker)

    myMarker.Address = (irow(

    "Address")).ToStringDim myMarker As New GoogleMarker()"Address")) & "Toronto"

    myMarker.Text =

    "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='" & (irow("BarUrl".ToString)).trim & "'>" & (irow("BarUrl")) & "</a>"

     

    ' myMarker.Text = "<b>" & (irow("BarName")) & "</b>" & "<br/>" & (irow("Address")) & "<br/>" & (irow("BarDescription")) & "<br/>" & (irow("Email")) & "<br/>" & "<a href='http://www.google.ca'> Website </a>"

     

    End If

     

     

    Next irowEnd Using

     

    End Sub

    Friday, January 29, 2010 12:52 PM
  • User188494879 posted

    Ok.

    Do it like this:

    Create Another sub, call it as PopulateMapData() and move all the code that you have in your page load to this sub.

    And then in your page load do this;

    If not Page.IsPostback then
      PopulateMapData() --- This is the call to your sub.

    End If


    This should work.


    Friday, January 29, 2010 1:31 PM
  • User-448731444 posted

    Thanks for your time Karan.  Turns out it has to do with wrapping the map with an ajax update panel.  I am new to the ajax concept and somehow the panel was refreshing the map with old data.  Now that I have removed the panel the map refreshes with the current data. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 31, 2010 12:14 AM