Selecting data from two sql server tables in database

[claire_bicknell]

Hi, I am hoping someone might be able to help me.

I have a form which is linked to a SQL Server database.

I have successfully managed to load shop names into a list box and once selected i can generate further information all stored in the same table in the database.

I have one text box left to fill in. This information needs to come from a separate table called dbo.CentreShops. The text box needing to be filled is OpeningClosingTimes.

How can i select from two different tables within a database? I am sure this is fairly simple. Below I have entered the code that it fully functioning but needs altering.

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=PC-CLAIRE;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()

        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 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

Any help is much appreciated.

 

[MattP]

I'm assuming your OpeningAndClosing times column is related in someway to the ShopName. Take a look into JOINs.

SQL Joins

 

[claire_bicknell]

Hi Matt,

Thanks for the quick response.

I have a table called "shops" which has the following (generic info):

ShopID, ShopName, Description, Website


I have a table called "Centre" which has the following:

CentreId, CentreName, OpeningClosingTimes, Address and TelephoneNumber


I have a link table called "CentreShops" which links certain shops to a centre (unique info)

CentreId, ShopId, OpeningClosingTimes, TelephoneNumber

I really have two issues here:

1. I have an interactive map with hotspots which allows the user to select a shopping centre. As soon as they click a shopping centre from the map a new page pops up and I would like the listbox to only list the shops for that centre. Currently it is listening every shop in my database.

2. Once they select a shop from the list box i have successfully managed to return the generic data from my "shop table" (inc. description and website) but i also need it to return OpeningClosingTimes(unique info) from the CentreShops table.

To clarify, i have developed my database this way because each centre will contain different shops and will therefore have different openingtimes (hence why opening times is located in a different table: centreshops)

I hope i have explained that clearly enough. Tell me if I have confused you. I am good at doing that!

Look forward to hearing from you

 

[MattP]

"CentreShops" has OpeningClosingTimes & TelephoneNumber columns? I would think these would either be global for the centre and stored in the 'Centre' table or broken down to the individual shop and stored in the 'Shops' table.

SELECT s.ShopName, s.Description, s.Website, c.OpeningClosingTimes, c.Address, c.TelephoneNumber
FROM Shops s INNER JOIN CentreShops cs ON s.ShopID = cs.ShopID INNER JOIN Centre c ON c.CentreID = cs.CentreID
WHERE cs.CentreID = @CentreID

Take a look at cjard's parameterized queries thread (here) for passing in the CentreID (@CentreID in my example).

 

[claire_bicknell]

The reason i have put "telephoneNumber" and "OpeningClosing" in my link table "CentreShops" is because the Opening Closing Time and TelephoneNo is for that individual shop and not for the Centre. "Centre" has an Opening Closing Time of its own.

Also i can't put any of this info in "Shop" because each individual shop will have its own Opening Closing Time and Telephone Number. For example:

John Lewis in Manchester Opens at 9am-5pm and telephoneNo. 067963830

But,

John Lewis in Liverpool Opens at 9.30am-6pm and telephoneNo. 37383020

I know this is all a bit confusing.

That SQL statement you wrote me also, where does that go? VB or my database?

I am soooooo new to this. Help is much appreciated.

 

[MattP]

...i can't put any of this info in "Shop" because each individual shop will have its own Opening Closing Time and Telephone Number. For example:

John Lewis in Manchester Opens at 9am-5pm and telephoneNo. 067963830

But,

John Lewis in Liverpool Opens at 9.30am-6pm and telephoneNo. 37383020

Isn't that why you'd want this information in the shops table? Each of these 'John Lewis' shops is going to have a different ShopID in your table so you'll be able to distinguish one from the other.

Your CentreShops table should just be handling the many-to-many relationship between your Shop and Centre tables.

That SQL statement you wrote me also, where does that go? VB or my database?

In your case it would go in you SqlCommand object 'com'.

That being said I would take a look at this series: Forms Over Data. I would recommend the whole series but take a look @ 2, 3, & 16.

Here's the data-walkthrough series that cjard is always recommending: DW2. Check out the links for 'Creating a Simple Data Application', 'Creating a Form to Search Data in a Windows Application', & 'Displaying Related Data on a Form in a Windows Application' to start.

I am soooooo new to this. Help is much appreciated.

Not a problem. The people that answer questions here want to help others learn to do things the right way. When the advice is well received you'll find that you get better answers in future requests.

I realize you're well into your program at the moment, but it would be VERY beneficial to you to go through the walkthroughs.

 

[MattP]

In continuation of the previous post I'll show you how I would set up the tables and how I go about writing a query to get the ShopID when given a CenterName.

I've attached images of the 3 mock tables that I used constructing the query.

The information you start off with is the CenterName which is only in the Centre table. I start by writing a query to pull the corresponding information from the table.

SELECT *
FROM Centre c
WHERE c.CenterName = 'Liverpool'

Results:

2 Liverpool 263 277 10 10

The critical piece of information pulled back is the CenterID of 2 which I can use to match with your cross-reference table CentreShops.

SELECT *
FROM Centre c INNER JOIN CentreShops cs ON c.CenterID = cs.CenterID
WHERE c.CenterName = 'Liverpool'

2 Liverpool 263 277 10 10 2 4
2 Liverpool 263 277 10 10 2 5

