Harambe Harambe - 22 days ago 12
Vb.net Question

SQL UNION error - Selected columns do not match

I'm trying to use a

SQL UNION
query to populate my
DataGridView
.

The 3 tables I'm trying to join are;


  • Customers - Contains basic customer information (PK: Customer_Code)

  • Customer_Addresses - Contains contact information (Telephone, fax, address etc) (PK: Cust_Code)

  • Delivery_Addresses - The same as Customer_Addresses, but for delivery locations (PK: Cust_Code)



I'm trying to display in my
DataGridView
some data from each table. I want to be able to show, on each row, the data from each table, and join it using the Primary Keys I mentioned above.

So far, I have tried joining using an
INNER JOIN
and a
UNION JOIN
. However, an
INNER JOIN
didn't allow me to select only some columns from both tables, whereas a
UNION JOIN
gave me the following error;


The number of columns in the two selected tables or queries of a union query do not match.


This is my code, what am I doing wrong?

ElseIf type = "Customer" Then
sql = "SELECT [Customer_Code], [Customer_Name], [Customer_Category], " & _
"[Average_Payment_Terms], [Notes], [Aq_Date], [salesRep], [hoEmail], [webpage] " & _
"FROM Customers " & _
"UNION SELECT [Contact_Code], [Contact_Type], [Contact_Name], [Add1], [Add2], " & _
"[Add3], [Add4], [Add5], [Postcode], [Country], [Telephone], [Fax], " & _
"[Email], [Mobile_Phone] FROM [Customer_Addresses]"

Dim da As New OleDbDataAdapter(sql, con)
Dim ds As New DataSet
Dim dt As New DataTable

da.Fill(ds) ' This is the line that the error occurs on
dt = ds.Tables(0).Copy()


EDT

I've tried using a
JOIN
again and used the following;

ElseIf type = "Customers" Then
sql = "SELECT Customer_Addresses.Cust_Code, Customers.Customer_Name, " & _
"Customers.Customer_Category, Customers.Average_Payment_Terms, Customers.Notes, " & _
"Customers.Aq_Date, Customers.salesRep, Customers.hoEmail, Customers.webpage, " & _
"Customer_Addresses.Contact_Code, Customer_Addresses.Contact_Name, " & _
"Customer_Addresses.Contact_Type, Customer_Addresses.Add1, Customer_Addresses.Add2, " & _
"Customer_Addresses.Add3, Customer_Addresses.Add4, Customer_Addresses.Add5, " & _
"Customer_Addresses.Postcode, Customer_Addresses.Country, Customer_Addresses.Telephone, " & _
"Customer_Addresses.Fax, Customer_Addresses.Email, Customer_Addresses.Mobile_Phone " & _
"FROM Customers AS c INNER JOIN Customer_Addresses AS a ON c.Customer_Code = a.Cust_Code"

Dim da As New OleDbDataAdapter(sql, con)
Dim ds As New DataSet
Dim dt As New DataTable

da.Fill(ds)
dt = ds.Tables(0).Copy()

dgvExport.DataSource = dt


Which results in the following error;


No value given for one or more required parameters

Answer

First of all, you should be using an INNER JOIN for this.

Secondly, to use an inner join, you need to make sure that the keys you wish to join on have a relationship between them in Access. When making a new query, and adding the two tables (In Access) to the query, it will tell you there is no relationship between them. Pressing the 'OK' button will take you to the relationships designer screen.

Select from your Customers table the Primary Key (Customer_Code), and drag it across to the matching key in the other 2 tables (Cust_Code). This will allow the database to join the keys together, instead of asking for parameter values.

The query itself should then look like this;

sql = "SELECT [Customer_Addresses].[Cust_Code], " & _
   "[Customer_Addresses].[Contact_Code], [Customer_Addresses].[Contact_Name], " & _
   "[Customer_Addresses].[Contact_Type], [Customer_Addresses].[Add1], " & _
   "[Customer_Addresses].[Add2], [Customer_Addresses].[Add3], " & _
   "[Customer_Addresses].[Add4], [Customer_Addresses].[Add5], " & _
   "[Customer_Addresses].[Postcode], [Customer_Addresses].[Country]," & _
   "[Customer_Addresses].[Telephone], [Customer_Addresses].[Fax], " & _
   "[Customer_Addresses].[Email], [Customer_Addresses].[Mobile_Phone], " & _
   "[Customers].[Customer_Code], [Customers].[Customer_Category], " & _
   "[Customers].[Average_Payment_Terms], [Customers].[Customer_Name], [Customers].[Notes], " & _
   "[Customers].[Aq_Date], [Customers].[salesRep], " & _
   "[Customers].[hoEmail], [Customers].[webpage] FROM Customers " & _
   "INNER JOIN Customer_Addresses ON [Customers].[Customer_Code] =[Customer_Addresses].[Cust_Code]"

Obviously this is just for two tables, but once you join the Primary Key up to Delivery_Addresses.Cust_Code then it will include this table too.

Another thing to note is that if you have 100 customers, let's say, and each has 1 address in Customer_Addresses, but only 5 customers have an address in Delivery_Addresses, it will only show the 5 customers with a delivery address. It will show data from all 3 tables, but only records where data for that customer exists in all tables.

Maybe storing addresses in just one table is the better way to do it.

Comments