listing results in a listbox on a second form.

[claire_bicknell]

I have recently created an interactive map with hotspots to show the different shopping malls around the UK.

Ideally what I would like is to be able to click on a region (manchester) and then have it list all the shops located within that selected centre, in a listbox on another form.

Code so far for form1 (interactive map) is:

Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1
    Private places As New Dictionary(Of String, Rectangle)
    Const strConnection As String = "Data Source=(local);Initial Catalog=ShoppingCentre;Integrated Security=True"
    Dim con As New SqlConnection(strConnection)
    Dim com As New SqlCommand("Select * from dbo.Centre")

    Private Sub LoadPlaces()
        places.Add("Manchester", New Rectangle(287, 275, 10, 10))
        places.Add("Liverpool", New Rectangle(263, 277, 10, 10))
        places.Add("Drake Circus", New Rectangle(184, 422, 10, 10))
        places.Add("Portsmouth", New Rectangle(283, 421, 10, 10))
        places.Add("London", New Rectangle(314, 400, 10, 10))
        places.Add("Bristol", New Rectangle(246, 385, 10, 10))
        places.Add("Coventry", New Rectangle(301, 352, 10, 10))
        places.Add("Birmingham", New Rectangle(287, 345, 10, 10))
        places.Add("Sheffield", New Rectangle(304, 283, 10, 10))
        places.Add("Leeds", New Rectangle(301, 263, 10, 10))
        places.Add("Newcastle", New Rectangle(312, 226, 10, 10))
        places.Add("Belfast", New Rectangle(180, 206, 10, 10))
        places.Add("Glasgow", New Rectangle(257, 145, 10, 10))
        places.Add("Aberdeen", New Rectangle(323, 95, 10, 10))
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        LoadPlaces()

    End Sub

    Private Sub PictureBox1_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles PictureBox1.Paint
        For Each hotspot As Rectangle In places.Values
            e.Graphics.DrawEllipse(Pens.Red, hotspot)
        Next

    End Sub
    Private Sub PictureBox1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles PictureBox1.MouseDown
        For Each place As String In places.Keys
            If places(place).Contains(e.Location) Then
                Stores.Show()

                Me.Hide()
                Exit For
            End If
        Next
    End Sub
    Private Sub PictureBox1_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles PictureBox1.MouseMove
        Dim current As String = Me.ToolTip1.GetToolTip(Me.PictureBox1)
        For Each place As String In places.Keys
            If places(place).Contains(e.Location) Then
                If current <> place Then

                    Me.ToolTip1.Show(place, Me.PictureBox1)
                    Exit For
                End If
            End If
        Next
    End Sub

And the code for the second form (Stores) which has a listbox is:

Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient


Public Class Stores
    'Private places As New Dictionary(Of String, Rectangle)
    Const strConnection As String = "Data Source=(local);Initial Catalog=ShoppingCentre;Integrated Security=True"

    Dim con As New SqlConnection(strConnection)
    Dim com As New SqlCommand("Select * from dbo.Shops")

    
    Dim myDataAdapter As New SqlDataAdapter()
    Dim myDataSet As New DataSet
    Public myDataTable As New DataTable

   
    Private Sub Stores_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        myDataAdapter.SelectCommand = com
        myDataAdapter.SelectCommand.Connection = con
        con.Open()
        myDataAdapter.Fill(myDataSet, "dbo.Shops")
        myDataTable = myDataSet.Tables("dbo.Shops")
        



        'Return myDataTable
        con.Close()


        'Dim strSQL As String = "SELECT * FROM CentreStores WHERE CentreID='" & Form1.listbox1.text

        Call ListBoxItems()

    End Sub
    Private Sub ListBoxItems()

        con.Open()
        Dim maxRowCounter As Integer
        Dim intRowCounter As Integer
        maxRowCounter = myDataSet.Tables("dbo.Shops").Rows.Count
        For intRowCounter = 0 To (maxRowCounter - 1)
            ListBox1.Items.Add(myDataTable.Rows(intRowCounter).Item("ShopName"))
        Next

        con.Close()

    End Sub

    Private Sub lstProducts_SelectedIndexChanged(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged


        Dim con As New SqlConnection(strConnection)
        con.Open()

        'Dim strLoad_File As String
        Dim intRow As Integer
        


        intRow = ListBox1.SelectedIndex
        'TextBox2.Text = myDataTable.Rows(intRow).Item("OpeningClosingTimes")
        TextBox2.Text = myDataTable.Rows(intRow).Item("Description")
        TextBox3.Text = myDataTable.Rows(intRow).Item("Website")



        'strLoad_File = myDataTable.Rows(intRow).Item("Picture")
        'PictureBox1.Load(IO.Path.Combine(Application.StartupPath, strLoad_File))

        con.Close()

        'com.Connection = con



    End Sub

I am using a sql server database and would only like to return the names of the stores that are housed within a selected shopping mall (selected by clicking on map).

This is going right over my head guys and I am fairly new to this. Any sample coding or amendments to my existing code would be much appreciated as I have given this hours of thought and am getting nowhere, fast!

Thanks

 

[MattP]

Private Sub PictureBox1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles PictureBox1.MouseDown
For Each place As String In places.Keys
If places(place).Contains(e.Location) Then
Stores.Show()

Me.Hide()
Exit For
End If
Next
End Sub

You need to pass the 'Centre' to your Stores form so you can use it to filter your query.

This article goes over several methods to pass data between forms: Passing Data Between Forms

 

[JohnH]

Read the "DW2" Data Walkthroughs in help, specifically the topic "Creating a Form to Search Data". Doing this with your second form, remove all your code and leave that to the wizard. You can optionally remove the search strip if you don't want user to be able to search too (but note what the search button does in code).

You can add Show method overload to second form that takes a string as parameter, so you can call Stores.Show("the city string") instead of just Stores.Show(). In this method you use the given string parameter to call same method the generated search box does: Example, something like this:

Public Overloads Sub Show(ByVal param As String)
    Me.Table1TableAdapter.FillByCity(Me.TestExDataSet.Table1, param)
    Me.Show()
End Sub

 

[MattP]

Rather than hard code all of your places you should have this information in your 'Centre' table.

That way you can create a query in your TableAdapter like "SELECT * FROM Centre". Loop through the results and add them to your places Dictionary. This way you can add/remove centres in the table and have them reflected the next time your form is opened.

	Private places As New Dictionary(Of String, Rectangle)

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles MyBase.Load

		LoadPlaces()

	End Sub

	Private Sub LoadPlaces()

		Me.CentreTableAdapter.Fill(Me.CentreExampleDataSet.Centre)

		For Each row As DataRow In Me.CentreExampleDataSet.Centre
			places.Add(row.Item("CentreName"), New Rectangle(row.Item("X1"), row.Item("Y1"), row.Item("X2"), row.Item("Y2")))
		Next

	End Sub

 

[claire_bicknell]

I am a bit befuzzled.

Firstly there are some errors occurring with this code:

Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1
    Private places As New Dictionary(Of String, Rectangle)
    Const strConnection As String = "Data Source=(local);Initial Catalog=ShoppingCentre;Integrated Security=True"
    Dim con As New SqlConnection(strConnection)
    Dim com As New SqlCommand("Select * from dbo.Centre")

    Dim MyDataSet As New DataSet()
    Dim CentreTableAdapter As New SqlDataAdapter()

    Private Sub LoadPlaces()
        Me.CentreTableAdapter.Fill(Me.MyDataSet.Centre)
        For Each row As DataRow In Me.MyDataSet.Centre
            places.Add(row.Item("CentreName"), New Rectangle(row.Item("OpeningClosingTimes"), row.Item("Address"), row.Item("TelephoneNumber")))
        Next
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        LoadPlaces()

    End Sub

Particularly this bit:

 Private Sub LoadPlaces()
        Me.CentreTableAdapter.Fill(Me.MyDataSet.dboCentre)
        For Each row As DataRow In Me.MyDataSet.dboCentre
            places.Add(row.Item("CentreName"), New Rectangle(row.Item("OpeningClosingTimes"), row.Item("Address"), row.Item("TelephoneNumber")))
        Next
    End Sub

The error reads:

"'dbo.Centre' is not a member of 'System.Data.DataSet'."

I think i am probably just being silly.

Also what i don't understand. You will remember in my first post I actually hard coded every place into vb.net. I have since removed this and inserted your code example in its place. Yet, when i run it... and hover over the points on my map they are still appearing as "Manchester" and "Liverpool".

I know this is not working correctly because instead of "Liverpool" in my table it actually reads the name of the shopping centre located in Liverpool. In this case "St Johns". Also its obviously not returning the address etc... which i would also like when i hover over a point on the map.

See attached screenshot

Thanks again for your continuing help

 

[claire_bicknell]

Matt P,

I also took your advice and read "Passing data between Forms" and got very confused. I had that many errors when copying and pasting what he was telling me to do that it just confused me further and now i am completely lost!

Oh I am getting frustrated with myself and feel and I am getting nowhere fast *sob sob*

 

[claire_bicknell]

Read the "DW2" Data Walkthroughs in help, specifically..........

Hi John

This just totally confused me as I couldn't get anything from the walkthrough working.

I am feeling a bit stressed out and frustrated about this and I have a deadline coming soon and I have been stuck for two days.

Lol i feel on the brink of crying.

Help still very much appreciated.

 

[MattP]

    Const strConnection As String = "Data Source=(local);Initial Catalog=ShoppingCentre;Integrated Security=True"
    Dim con As New SqlConnection(strConnection)
    Dim com As New SqlCommand("Select * from dbo.Centre")

    Dim MyDataSet As New DataSet()
    Dim CentreTableAdapter As New SqlDataAdapter()

    Private Sub LoadPlaces()
        Me.CentreTableAdapter.Fill(Me.MyDataSet.Centre)
        For Each row As DataRow In Me.MyDataSet.Centre
            places.Add(row.Item("CentreName"), New Rectangle(row.Item("OpeningClosingTimes"), row.Item("Address"), row.Item("TelephoneNumber")))
        Next
    End Sub

The code I gave you was to access the DataSet created with the Data Source Configuration Wizard. Saves you the time of needing to do 'Dim con As New SqlConnection...etc.

Change your TableAdapter's fill code to

Me.CentreTableAdapter.Fill(MyDataSet, "Centre")

Then you can loop through the DataTable rows like

For Each row As DataRow in Me.MyDataSet.Tables("Centre")

You'll want to take a look at this line:

places.Add(row.Item("CentreName"), New Rectangle(row.Item("OpeningClosingTimes"), row.Item("Address"), row.Item("TelephoneNumber")))

I was suggesting storing the location of each of each of the Centres in your Centre table. In my example X1, Y1 are the X, Y coordinates of the rectangle and X2, Y2 are the Width and Height.

 

[claire_bicknell]

Hi MattP,

Ok I have made amendments to both my code and my database. My code now reads:

 Private Sub LoadPlaces()
        Me.CentreTableAdapter.Fill(MyDataSet, "Centre")
        For Each row As DataRow In Me.MyDataSet.Tables("Centre")
            places.Add(row.Item("CentreName"), New Rectangle(row.Item("X1"), row.Item("Y1"), row.Item("X2"), row.Item("Y2")))
        Next
    End Sub

But, There is now an error with this line:

For Each row As DataRow In Me.MyDataSet.Tables("Centre")

The error reads:

"Expression is of type 'System.Data.DataTable', which is not a collection type"

Any ideas?

I have also added some extra columns to my table. I have attached a screenshot of how it now looks.

Thanks again.

 

[MattP]

For Each row As DataRow In Me.MyDataSet.Tables("Centre")

Oops, should be

For Each row As DataRow In MyDataSet.Tables("Centre").Rows

You'll also need to change this

Dim com As New SqlCommand("Select * from dbo.Centre")

To this

Dim com As New SqlCommand("Select * from dbo.Centre", con)

And tell the table adapter what SqlCommand to use for the SelectCommand

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		CentreTableAdapter.SelectCommand = com
		LoadPlaces()

	End Sub

 

[claire_bicknell]

Hi Matt P,

Fantastic!!!

I owe you my life already but....

When i hover over a point on my map it says "the centre name" but i would like it also to call the address and telephone number? How can i do this?

And now i have this working....

I would also like to be able to click on manchester and then on a second form return in a listbox, only the stores that are located in manchester.

You are saving my bacon, believe me!

 

[cjard]

When i hover over a point on my map it says "the centre name" but i would like it also to call the address and telephone number? How can i do this?

Thought exercise, rather than an answer:
At what point in your code, do you retrieve the centre name, and associate it with rectangular region? That is probably the point where you want to (also) retrieve the phone number and address

I would also like to be able to click on manchester and then on a second form return in a listbox, only the stores that are located in manchester.

This somewhat illogical given the request above.. You cannot have both a specific store identified on a map, and also a general area that lists all stores in that radius! If youre putting Manchester on a map, then youre eventually going to have to run a database query something like SELECT * FROM centres WHERE city = 'Manchester'

As far as I see it, your addresses arent very accurately segmented, so you may struggle with this. Perhaps you should represent your data better, i.e. a separate column for the city name instead of putting it into the address

 

[InertiaM]

As far as I see it, your addresses arent very accurately segmented, so you may struggle with this. Perhaps you should represent your data better, i.e. a separate column for the city name instead of putting it into the address

I agree. At the very least, I would separate out the postcode into a separate field. Plotting postcodes on a map is easy

 

[MattP]

Thought exercise, rather than an answer:
At what point in your code, do you retrieve the centre name, and associate it with rectangular region? That is probably the point where you want to (also) retrieve the phone number and address


This somewhat illogical given the request above.. You cannot have both a specific store identified on a map, and also a general area that lists all stores in that radius! If youre putting Manchester on a map, then youre eventually going to have to run a database query something like SELECT * FROM centres WHERE city = 'Manchester'

As far as I see it, your addresses arent very accurately segmented, so you may struggle with this. Perhaps you should represent your data better, i.e. a separate column for the city name instead of putting it into the address

claire_bicknell's table structure is discussed in this thread: link.

In this post I walked them through joining the Centre table to the Shops table using the cross-reference table that's set up: link

Claire's current question was answered by JohnH in post 3 of this thread showing how to overload the Show method to pass data (Centre name). Claire now just needs to combine the concepts of passing the Centre name to the form and searching by Centre name to get the desired results.