CODEWITHSUNDEEP
mysqlvb.netdatagridview
Kyle Moffat
Kyle Moffat

Reputation: 173

SQL Database Relationships & VB.Net Application

I know these questions have been asked and answered multiple times all over these forums and the internet but I am struggling to wrap my head around it. I am having trouble understanding exactly how the relationships work with Primary and foreign keys in an SQL Database. Say I have a Table Called "Employees" with the Primary key named "employeeID" then another table name "Addresses" with the Primary key named "addressID" I would create a Foreign key in the "Addresses" table called "employeeID" then create the relationship between the Employees table and the Addresses table correct?

I have a database that has Employees, Addresses, Emergency Contacts etc... where from what I have read and understood from various sources is created correctly. So in my application vb.net using Azure SQL DB I am trying to display in a datagridview say the employees and their addresses. It is obvious that a datagridview can only display information from one table at a time so I tried to create a new dataset that would gather the information from the two tables and the display it as one in the gatagrid view. When the datagrid view displays this information it is all mixed up and the relationships are not being honored. For each entry in the employee table it lists each entry for each person three times in the address table. enter image description here

Here is a copy of the code for the Dataset I am using:

   Dim connectionstring As String = My.Settings.MacroQualityConnectionString
    Dim sqlemp As String = "SELECT * FROM Employee_Names, Addresses"
    Dim connection As New SqlConnection(connectionstring)
    Dim dataadapter As New SqlDataAdapter(sqlemp, connection)
    connection.Open()
    dataadapter.Fill(dsemployees, "Employee_Names")
    connection.Close()

    DataGridView1.DataSource = dsemployees.Tables(0)

I know it probably isn't correct but I am not sure how to make the application honor the relationships in the database? Eventually I need to be able to gather information from multiple tables to be correctly displayed in a datagridview but unfortunately my SQL skills are quite weak among most of my other skills! Maybe most of my issue is back at the design and set up of my DB? Any input is greatly appreciated everyone as I have been struggling with this for a few weeks now! :(

Thank you all in advance!

Upvotes: 0

Views: 2042

Answers (1)

David
David

Reputation: 218960

You can explicitly define the relationship in the query with a JOIN clause. Something like this:

SELECT *
FROM Employee_Names
INNER JOIN Addresses
  ON Employee_Names.employeeID = Addresses.employeeID

This tells the query to explicitly follow that key relationship (which it might be smart enough to figure out on its own for simple relationships, depending on the RDBMS, but being explicit makes the code more clear).

What this produces is "one table" in the sense that the results are one set of records. However, consider logically what that would mean if any given record in the Employee_Names table has more than one associated record in the Addresses table. Flattening them into a single table of results like this would mean that there would be duplicate Employee_Names records.

Such is the nature of tabular data. If a single grid is displaying two dimensions of data, it's going to have to flatten them, resulting in some repeated data.

Essentially you need to ask yourself exactly what a "record" in the DataGridView should represent. Currently it represents a unique combination of Employee_Names and Addresses. Regardless of repeats, it's going to show every combination.

If, instead, you want each record to represent a unique Employee_Names record then you wouldn't want to perform the JOIN in the query quite like that. Instead, you'd be looking to use that foreign key relationship to indicate parent/child records and bind the DataGridView to multiple tables (instead of a single table of results from the query).

I don't have much experience with the tooling for that, but this seems like a reasonable place to start. The idea is that the DataGridView would show records from the Employee_Names table, and clicking an expandable area of any given record would then show associated records from the Addresses table in a sub-grid.

Upvotes: 2

Related Questions