Now that you're joining information from your cross-reference table you can see that ShopID's 4 & 5 are associated with CenterID 2. You can now pull in the information from your Shops table.

SELECT * 
FROM Centre c INNER JOIN CentreShops cs ON c.CenterID = cs.CenterID INNER JOIN Shops s ON s.ShopID = cs.ShopID
WHERE c.CenterName = 'Liverpool'

2 Liverpool 263 277 10 10 2 4 4 Cal's Eatery Another Place to Eat www.bigcals.com 345 Here 6:00AM - 5:00PM (456) 789-0123
2 Liverpool 263 277 10 10 2 5 5 Jim's Books Limited 1st Edition Books Coming Soon... 456 My Place 10:00AM - 5:00PM (567) 890-1234

Now you've got all of the information that's associated between your tables. You only want the information from the Shops table so you'll only want those columns.

SELECT s.ShopName 
FROM Centre c INNER JOIN CentreShops cs ON c.CenterID = cs.CenterID INNER JOIN Shops s ON s.ShopID = cs.ShopID
WHERE c.CenterName = 'Liverpool'

Cal's Eatery
Jim's Books

In these examples I've hardcoded 'Liverpool' into the CenterName. You'll want to pass in the CenterName like the example JohnH gave you with FillByCity.

SELECT s.ShopName 
FROM Centre c INNER JOIN CentreShops cs ON c.CenterID = cs.CenterID INNER JOIN Shops s ON s.ShopID = cs.ShopID
WHERE c.CenterName = @CenterName

 

[claire_bicknell]

Hi Matt P.

I might be missing the point...

I see your link table (CentreShops) just shows the CentreId and ShopId. Surely I need to store the telephone number and openingClosing times in this table too.

Because Manchester John Lewis wont have the same telephone number as the John Lewis in Liverpool. So imagine in vb when i click on manchester, a listbox will show just the shops located in manchester shopping centre a long with the unique telephone number for that store. If telephone number is stored in shops its going to pull the same telephone number for every John Lewis in the country.

Also I am still confused as to where you are writing this SELECT * c.description etc... from. Are you writing this straight into VB or in SQL Server somewhere?

Thanks for all your help so far. Much appreciated.

 

[claire_bicknell]

Here are some screenshots to help you to understand the nonsense that I am probably talking

 

[MattP]

I see your link table (CentreShops) just shows the CentreId and ShopId. Surely I need to store the telephone number and openingClosing times in this table too.

Because Manchester John Lewis wont have the same telephone number as the John Lewis in Liverpool. So imagine in vb when i click on manchester, a listbox will show just the shops located in manchester shopping centre a long with the unique telephone number for that store. If telephone number is stored in shops its going to pull the same telephone number for every John Lewis in the country.

Each John Lewis will be associated with a different 'Centre' (by ShopID in your cross-reference table) so you'll only be pulling the related information. I've attached a new example Shops table. In this example there are 4 seperate John Lewis' each with their own description, address, opening and closing times, and telephone number. Each of these loctions has their own unique ShopID in the table. Because you're only pulling the information that is related to a specific Centre that'll be the information you get.

SELECT s.* 
FROM Centre c INNER JOIN CentreShops cs ON c.CenterID = cs.CenterID INNER JOIN Shops s ON s.ShopID = cs.ShopID
WHERE c.CenterName = 'Liverpool' AND s.ShopName = 'John Lewis'

Returns: 4 John Lewis John Lewis In Liverpool www.bigcals.com 345 Here 6:00AM - 5:00PM (456) 789-0123

SELECT s.* 
FROM Centre c INNER JOIN CentreShops cs ON c.CenterID = cs.CenterID INNER JOIN Shops s ON s.ShopID = cs.ShopID
WHERE c.CenterName = 'Manchester' AND s.ShopName = 'John Lewis'

Returns: 1 John Lewis John Lewis In Manchester www.joesdiner.com 111 Someplace 9:00AM - 10:00PM (123) 456-7890

As you can see when searching for a John Lewis in Liverpool information for ShopID 4 is returned because that's what's associated with the Liverpool CentreID. When searching for Manchester you get information for ShopID 1.

Also I am still confused as to where you are writing this SELECT * c.description etc... from. Are you writing this straight into VB or in SQL Server somewhere?

Thanks for all your help so far. Much appreciated.

I'm writing this in Microsoft SQL Server Management Studio Express.

When I wrote FROM Centre c in my example I'm assigning an alias of c to the Centre table. That way when I need to refer to a column in the query I can use c.CentreID to refer to the column rather than Centre.CentreID.

 

[claire_bicknell]

Right I have had another shot at my database. I have attached screenshots so they look as you suggested. What do you think now?

I am sorry i am so slow at understanding.

Also... in regards to this comment:

SELECT s.* 
FROM Centre c INNER JOIN CentreShops cs ON c.CenterID = cs.CenterID INNER JOIN Shops s ON s.ShopID = cs.ShopID
WHERE c.CenterName = 'Liverpool' AND s.ShopName = 'John Lewis'

How do i go about actually writing this in Microsoft SQL Server Management Studio Express?

Yet again, I am new to this process and a step by step would be much appreciated.

Thank you again for such great help

 

[MattP]

In SQL Server Management Studio Express I click on the 'Connect' button and select the server I'm going to be working with. Then I click on the 'New Query' button and select the database I'm writing the query against from the drop down box.